on 05-14-2016 12:12 PM
Hi All,
I would like to know how using the HANA Modelling (Analytic/Calculation view) if we can control the display of Measures based on the Characterstics that we select in the report output.
For e.g. Forecast is at Year level for a given Vendor..So when I have the report layout at Vendor level and not at any other fields like Period, I want the Forecast to be displayed...If I have any other level which is detailed at which the Forecast is not maintained, I want the Forecast measure to display zeros.
Thanks
Ganesh
Hi Ganesh,
If you want to do this in HANA modeling and not through reporting tool then we can do this in Sqlscript calculation view.
Below is the sample logic which I'm trying to give you some idea of doing:
BEGIN
-- Declare all the variables used in script
DECLARE REPORTING_LEVEL NVARCHAR(10) DEFAULT '';
IF REPORTING_LEVEL = 'VENDOR' THEN
TAB_RESULT = SELECT VENDOR, YEAR, ....., SUM(FORECAST) AS FORECAST
FROM ......
GROUP BY attributes....
ELSE
TAB_RESULT = SELECT VENDOR, YEAR, ....., 0 AS FORECAST
FROM ......
GROUP BY attributes....
END IF;
Regards
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ram,
TAB_RESULT is like an internal table which holds the results of the query.
The final output of the query must always assign to VAR_OUT.
Ex:
IF REPORTING_LEVEL = 'VENDOR' THEN
TAB_RESULT = SELECT VENDOR, YEAR, ....., SUM(FORECAST) AS FORECAST
FROM ......
GROUP BY attributes....
ELSE
TAB_RESULT = SELECT VENDOR, YEAR, ....., 0 AS FORECAST
FROM ......
GROUP BY attributes....
END IF;
VAR_OUT = SELECT * FROM :TAB_RESULT;
Hi Raj,
Thanks for the idea. Well I was looking something at modelling level where depending on the characteristics that I select at Runtime in query output, the KF's will either show or dont show the values..the perfect e.g. is the Forecast done at year level.. say I need to show the Forecast only when the current layout is at Year level and should not show when I bring/drill in say period values..
Similar to dynamic join that works based on the fields used..I was hoping that there could be a way ..but nevertheless what you suggested will also work but we need to explicitly mention which level that we want to execute the report before in hand..
Thanks
Ganesh
Hi Ganesh,
We have two cases here:
1) In HANA Modeling views, we can achieve the dynamic calculations based on the user input values (which are mapped to Input Parameters and input parameters can be used as part of the calculations).
2) However, if we have to control the display of measures dynamically based on the user selections, this need to be handled in the reporting tool.
Hope this will help in evaluating and choosing the right option for your scenario.
regards,
Varma
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.