So what do you do when the report design requirements for a webi report call for customizations beyond the default aggregation?
Modifying Context to Customize Web Intelligence Calculations
The default aggregation in Web Intelligence provides a dynamic way to transform data on the fly. With simple drag, drop, and mouse click actions, numbers are automatically recalculated to reflect a user’s interaction with report elements. However, there are times when report design requirements call for customization beyond the default aggregation. In these scenarios, understanding how to modify Webi’s calculation context can be an invaluable tool in a report designer’s toolbox.
For example, let’s look at the following data in the default vertical table returned by a Web Intelligence query:
Revenue is aggregated and displayed by year as a result of the Order Year dimension object being included in the table with the Revenue measure. In addition to yearly totals, each year can be broken down to reflect the revenue total for each month. Adding the Order Month to the table, and a break on order year would provide the following monthly breakout, in descending order of the $14,112,440 total revenue for 2007:
Let’s say we’ve received report requirements that call for only the yearly revenues from our original table, and two additional columns that display the highest monthly revenue amount, and the name of the month that revenue occurred in. Looking at the monthly breakout above, we see that August’s $1,516,548 value is the highest revenue amount out of all of the twelve months. Let’s add two columns to our original table to house this data.
Another way to describe the Highest Month value is to consider it the Maximum revenue amount out of all the twelve months. Thus, to calculate the value for the Highest Month column, we can use the Max() function for our Revenue dimension. With only Order Year included in the table, the maximum revenue is calculated for total year, and is thus equal to the $14,112,440 for 2007.
What the report requirements call for however is the Max([Revenue]) month for each year. So, the August value of $1,516,548 (see monthly revenue breakout screenshot above) should be displayed in the Highest Month column for 2007. We need to bring the Order Month dimension into the calculation without physically dragging the Order Month dimension into the table. To do this, we can modify the Input Context to bring the Order Month dimension into the calculation.
By using the ForEach keyword, we are instructing Webi to continue considering all of the existing dimensions in the table (in this case Order Year) in the calculation, as well as the dimensions listed after the ForEach keyword. Thus, Webi will now look into each year’s revenue, analyze the corresponding monthly values, and display the maximum, or highest value found.
Now that we are correctly displaying the highest monthly revenue amounts for each year, let’s display the month name for each of these amounts in the Month Name column. For example, let’s display August for the 2007 row as this is the month the $1,516,547 occurred in. We will again turn to the Max() function and Input Context, and add an Output Context to create the following variable.
This variable is calculating the max revenue for Order Year and Order Month, and is doing so at the Order Year output level (Output Context). Note the Input Context is using the In keyword instead of ForEach. The In keyword acts as a reset in that it instructs Webi to disregard all dimensions in the table (Order Year in our example), and only consider the dimensions listed after the In keyword. We want this variable to always consider the Order Year dimension, so we add it directly into our variable.
To see this in action, we can add the v_Best Month variable to the monthly breakout table above, and add a comparison column that provides a toggle value (0 or 1) to determine if the revenue value for each month is equal to the max monthly revenue. Note August displays a 1 indicating its revenue value = the max revenue value (v_Best Month) out of all of the twelve months.
We can now leverage this toggle logic to display the Month Name in our original table by adding the following formula to the Month Name column:
This formula is using a Where clause to analyze the monthly revenue amounts and determine if a revenue amount is equal to the v_Best Month variable. If the revenue is equal to v_Best Month, and is thus the highest revenue amount, the corresponding month name is displayed. We see, for example that August is displayed for the 2007 row. We now have our completed table per the report requirements.
I’m up for a challenge. Do you have a problem you can’t solve in Webi? Leave a comment below.