Bex Query Design

[GVR]

Hello Experts,

Kindly please look into below Requirement and Help me to Create the Report in query designer.

Source Layout

Same Layout need to create new Query, i have tried to Create but In % is not able to create in Rows.

Production ( SALES OF MACHINES+USED MACHINES+SALES OF SERVICE+RENTAL+Whip Variation+Sales Others) it will give in the report out is overall result like below based on that we need to calculate in %. (Production /production) * 100

Like that we need to create other formulas, and it should be show in Rows.

Thanks and Regards,

Venkat.

[MKreitlein · Accepted solution]

Hello @GVR 

In my opinion, the only solution for your requirement is

a) using two structures in your query: https://help.sap.com/doc/saphelp_nw73ehp1/7.31.19/en-US/46/81fc706a23468ce10000000a114a6b/frameset.h... 

b) working with reference cells and cell-based formulas: https://help.sap.com/doc/saphelp_nw73ehp1/7.31.19/en-US/d2/02223c5f00612be10000000a11402f/frameset.h... 

In that way, you have to restrict selections in the columns and in the rows (here the hierarchy nodes), and then you can create a formula for your percentage calculation.

BR, Martin

[GVR · Accepted solution]

Thanks Very Much

[umasaral]

Hi 

Got it. To calculate the percentages in your BW report similarly to the Source report,
you need to ensure that each node's values are summed up and then express each value as a percentage of the total for that node's level.
Ensure that your BW report has the same hierarchy and that totals are calculated for each node.
Then, calculate the percentage by dividing each node's value by its respective total.


Note:
Check if your BW report has the same hierarchical structure as in your Source report.
Ensure all nodes and sub-nodes are represented properly in your BW report.

In BW, calculate the total values for each node in each column (e.g., Actual, Budget).
This step is crucial to ensure you have the base for percentage calculations.
After calculating the totals, compute the percentage for each node relative to its total.
Identify the Node TotalFor each node (e.g., Production), sum up the values for all columns (Actual, Budget, etc.)
Use the following formula to calculate the percentage for each value within the node:
Percentage=(Total for NodeValue​)×100
This will give you the percentage of each value in relation to its node total.

Implement in BW
Create calculated columns for percentages in your BW report. For example, add a column for Actual % and Budget %.
Configure these columns to compute percentages based on the totals you calculated for each node.
Ensure the percentages in your BW report align with those in your Source report. Cross-check totals

To calculate the percentage for Production:

Actual Percentage:
\text{Actual %} = \left( \frac{1000}{3000} \right) \times 100 = 33.33\%

Budget Percentage:
\text{Budget %} = \left( \frac{1200}{3600} \right) \times 100 = 33.33\%

[umasaral]

Hi

create a report in Query Designer with percentage calculations, follow these steps:
Add the required key figures for Production categories (e.g., SALES OF MACHINES, USED MACHINES, etc.) as columns.
Create a new key figure for total production using a formula, e.g.,
[SALES OF MACHINES] + [USED MACHINES] + [SALES OF SERVICE] + [RENTAL] + [Whip Variation] + [Sales Others]

Create a calculated key figure for each percentage using the formula:
([SALES OF MACHINES] / [Total Production]) * 100
Repeat for other categories.

Ensure all key figures, including calculated percentages, are added to the Rows section in Query Designer.
Ensure the query aggregation is set correctly to reflect the percentage calculations.

[GVR]

Hello Umasaral,

Thanks for you are quick reply. I think you are not understanding my question.

i have the report in Source like Gl Account it will using with Hierarchy in Rows and Columns is Actual Budget etc.

in Rows in Gl Account Hierarchy Nodes like Production, Operative Expenses etc. for that i need to calculate the % based on the node level. Please find the below BW report and Source report. In source after every node, they are calculating the total (like Production, Operative expense for all the Columns like Acutal, Budget etc) and after that they are the % (find in source report screen short for the same) like that they need in BW also. Could you please check and help.

BW Report

Source Report

Thanks and Regards,

Venkat.