Skip to Content

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
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply