Usually the scaling factor in BI-IP planning queries or BI reporting queries is set for each key figure at query definition time in the query designer. The following paper describes an approach to display and plan data in BI-IP with dynamic scaling factors. The term dynamic scaling factor means, that the user is able to select and change the scaling factor while he is working with the query at runtime. The approach can be used for reporting purposes as well. In fact we first design the approach for reporting queries and later enhance it for input ready queries, so that the queries keep their input readiness. The approach is based on “inverse formula” concept in BI-IP.
Author(s): Matthias Nutt
Created on: 19 January 2014
Matthias Nutt worked for SAP (Switzerland) AG. He was a professional consultant from 2002 to 2012 and worked in the area of BW in close co-operation with the development and support team. His work spanned a variety of roles including project manager, technical project manager, consultant, developer, primary and development support.
BW 7.X, BI-IP
We reuse several objects which have been used in the document “Raising ‘pop up to confirm’ dialogs in SAP web templates” Raising “pop up to confirm” dialogs in SAP web templates. These objects are the infoprovider DEMO_00 and the aggregation level DEMO_ALV. Please check the detailed setup there. Nevertheless this paper demonstrates the general approach and any other aggregation level may works for our purposes.
For the demonstration outlined in this paper, a simple query as shown in the screenshot below is used. It contains the company code 0COMPCODE and the currency 0CURRENCY and an input ready key figure 0AMOUNT.
Outline of the approach
A query has an input ready key figure. Usually the scaling factor for this key figure is set statically in the query designer at query definition time as we can see in the following screen shot.
Nevertheless for a global company it is necessary to allow data entry using different scaling factors for e.g. different companies and their currencies and depeding on the size of their business. In reports this can be achieved easily. We just need to define a formula variables and divide the key figure value by the value contained in the formula variable and store this calculation in a new formula. If the formula variable is input ready the user can select the scaling factor at runtime. Alternatively the scalar factor can be computed e.g. in an user exit. The following screenshots explain the details of this approach in detail.
First we create a new formula variable DEMO_SCALING_FACTOR and set the input ready flag. In addition we set the default value to one.
In the second step we create a new formula “Scaled amount”. The formula is straight forward. It uses our keyfigure 0AMOUNT and divedes it by our formula variable DEMO_SCALING_FACTOR (see screenshot below).
For demonstration purposes we display the scaling factor itself in the columns in the third step to see the values in the query for testing reasons.
So in total we have the following keyfigures: Scaled amount, scaling factor and amount
If we now execute the query (in transcation RSRT2) we see the variable screen which shows the scaling factor. We can select a scaling factor (e.g. 1, or 1000) here and execute the query.
As we can see in the screenshot above the query displays some data we have entered. It displays the scaled amount (100 CHF/ 1), the scaling factor (1) and the amount (100 CHF). Executing the query with a different scaling factor results in the following screen shots:
Again we see the scaled amount (0.10 CHF = 100 CHF /1000), the scaling factor (1000) and the amount (100 CHF). If we just need the dynamic scaling factor in reporting queries we are basically done. All that is left to do is to hide the amount key figure in the query and maybe hide the scaling factor and adjust the header of the scaled amount column to display the correct scaling factor. In the BI-IP planning world this is currently not enough. As we can see in the screenshots above, the scaled amount column is not yet ready for input and therefore we can’t input data. So the next step is to enhance the demo exable and achieve input readyness for formula key figures.
Achieving input readyness for formula key figures
For input ready queries the approach outlined above needs additional steps because the data cannot be entered on formulas right away. Nevertheless the concepts of “inverse formulas” allows to model the desired behaviour. We create another formula key figure called “Scaled input (input ready)”, apply our scaling factor calculation and set it to input ready as shown in the screen shot below.
In addition we need to define the inverse formula because want the that value we enter in this keyfigure update the amount keyfigure. Of course we invert the calculation of the keyfigure values here as well.
In summary we have the following query layout now:
Executing the query (in transaction RSRT2) results in:
Now we delete the “Scaled Amount” formula and hide the “Amount” key figure by setting it to “hide (can be shown)” as we can see in the next screenshot.
We execute the query again and see the final result.
The query is still ready for input and we can enter data according to the dynamic scaling factor.
This approach demos how we can achieve dynamic scaling factors in BI-IP queries. Please note that the number of decimals in the “scaled amount” keyfigure might be set set to a different value in your query as well.