|< Day Day Up >||
Most of the data you have manipulated in this chapter comes from results that a data provider returns to the microcube, which is then called a
. As you added Calculations to the report, BusinessObjects automatically inserted formulas. Formulas allow you to manipulate the raw variable data in a number of ways. Because the manipulation is against the local report variables, they can use a proprietary BusinessObjects syntax that overcomes many limitations of SQL. You can use formulas to create new variables. The
When you create or modify a formula, the formula is available to all reports within a document. BusinessObjects provides several sets of formula functions that allow you to manipulate different types of data. For example, the calculations via the Slice and Dice Panel are referred to as
they allow you to create different types of subtotals (
, and so on). Aggregate functions also allow you to create running totals, averages, and others. The conversions to Euro were accomplished through a
); numeric functions allow you to manipulate
Figure 18-8: The Formula Editor helps you write complex formulas.
There are multiple ways to launch the Formula Editor and to insert a formula into a block:
Insert a blank column in a report and then select Data Edit Formula.
Display the Formula bar with View Formula Bar, then click the Formula Editor button from the bar.
Select the pull-down
Once you have created a formula or variable, you can select it either from the Report Manager or the Slice and Dice Panel to insert it into an existing block. With this approach, BusinessObjects will automatically insert a new column for the new formula.
As you work with the Formula Editor, you can perform simple calculations such as = < Unit Price > *1.06 or you can work with functions. When you work with functions, you insert the function first, then the variable to which it applies and any additional parameters. As you select an individual function, the Formula Editor displays the syntax at the bottom of the dialog box. Notice in Figure 18-8 that it also displays the output type for the particular function; this is important, as the variable type and function must be compatible. Certain functions will change the output type. For example, the date function Year will change a date type field to number; once it is converted to number, you can no longer use other date functions against the new variable unless you convert it back again.
From within the Formula Editor, you also can click Function Help to access more information about the function and see examples.
As you create your own formulas, follow these guidelines:
Always start the formula with an = sign.
When you reference a variable name, enclose it in brackets <>. In Figure 18-8, <Unit Price> is the variable.
The formula function must
Enclose text parameters in quotation marks.
Do not enclose numeric parameters in quotation marks.
If you want to use an alerter on a formula column, you must first convert the formula to a variable.
When you want to reuse a formula in different reports or perhaps in another formula, you can either explicitly create the formula as a variable or convert an existing formula to a variable. By identifying a formula as a variable, you also can apply alerters to the new variable or store the formula as a dimension to use for drill-down analysis.
To create a variable from a new formula:
Select Data Variables.
The Variables dialog box lists the current variables, formulas, and constants. From the Variables dialog box, click Add.
BusinessObjects displays the Variable Editor. On the Definition tab, you specify a name and a qualification. The variable name becomes the column headings in reports and is displayed in the Slice and Dice Panel and the Report Manager.
Select Dimension for Character, Date, and Numeric fields that you may want to
As you define the formula, BusinessObjects will propose a qualification based on the variables you use in the formula. If your formula contains a measure variable, it proposes that the new variable also be a measure. The proposed qualification may not always be correct. For example, if you want to flag highly rated wines (
), then BusinessObjects will interpret it as a measure, when it should be a dimension given the result of the formula. Also, after you select a variable to use in the formula, the Type box in the
The formula tab displays the formula editor shown earlier in Figure 18-8.
Once you have defined the variable and the formula, click OK. The new variable now exists in the document or microcube. To display it in the report, use the Slice and Dice Panel to drag it to a new column.
If your report already contains a formula, you can convert the formula to a variable.
Select the column that contains the formula.
From the pull-down menu, select Data Define As Variable or click the Define As Variable button from the Formula bar.
Enter a Name. This name becomes the variable name displayed in the Slice and Dice Panel and Report Manager.
You can choose to keep the formula generic or to fix the context in which the formula is evaluated. Contexts are explained in the
Click OK to close the Define The Variable dialog box and return to the report. While the results of the calculation in the report may not have changed, the
BusinessObjects will have automatically assigned a qualification (dimension, detail, or measure). If you want to change the qualification, choose Data Variables, then Edit.
With calculations and formulas you build yourself, BusinessObjects reevaluates the formula to give results based on the data displayed in the current block. This
As an example, refer to the rows in the report in Figure 18-9. The second mini-table in the report contains seven rows of data with individual unit prices for French wines produced by Chapoutier. Even though there are multiple wines for a particular
Figure 18-9: Calculations in columns and footers are reevaluated according to their context.
defines which variables or subtotals within a report go into the calculation; the input context controls the level of detail in the calculation; therefore, these contexts are critical in calculating correct averages and counts. The
determines at which point a
=Function(<Variable> In InputContext ) In OutputContext
BusinessObjects provides three operators that allow you to control the contexts:
In Works with context keywords such as Body , Block , Report , and CurrentPage
Works with dimension
ForAll Indicates that the grand total for the dimension should be used in the calculation
The following table summarizes the different
Area of Report
The data displayed in the body of the table. By default, BusinessObjects reevaluates calculations according to what is displayed in the body.
The individual row values; these may not be displayed in the body of the report, but they exist in the document. In order to get “weighted” averages, replace the Body input context with Block.
The entire report. This component is relevant only for reports that contain multiple blocks.
The current page within a report. Use this when you want to get a subtotal for one page in a multipage report.
< Variable >
< Variable > contexts specify at which point a new subtotal should be calculated. If you are using a cumulative aggregate function such as RunningSum , you can specify the context to be reset at a particular break level by inserting a semicolon before the break variable: CumulativeAggregate (< measure >; < dimension variable >).
To see the extended syntax of a formula with the explicit contexts:
Display the Formula bar (View Formula Bar).
Select the measure column or the break footer that contains the subtotal calculation. In the Formula’s text box, the simple formula is displayed as:
Position the mouse over the Formula bar in the text box (but do not click in the text box). The full formula with the context is displayed as a tool tip.
The full formula with contexts for the Average Price column is
=<Average Price> In (<Producer>, = RowIndex(), <Country>, <Varietal>, <Vintage>)
In the Producer break footer, the full context for Average Price in Figure 18-9 is
=Average(<Average Price> In Body) In <Producer>
The extended syntax for Average Price for the entire report
=Average(<Average Price> In Body) In Report
When automatic aggregation is re-enabled, the Average Price in Figure 18-9 is automatically calculated from the four dimensions displayed in the current table. The three rows for the vintage 1996 are
This now poses a problem with the footer averages. Is the average price for Chapoutier wines $112.34, as shown in Figure 18-9, or is it a new result of $105.90 because one row at $128.43
If you want to use the eight individual rows from Figure 18-9, rather than the six rows displayed in the body in the preceding table, to calculate the average, then change the input context from In Body to In Block .
To reflect an average price of $112.34 for Chapoutier wines, the formula at the break footer should be
=Average(<Average Price> In Block) In <Producer>
To reflect a total average price for both
=Average(<Average Price> In Block) In Report
So far, these calculations have been affected by the columns that appear in the report. Another feature that affects the calculations are filters. So if you were to filter the report in Figure 18-9 by Varietal or by Vintage, all the footer averages would be recalculated. If you do not want them to be recalculated, then insert a NoFilter function into the formula:
=NoFilter(Average(<Average Price> In Block) In Report)
You can copy a cell with a subtotal calculation to the main section of a report. This cell will reflect the grand total or overall averages for the entire report, regardless of the columns displayed and the rows filtered. The Average Price of $163 in Figure 18-9 is for all French wines, not just the two producers displayed in the table. Inserting NoFilter into the footer calculation has the same effect.
Extended syntax and contexts can be a painful concept to understand. So I want to make some sweeping
Do not worry about contexts for rows within a table that deal with straightforward sums.
Understand input contexts for averages, min, max, and count for break footers.
For rows within a table that use averages, min, max, or running aggregates, understand variable contexts and resets.
Leave output contexts at their default.
BusinessObjects provides dozens of functions that allow you to manipulate and better analyze the data. Some of the more frequently used functions are covered in the next sections.
If Then Else syntax appears in the Operator box of the Formula Editor. Use If Then Else logic to create complex formulas and powerful alerters.
The following formula describes a wine as Highly Rated if the Average Rating is > 90:
=If <Average Rating> > 90 Then "Highly Rated" Else "Normal"
To combine multiple comparisons in the If statement, enclose the comparison in parentheses. The following formula describes a wine as a Good Value if both the rating is above 90 and the Average Price is less than $100:
=If (<Average Rating> > 90 And <Average Price> < 100) Then "Good Value" Else "Normal"
In calculating ratios, pay attention to the calculation order to get a correct subtotal. If your universe contains objects that are ratios, you must ensure that you create a calculation to recalculate the ratios at each subtotal or break level:
=Sum(<Sales revenue>)/Sum(<Quantity sold>)
If the universe does not already contain a ratio or price object, you may want to create a formula that calculates the price row by row:
=<Sales revenue>)/<Quantity sold>
As long as you do not convert this formula to a variable, you can reuse this same formula in the break footer. This is where dynamic contexts are truly wonderful, as BusinessObjects does not require you to create a new calculation for each subtotal; it does it automatically for you. There was a time in early versions in BusinessObjects in which you had to explicitly define which subtotal and break level to use; some other BI tools still require you to do this. As you added and removed breaks, you would have to manually redo the ratio/price formulas at each break level. For example, if you have a table that contains breaks at clothing line and year, you can insert the same formula = < Sales revenue > )/ < Quantity sold > in the two break footers; the extended syntax is different for each footer, as shown in the following table. If you remove or add breaks, the new context is automatically specified. Awesome, isn’t it?
Formula with Extended Syntax
=( < Sales revenue > / < Quantity sold > ) In ( < Lines > , < Year > )
First, Clothing Line
=( < Sales revenue > / < Quantity sold > ) In < Lines >
=( < Sales revenue > / < Quantity sold > ) In Report
To ensure that you do not receive a divide by 0 error, include an If statement to test the denominator for zero values.
=If Sum(<Quantity sold>)>0 Then Sum(<Sales revenue>)/Sum(<Quantity sold>)
As part of the formula, you cannot specify a text value such as “NA” to display for those rows in which the Quantity Sold was 0, as “NA” is a text value and all other values are numeric. However, you can format the
Within the Date functions, BusinessObjects provides several functions that allow you to interpret date columns. In certain circumstances, the function converts a date variable to a numeric type variable. It’s important to pay attention to this nuance to avoid calculation errors.
CurrentDate reads the current date according to your computer and returns a date type.
Year formats a date variable to a four-digit year and returns a numeric value.
The following formula creates a Last Year Sales variable. If the year of the date on the computer minus 1 (last year) is equal to the year in the report, then display the amount sold; else, return 0.
=If (Year(CurrentDate())-1=<Year>) Then <Amount Sold> Else 0
=If (Year(CurrentDate())-1=<Year> And <Month Number><=MonthNumberOfYear(CurrentDate())) Then <Amount Sold> Else 0
In certain circumstances, you may want to combine the values of two variables into one via the Character function Concatenation. This is useful for creating one formatted field that has both a first name and a last name in a form letter. Alternatively, you may have a column that contains an order number and an order line number. The order line number in itself is not a unique identifier unless it is combined with the order number.
Concatenation does not automatically trim blank spaces in fields, nor does it add spaces to make the newly combined cell legible—for example, to leave a space between a first name and a last name. To accomplish this, you can use RightTrim to trim blank spaces from a variable, and you can nest Concatenations to insert spaces between variables.
The following example creates a newly combined column that has both first name and last name. The “ “ in the formula
=Concatenation(Concatenation(<Cust First Name> ," ") ,<Cust Last Name>)
A similar structure for order number and order line would be
=Concatenation(<Order Number> ,<Order Line>)
You do not want too many spaces between the first name and the last name. If the first name variable has extraneous blank spaces at the end of the field, then incorporate RightTrim to remove the blanks as follows:
=Concatenation(Concatenation(RightTrim(<Cust First Name>) ," ") ,<Cust Last Name>)
An alternative to the Concatenation function is the ampersand (&) operator. The ampersand allows you to combine several fields at once and so may be easier to read than nesting multiple Concatenation functions. The preceding formula would be restated:
=<Cust First Name>&" "&<Cust Last Name>
The following formula shows how you can incorporate multiple ampersands with other functions. The Substr function allows you to display part of a field starting in a particular position. To display the first initial of someone’s name, start in the first position and display one character:
=<Salutation>&". "&Substr(<Cust First Name>,1,1)&". "&<Cust Last Name>
Cumulative aggregates incrementally aggregate each row of data within a report. By default, BusinessObjects will keep adding data through each break level in a report. If you want to create a running subtotal for a particular break level or mini-table, then insert a semicolon and a dimension variable to reset the calculation context:
CumulativeAggregate (< measure >; < dimension variable >)
RunningAverage calculates a running average for each row of data in the report. Compare the differences in RunningAverage s in the following report. The first measure column uses the standard syntax. The last column in the report resets the average for each break in Producer. Business Objects refers to this variable as a reset context :
|< Day Day Up >||