Topics Map > University of Chicago > IT Services > Business Systems
Business Objects - DeskI - Creating an Aggregate Variable with Context (i.e. Percent of Total)
This article explains how to create a variable with an Aggregating formula (i.e. Sum, Avg, etc.) that has a specific context regardless of where it is placed in the report.
Normally an aggregating formula (i.e. Sum Avg, etc.) is calculated based on the contexts of its location (see KB article Business Objects - DeskI - Creating an Aggregate Variable with Context (i.e. Percent of Total) ). You can add the context to the formula so that you can use it at lower contextual levels (NOT higher levels) to compare individual amounts to the aggregated amount. For example to create a percent of the total or a deviation from the average.
- Add an aggregate value to a table (Sum, count, average, etc) on a column (or use one that already exists)
- Right-click on the cell that displays the aggregated value
- On the menu click on Data and select Define as variable from the drop-down menu
- In the Define the Variable box that appears:
- Assign the variable a name (I usually prefix local variable names with 'My' so I can find them easily)
- Then choose the radio button for Evaluate the formula in its context. BO will automatically append the needed context syntax so that no matter where you use this variable, it will display the amount it's currently displaying.
- Click the OK button, the variable is no available to use in other formulas or elsewhere in the report.
- If the table contains breaks then you can select the aggregated value in step 2 from any of the break levels or the table summary.
- The variable created on a table break can be used in the table at the break level or lower, but not outside of the table or at higher break levels.
- To create a Percent of Total formula using your variable, simply add a column with the amount (or use an existing amount column) and change the data formula to "=<amount> / <My Variable>"