Calculations

---

Calculations allow you to add subtotals to a table that contains breaks. If the table does not contain any breaks, then the calculations are grand totals for the entire block. In most instances, if you add a calculation to one measure, you will want them on all measures in a report. The subtotals are inserted as formulas in the break footer. Table 18-2 lists the calculations available in the Slice and Dice Panel as well as the formula syntax inserted in the break footer.

Table 18-2: Calculations Available from the Slice and Dice Panel

Calculation	Explanation	Formula
Sum	Adds the values for a particular measure. SUM is the default calculation when you first apply a calculation to a variable. However, SUM may not make sense for certain measures such as ratios and percentages.	=SUM(<Measure Variable>)
Count	Counts the unique values within a break. Use this with dimension or detail objects.	=COUNT(<Dimension Variable>)
Count All	If you want a row count, rather than just the unique values, use Count All.	=COUNTALL(<Dimension Variable>)
Average	Calculates the average based on the individual measures displayed in the table. If you hide certain rows of data through a filter, the values from the hidden rows do not, by default, affect the subtotal. Average is not a weighted average, so for things like average price, you may want to create a formula to get a weighted average or use extended syntax, discussed later in this chapter.	=AVERAGE(<Measure Variable>)
Minimum	Displays the minimum value for a particular measure within the break.	=MIN(<Measure Variable>)
Maximum	Displays the maximum value for a particular measure within the break.	=MAX(<Measure Variable>)
Percentage	Calculates the percentage contribution a particular break level makes to the grand total. In addition, it inserts a new column in the table that shows the percentage each row contributes to the individual break level. This calculation uses the Context Operator ForAll described later in this chapter.	=<Measure Variable> /SUM(<Measure Variable>) ForAll (<Dimension Variable>)

The Calculation dialog box is somewhat  context-sensitive, depending on the object type (character, date, number); however, it does not consider the object qualification (dimension, measure, detail). For example, SUM appears for numeric dimension objects. This does not make sense from a business perspective; you may want to COUNT the number of products or number of wines, but it is not something you will want to SUM. Therefore, pay attention to which column you have selected before inserting the calculation function.

Using the same sample table from the preceding section, add AVERAGES and MAXIMUMS to the three measure columns:

1. From the Slice and Dice Panel, select the Average Rating column.

2. Click Insert Calculation.

3. To select the type of calculation, double-click the Calculation icon beneath the Average Rating column. This opens the Calculations dialog box.

   

4. Remove the check from Sum and select Average and Maximum. Click OK to close the dialog box.

5. Repeat steps 1–4 for the remaining measure columns.

6. Click Apply to insert the calculations into the table.

7. Minimize the Slice and Dice Panel to see the effect of the break.

   

   Note 

   If you do not have your break options set to include a break footer, the calculations are inserted only as grand totals at the end of the report. Also, if you apply a break after you have inserted calculations, the calculations are not repeated in the break footer and remain as grand totals. To have them appear as subtotals in the break footer, first set the break, then insert the calculations.

BusinessObjects inserts row descriptions for each different calculation you insert. This can sometimes be quirky—for example, averages may appear on multiple rows; the break value may be overwritten; the calculation description may be missing. The calculation description gets inserted in the column immediately preceding the first calculation, so you are less likely to encounter problems when this column is not the break column. To fix these problems, you can click Reset within the Slice and Dice Panel, but then you lose other formatting changes.

An alternative is to drag and drop the different break values and/or calculation descriptions to make the break footers more visually appealing. If you are uncertain about which calculation appears in the various columns and rows, use View | Structure to see all the formulas. In the following example, the Average calculation description has been modified to concatenate the country name; concatenation is described later in this chapter, in “Concatanation.” The calculation descriptions for the grand totals have been moved to the first column; the generic Average cell text has been replaced with Average for ALL Countries to more clearly identify it as a grand total.

Multiple Sorts, Breaks, and Calculations

As you insert sorts and breaks, you may find the tables are easier to read if the order of the columns reflects the sort and break order. You can easily reorder columns within the Slice and Dice Panel by selecting a column and dragging it to a new position within the Block Structure. Figure 18-3 shows a sample structure using data from the EFASHION universe. The leftmost column, State, is the primary sort order and primary break. Cities is the second sort column but does not contain a break. Clothing Line is the third sort column and contains a break. In actuality, the Sorts on State and Clothing Line are redundant as the breaks force a sort; however, they more clearly and accurately reflect that City is the third sort priority. Clothing Line, the third column, contains the break header so that the column headings are repeated for each change in clothing line. The State break does not contain a break header; otherwise, an additional row of column heading would appear for each change in State. Sales Revenue and Quantity Sold have two calculations, Sum and Percent. The bottom of Figure 18-3 shows a corresponding sample report in which the break footer rows have been formatted to make the subtotals more apparent. Refer to Chapter 17 for instructions on formatting a section of the table.

Figure 18-3: The Slice and Dice Panel and a formatted report with multiple sorts, breaks, and calculations

---