Calculation on lower level - ForEach

[kohesco]

Hi,

Some time ago I posted this document Dynamic Condition in Bex where I tried to explain a nice use of variables in conditions.  Now I had to create a summary report on that data in a WebI report.  Because the lack of support of conditions I had to find my way somewhere else.  So I stumbled against the "ForEach" parameter in the formula vocabulary of WebI.

Trying to help fellow members I noticed as well, I'm not the only one struggling with how to display aggregated data based on some calculations on a lower level.  So, I thought why not put it all in a nice document.

Here we go ...

Example.

In the Bex-Report I had to show for all materialgroups all the supplements or all the purchase orders from one year and seeing all the other supplements for the other years. Now in my aggregated report.  They just desired to see the percentage of supplements and orders for each year.  So I had to show the orders of each year in combination of all the supplements of all years regarding to that order.  In my previous post I managed this just for one year at the time.  Now they wanted to see the data over the previous 4 years.  The difficulty is to calculate the supplements I have to check first if there is an order for that year.  In Bex I handled this to put a condition on the orders (everything bigger then 0), fetch the list of purchase order documents and use this list to calculate the supplements.

In WebI I did this way ...

First calculate current year, previous year, current year -2, ...

Secondly I calculated the orders per year, You can do it in WebI or in the bex query designer as restriced keyfigures.  I opted in my final version for the second solution.  To push all calculation as deep as I can in my flow.

Very Important remark is to add alreayd in this stage the ForEach Purchase Order, this way we force the calculation engine to look at the PO's.

Secon step is to copycat the condition feature of BEx.  We created a check variable for this.

Remark as well, once again, you have to add the  ForEach Purchase Order!

Now we can calculate the supplements for the orders with a purchase value greather than 0.

Note that we calculate on N° PO of level and show the sum, as we want to show our result on Materialgroup level.

Finally the actual result put the number format in percentage and case closed.

Repeat those steps for all the years you want to show.

Use the "year variables" in the output of your table.  And because it is used in your calculations as well.  This solution is dynamically!

Just remember to force the calculation engine EACH TIME to FOREACH(dimension)!

Note as well that I do not use N° Purchase Orders in my report, and query stripping is enabled, the calculation engine is forced to look at the N° PO and by consequence the N° PO is not stripped from my query!