Home / Products / BusinessObjects / What to do when report design requirements call for customization beyond the default aggregation in Webi
6. Creating a variable - modifying context to customize Web Intelligence calculations

What to do when report design requirements call for customization beyond the default aggregation in Webi

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:

1. table in webi

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:

2. table in webi with monthly breakout

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.

3. modifying context to customize Web Intelligence calculations

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.

4. modifying context to customize Web Intelligence calculations - Max Revenue

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.

5. modifying context to customize Web Intelligence calculations - Max Revenue Month

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.

6. Creating a variable - modifying context to customize Web Intelligence calculations

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.

7. best month- modifying context to customize Web Intelligence calculations

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:

8. adding where clause- modifying context to customize Web Intelligence calculations

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.

9. highest month - modifying context to customize Web Intelligence calculations

I’m up for a challenge.  Do you have a problem you can’t solve in Webi?  Leave a comment below.

About Steve Thatcher

Steve is a Senior Business Intelligence Consultant with InfoSol. He has extensive design and development experience with both the SAP BusinessObjects and Yellowfin business intelligence suites. He is a Certified Web Intelligence Application Associate and, in addition to teaching the SAP Web Intelligence report design courses offered at InfoSol, provides Web Intelligence services to domestic and international customers. In addition to Web Intelligence, Steve is also a Certified SAP Dashboards/Xcelsius Application Associate, and continuously stays abreast of the many offerings of the SAP business intelligence solutions suite. Steve’s business intelligence background is complemented by his over 13 years in Corporate Finance gaining experience in financial modeling, reporting, spreadsheet and information system development.

Check Also

Main Landing Screen

Dashboards Helping Drive Healthcare data from Analysis to Action in the UAE

One of the most awe-inspiring presentations at this year’s IBIS conference was about how a ...

One comment

  1. This was really useful and helped me 95% with a problem I was having. My extension to the above would be

    I) what if you have multiple months with highest revenue – is it possible to have a column that shows both?
    II) is there a way to ignore certain months i.e. I want highest month but excluding Jan and Feb.