Dynamic Display of Measures

[former_member679146]

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

[rindia]

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

[Former Member]

Hi Raj,

TAB_RESULT is table type?

[rindia]

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;

[former_member679146]

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

[varma_narayana]

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