Implementing the BEx Data Function in WEBi Report

[vinay_lohakare5]

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.

Year	Quarter	Number of Employee Hired
2010	Q1	600
2010	Q2	500
2010	Q3	400
2010	Q4	300
2011	Q1	300
2011	Q2	400
2011	Q3	500
2011	Q4	600

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.

Year	Quarter	Number of Employee Hired	Percentage
2010	Q1	600	0.17
2010	Q2	500	0.14
2010	Q3	400	0.11
2010	Q4	300	0.08
2011	Q1	300	0.08
2011	Q2	400	0.11
2011	Q3	500	0.14
2011	Q4	600	0.17

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.

Year	Quarter	Number of Employee Hired	Percentage
2010	Q1	600	0.33
2010	Q2	500	0.28
2010	Q3	400	0.22
2010	Q4	300	0.17

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 ))

Year	Quarter	Number of Employee Hired	% of Total	% of Year
2010	Q1	600	0.17	0.33
2010	Q2	500	0.14	0.28
2010	Q3	400	0.11	0.22
2010	Q4	300	0.08	0.17