Implementing the BEx Data Function in WEBi Report

Senario:

The BEx query had the Percentage Function – >%GT (SUMGT) used in one of the key figure. % GT (SUMGT)function gives the Percentage Share of overall result. The overall result means the result of aggregation at the next level in the list.

If a BEx query is using DATA functions it cannot be enabled for the External access, and cannot be used as a data source for BO Reporting. Thus we need to remove these Data functions from the query and need to implement at BO WEBI report level. Below solution shows how the %GT function can be achieved in WEBi.

Solution:

Example Data:

Below report shows the number of employee hired by year and quarter. In BEx query we can get the percentage share of overall result using the %GT function.

Same can be implemented in WEBi using the already available functions.

Percentage Function:

We can use the Percentage function for calculating the percentages. The function calculates the percentage of a number in relation to its surrounding context. We can use the formula Percentage ([Number of Employee Hired]) and get the below result.

The function calculates the Number of Employee hired as a percentage of the number of employee hired. The function will work even if the report is split into sections by year. In this case the surrounding context outside the table becomes the total employee hired as seen below.

Sum Function:

We can also make use of the Sum function along with the extended syntax keywords Report and Section to calculate the overall total employee hired and the yearly employee hired respectively.

Column “Percentage of Total” has below formula:

[ Number of Employee Hired ]/ ( SUM ( [ Number of Employee Hired ] In Report ))

Column “Percentage of Year” has below formula:

[ Number of Employee Hired ]/ ( SUM ( [ Number of Employee Hired ] In Section ))