Report Variables and Formulas


What if, in this report, you wanted to know the sales per unit? You could go back to the universe and see if there is a Sales per Unit measure object. In this case there is not, but because you have Sales Revenue and Quantity Sold objects, you can calculate the sales per unit within the report itself. Web Intelligence has a formula language just like Crystal Reports, with dozens of available functions.

The formula language can be used in two ways. First, a user could just type a formula into a cell and have the formula applied to the report block directly. As such, it resides only within that one cell for that one report block. The formula can also be within an object defined within the report. This is called a report variable. As an object, it is available for projection and reuse just like a measure object, meaning it can be swapped in and out easily or reused in multiple charts or tables.

Note

Although both report formulas and variables can be created in the WebI Java report panel and in the Interactive Viewing mode, the XI release provides a bit more support in the user interface when using the Java report panel. The examples in this section use the Java report panel for ease of understanding.


Tip

When developing a report and using formulas, it is best practice to encapsulate the formula in a Report Variable object so other users can interact with the object when doing analyses.


In the Java report panel, there is an icon with three little objects on it and a down arrow (indicating it will drop down into the report space). This is the Show/Hide Variable Editor button. Clicking this reveals the variable editor, as seen in Figure 20.17.

Figure 20.17. The Java report panel with the variable editor showing


The variable editor contains an area to write the name of the variable, a text box to type in the formula (Formula Definition box), and helper boxes where objects, functions, and operators can be double-clicked and included in the Formula Definition box.

The Available Functions box groups the functions into folders so that they can be easily found, or the user can choose the All folder to see all available functions. The major function categories are

  • Aggregate These are aggregate functions: sum, count, max, and so on. These aggregate functions also dictate how the variable behaves as it is projected within the microcube. These functions apply to report variables that are also measures.

  • Character Typical string manipulation functions: Ltrim, Substr, Length, Formatdate, replace, and so forth.

  • Date & Time Typical date and time functions: CurrentDate, CurrentTime, DayNumberofWeek, DaysBetween, RelativeDate, and the like.

  • Document Functions detailing document specific parameters: DocumentAuthor, DocumentDate, DocumentName, DocumentPartiallyRefreshed, DocumentTime, and DrillFilters.

  • Data Provider Functions that describe the individual data providers (resultsets) within the report: DataProvider, LastExecutionDate, LastExecutionTime, and UserResponse.

  • Misc Miscellaneous functions.

  • Logical Typical logical functions: IsDate, IsError, IsNull, IsNumber, IsString, and IsTime.

  • Numeric Typical numeric functions: Abs, Sqrt, Floor, Exp, Log, Truncate, Round, and so forth.

Note

Readers that are more familiar with the Business Objects full client will note that the function library available in Web Intelligence is not as extensive as the one available in the desktop version. The function capabilities are continuously expanded and there will be many additional functions supported in the next release, with continual expansion for all subsequent releases.


The Crystal Reports function library is more extensive than the Web Intelligence function library. This is due mainly to two reasons. First, Crystal Reports can provide highly sophisticated formatting, so it requires a function language that enables this. Web Intelligence functions are focused on report object creation for on-report analysis. Second, the Web Intelligence function library is relatively new compared to the Crystal Reports function library, so expect it to expand significantly in future releases.

This example creates a Sales per Unit report variable. First, type in the report variable name in the Variable Definition box, and choose Measure as the Variable Qualification (this tells it what type of object it is). Then create the variable formula. In this case, you want to divide Sales Revenue by Quantity Sold to get the Sales per Unit. Find the Sales Revenue object in the Available Objects box and double-click it; it appears in the Formula Definition box. Select the solidus (division bar) from the Available Operators box, and then go back to the Available Objects box to double-click on the Quantity Sold object. You can check the variable formula before saving it by clicking on the green check mark just to the left of the Formula Definition box, and, if it passes, save the formula by pressing the Save button. Figure 20.18 shows the outcome of the actions just described.

Figure 20.18. The Variable Editor dialog with a completed formula.


Notice the Web Intelligence syntaxall formulas start with an equal sign and universe objects are noted with square brackets [ ] around the object name. The formula shows the Sales Revenue object divided by the Quantity Sold object.

Because these values change at all dimensional intersections in the microcube, this formula changes its results in the report block. So, unlike Excel where the formula is dependent on a particular row/column combination, the Web Intelligence formula is not, and recalculates as the projection in the report block changes. Simply put, you can use it like any other objectswap it in and out of the table, place different dimensions next to it, drill on itand it recalculates the proper values for each appropriate level.

An in-depth discussion on creating report variables is beyond the scope of this chapter. If you would like more information, refer to the Web Intelligence User Guide that has a more thorough discussion of all functions and uses.




Crystal Reports XI(c) Official Guide
Crystal Reports XI Official Guide
ISBN: 0672329174
EAN: 2147483647
Year: N/A
Pages: 365

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net