Percentage scenarios in SAP Business Objects Web Intelligence

Introduction:

This document will provide the overview on how to display the percentage change of KPI of any dimension by period wise.

I have developed this report when the end user wants to see the percentage change of KPI year wise or quarter wise for consecutive periods.

Scenario:

We need to show the particulars and its business amount for each quarter and we need to calculate the percentage change in each Quarter.

Percentage Change: (Amount (Current Year) – Amount (Previous Year) / Amount (Previous Year)) * 100

Currently as per WebI specifications, we can provide the period wise KPI’s in cross tab. So, if we need to display the percentage change periodically, we need to add the Percentage Column on right of KPI. This in result the percentage column will be added after every period as shown in screenshot below.

Formula used to calculate Percentage Change:

But we don’t need to display percentage change column after first period and also it will not give the proper calculation amount.

We need to create a report with particulars, amount for first two periods then percentage change column and amount with corresponding percentage change sequentially as per below requirement.

System Requirements to achieve this scenario:

◉ SAP Business Objects 4.0 SP2 FP13 (Web Intelligence Rich Client or BI Launchpad)

◉ DB2 C Express V9.0 (free from IBM).

Steps to achieve:

1. Create a Universe based on the required table and add the necessary objects

2. Create a WebI report on top of that universe.

Here we are going to filter out the amount of Maximum year from the report and add it in the Vertical Axis and need to create some formula to achieve the percentage change.

3. Create a cross tab with particulars as Vertical Axis, Period as Horizontal Axis and amount field as body axis

4. Create a variable to filter out the maximum period from the report.

5. Right Click on the block and add that Variable in Report filter.

6. Insert a new column next to the particular dimension and add the following formula to get the amount of maximum period.

7. Give the heading for first column as Nofilter (Max(Period)) as per requirement.

8. Now insert a new column on the right of Amount field with the name of Percentage Change.

9. We will get the layout as per requirement.

10. Now we need to get the amount of maximum year when we need to calculate the percentage for maximum period.

11. By using below formula, we can get the amount of maximum year.

12. While calculating Percentage Change, we will be getting the previous period value in that percentage change column as we inserted the Percentage

change column next to previous period. To get the Current Period value we need to get the amount of previous column value.

13. To get the previous column value we can use the WebI default function Previous([Amount]).

14. So now, If it is a maximum year then we need to take Amount of Max Period in Calculation for current period else we can take previous period value.

15. Now we got the Current Period Value, so we can calculate the Percentage Change Value as per formula.

16. Apply the Percentage Change variable to Percentage Change column.

Now we will get the report as per Requirement.