In SAP Business Objects the WebI tool is designed to put some intelligence based on selected dimension for calculation of any measures. For example if you select a couple of objects like Month and Sales.Then the BO will group by all the sales records by month however if you drag Year with sales its will automatically group by the measure by Year within its reporting engine and show it by year. This works great for most of the simple scenario.
However there is couple of use case where this approach breaks or does not work all great. We will shed some light on it here
Case 1 : There are some scenario where the measures that are calculated are not fully additive. For example if the measure is based of a Average or percentage then the default syntax approach would not work right. Some times these measure needed to be calculated by a database query rather then the reporting engine and here comes the need for smart measure. That is a topic of discussion for another post however.
Case 2 : What happens if there is a case I want to see the sales figure by month also I want the maximum sales for any given month for the year in the same record. Now here come the need for calculation syntax. Calculation syntax is nothing but overriding the default syntax of the section or table and then replacing it with a new set of dimension to calculate.
Now we will go a little deeper into the calculation syntax.Now as we have understood the problem there is actually more to it. In the specific syntax we are needed to change the group by which is the basis on which we calculate the number also we need to aggregate the results themselves to be printed into specific areas of the reports like the report header/footer , breaks , sections footers or some other places cross tab reports. So there is actually two problems still, one ask the reporting engine to tell the group by clause like in SQL (Input Context ) or we want to tell where we want it to print the formula like report footer/header or a block with a break (Output Context )
Input Context :
Input context is quite simple to understand , basically it is asking the formula to be calculated based on specific context like group by clause in SQL.
For example if I have a revenue measure and its just defined as SUM([Revenue]) and we put it with dimension like year. So the results will be like the query below.
Select Sum(Revenue) , Month
Group by Month
Similarly the group be clause would change it you put it along with Year.However if you want to show the max monthly sales in entire year along with sales for each month you have to use the input context for the max monthly sales in the year. The syntax is as below.Which you can also get easily from internet. Now remember the default context you have in your section is Month.
Max ([Revenue] In ([Year]))
Remember the input context should be in parenthesis and all the dimensions needs to be separated by semicolons. Even if you use one dimension you need the parenthesis.
So what it actually does , it basically adds a measure which is calculated based on a different group by clause and merges the results into your report using your common dimensions. Simple ??
If you are well versed with Oracle SQL and analytic function you can still get the results in the query below.
Select Month , Sum(Revenue) ,
Max(Revenue) OVER ( partitioned by Year )
group by Month
So whats is so great about them , honestly ; NOTHING. Its just the way you need to do complex calculation in SAP Business Objects.
Now lets see here we just defined all the dimensions based on which the calculation needs to be done.So what happens when I have 20-30 or even more dimension which are being used in the report. Do you need to explicitly write all of them into the formula if you need to use the calculation syntax.
The answer is Yes but there are some easier ways to do it..
So here comes the need for addition or deletion of some dimension from the default calculation context and they are " FOREACH" and " FORALL". The objective is very simple "ForEach" adds a new dimension to the default context and "ForAll" removes a dimension to the default context.
Here is a example :
Here the default syntax is YEAR and Quarter. So if I want to calculate the Year total as shown above , I can just remove the Quarter from default syntax using "FORALL". Now the formula would be like below.
Sum([Revenue] FORALL ([Quarter]))
Similiarly if you need to produce the monthly max sales within querter you can use ForEach.
MAX([REVENUE] FOREACH [MONTH])
As this post has already become so large I will spare the talk about the output context to another post..