Skip to Content

This document will explain a Limitation of %GT operand when Allow External Access to this Query is checked and a workaround to achieve the same results .at BEx Query level.

Introduction :

%GT operand shows how high the percentage share is with regard to the overall result. The overall result is the outcome of aggregating the highest results level. Dynamic filters (filters that were not specified in the Query Designer) also play a role in calculating the overall result.

Example Table for Operator %GT <Operand>

Category

Sub Categor

Sales

%GT ‘Sales’

Oral Care

Toothpaste

30

  1. 16.6667

Mouthwash

30

  1. 16.6667

Thoothbrush

60

  1. 33.3333

Personal Care

Liquid soap

60

  1. 33.3333

Toilet saop

60

  1. 33.3333

Result for 2000

120

  1. 66.6667

Overall result

180

100

Business Scenario – User needs a financial report which gives Net Sales based on Category and Sub – Category. Further, he needs % Net sales for each Category and sub category based on Total Net Sales.


To achieve this there are two Approaches:

  1. By using %GT formula in Bex query (%GT will work when Allow external access to this query is unchecked)
  2. By using Constant Selection property of Keyfigure at Bex Query level (%GT will not work and throw error when Allow external access to this query is checked).

Approach 1 :


By Using %GT

STEP 1:

Drag and drop Category and Sub Category from Infoprovider Panel to rows and Net Sales to columns.

The basic layout of Bex query as below :

/wp-content/uploads/2014/04/1_431297.jpg

STEP 2 :

As shown in below screen shot ,under Columns, right click on Key Figure and click New Formula

/wp-content/uploads/2014/04/2_431298.jpg

STEP 3:

As shown in below screen shot click edit button of formula :

/wp-content/uploads/2014/04/2_431298.jpg

STEP 4:

As shown below change the description of  New Formula to %Net Sales and under data function double click NDIV0 (X) . NDIV0(X)will avoid the exception raised by query  of division by 0 . If Divisor if 0 , then result will be 0 .

/wp-content/uploads/2014/04/2_431298.jpg

STEP 5 :

Now double click the Net Sales from Key Figure section :

/wp-content/uploads/2014/04/2_431298.jpg

STEP 6:

Now select %GT operator as shown below :

/wp-content/uploads/2014/04/2_431298.jpg

STEP 7 :

Click ok

/wp-content/uploads/2014/04/2_431298.jpg

STEP 8 :

Save the query and to see the output execute Tcode RSRT . As show in below screen shot

Overall result for Category = -43,530,655,241.44 and Net sales for Toothpaste = 1,975,377.52

Therefore % Net Sales =( ((1,975,377.52 )/ 43,530,655,241.44)* 100 =00.45 approximately . It is concluded that Toothpaste Net Sales is Approximately 26% of total Net Sales .

Screen shot for toothpaste % Net sales and Net Sale

/wp-content/uploads/2014/04/2_431298.jpg

Screen shot for overall category Net sales

/wp-content/uploads/2014/04/2_431298.jpg

NOTE : The above mention steps are only possible  when Allow external access to this query is unchecked . IF when  Allow external access to this query  property is checked then while saving , the query throws a below mention error :

/wp-content/uploads/2014/04/2_431298.jpg

/wp-content/uploads/2014/04/2_431298.jpg

If you check allow external access then above error pops up

Detail description of error :

/wp-content/uploads/2014/04/2_431298.jpg

Now suppose you want this query to be allow external access to this query checked , there is another way to achieve the above mentioned desired result

Approach 2 :

STEP 1 : Create New Calculated Key Figure with NoDIM

/wp-content/uploads/2014/04/2_431298.jpg

STEP 2: Create Calculated Key Figure using NODIM operator to used keyfigure without any currency

/wp-content/uploads/2014/04/2_431298.jpg

STEP 3: Drag and drop CKY and check the Constant Selection  property as shown below :

/wp-content/uploads/2014/04/2_431298.jpg

With Constant selection property , Calculated key figure hold constant aggregated Net sales irrespective of filters Pane restrictions.

STEP 4:  Now create New selection as shown in below screen shot:

/wp-content/uploads/2014/04/2_431298.jpg

STEP 5: Click Edit and drag and drop Net Sales and put all the restriction which are at Filters pane(Query level restrictions)

/wp-content/uploads/2014/04/2_431298.jpg

STEP 6: Check the Constant selection property for Net Sales with Query selection as shown below:

/wp-content/uploads/2014/04/2_431298.jpg

STEP 7: Now create a % Net Sales formula which calculates  Percentage Net Sales to Net Sales with query selection as shown below:

/wp-content/uploads/2014/04/2_431298.jpg

%A operator is used for percentage calculation.Click ok

STEP 8: Save Query . Final Query Layout  with Allow External Access to this Query checked  as shown below :

/wp-content/uploads/2014/04/2_431298.jpg

Execute query in RSRT to validate the result. As shown in below screen shot

Net Sales with Query Selections=  [43,530,655,241.44]

Net Sales = [2,236,424,774,461.19]

% Net Sales = ([2,236,424,774,461.19] / [43,530,655,241.44]) * 100 = .0045

/wp-content/uploads/2014/04/2_431298.jpg

Referred links :

http://help.sap.com/saphelp_nw04/helpdata/en/9b/c1993c54966f3ae10000000a114084/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/e7/5f983c1a356858e10000000a114084/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/EN/e2/16f13a2f160f28e10000000a114084/content.htm

To report this post you need to login first.

5 Comments

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

  1. Suman Chakravarthy K

    Seems to be very nice article 🙂 When I have time, i will definitely go through thoroughly. But over all I can say it is a very good workaround. Thanks for sharing with the community.

    (0) 

Leave a Reply