Design studio calculate percentage share of total

[gertjan_oostrum]

Hi all,

I am working on a Design Studio Dashboard which is retrieving data from a HANA calculation view.

I would like to be able to calculate a percentage share of total in Design Studio.

I have checked the 1.6 version because that should support calculations at runtime.

t

The Dynamic Calculation is providing me the exact required output (adding a new column and show the percentage contribution:

However the HANA datasource which I am using does not support these dynamic calculations.

On the measures I do not see any option to add local calculations:

Is there a similar feature in the Calculations at Runtime ? And more important where are the Calculations at Runtime applied in DS ?

I know we can solve this in HANA by adding the percentage calculation over there but that is additional effort since we are having multiple levels of aggregations. For each level of aggregation we need to do the calculation of the total before we can filter the information towards DS.

That is making the model more complex than needed. I would like to apply this percentage contribution in DS after the resultset is retrieved.

Thanks for your suggestions, input !!

[MustafaBensan · Accepted solution]

Hi Gert,

Here is a scripting approach for calculating percentage contribution client side that works with BW.  You could try to see if it also works with your HANA calculation view.

The basic steps are as follows:

1.  In your data source, make sure the "Show Totals" option is selected for each of the dimensions as shown below:

2.  Hopefully, like a BW data source, this will calculate OLAP aggregates for each dimension level (as shown above) without you having to explicitly define the aggregates in the HANA calculation view itself, so that your Initial View looks something like this:

3.  Calculate Percentage Contribution at the required totals with a script like the following:

// Use variables to dynamically specify dimension member values for data selection

var myRegion = "D";

var myWeekday = "3";

var myAirline = "20363";

// Determine individual measure value

var myValue = DS_1.getData("006EIC2OSTY35SPMRNDFV5VVM", {"ZAIRLINID__ZAIREGN":myRegion,"0WEEKDAY1":myWeekday,"ZAIRLINID":myAirline}).value;

// Determine total at the required aggregation level by specifying only the aggregation dimensions

var myTotal = DS_1.getData("006EIC2OSTY35SPMRNDFV5VVM", {"ZAIRLINID__ZAIREGN":myRegion,"0WEEKDAY1":myWeekday}).value;

// Calculate Percentage Contribution

var myPercentageContribution = (myValue / myTotal * 100);

// Display Results

TEXT_VALUE.setText(myValue + "");

TEXT_TOTAL.setText(myTotal + "");

TEXT_PERCENTAGE_CONTRIBUTION.setText(myPercentageContribution + "");

The important points to note with the scripting are as follows:

• You can use variables to dynamically specify the individual dimension members to be used to retrieve the individual and total data values
• In the second getData() statement, all you need to do is remove dimension specifications from the end (compared to the first getData() statement), to retrieve the aggregate at the desired level.  In the above example, the detail value is at the level of Region / Weekday / Airline.  The aggregate value is for the total at the Region / Weekday / Airline level because we have removed the last dimension, Airline from the selection defintion

I hope that makes sense.

4.  The sample output looks like this:

The challenge with the above approach would be to display all the calculations in a tabular format, so another calculation approach you could try is the one described in the following blog post:

Regards,

Mustafa.

[gertjan_oostrum · Accepted solution]

Thanks for the suggestions.

We solved it in HANA by creating a fixed aggregate and restrict the totals by input controls for the dimensions in our model.
It would be a great addtition in case the same percentage share features on top of BW are made available in case HANA is your datasource.

[MustafaBensan · Accepted solution]

Hi Gert,

Thanks for the update.  For my understanding, can you clarify what you mean by "input controls"?  Are you referring to "input parameters" for HANA?

Regards,

Mustafa.

[gertjan_oostrum · Accepted solution]

Yes indeed, input parameters in HANA 🙂

[Former Member · Accepted solution]

Hello Gert - Can you explain your procedure.

Thanks

Ravi

[MustafaBensan · Accepted solution]

Thanks Gert.

[gertjan_oostrum · Accepted solution]

https://ideas.sap.com/D36023

We have created to separate calculations in a calculation view, one for the overall totals and one of the overall totals per dimension we would like to display in the chart.

Bring back togehter the 2 calculations in a join and aggregate the resultset.

Since the dataset is influenced by filters we added input parameters to restrict the resultsets in the first/bottom projections.

This is not a very flexible solution but it works for now and specifcally for this purpose.

Rather have the same option to calculate this in the datasource after retrieval of the data, but

unfortunately Design Studio is not able to achieve this in combination with HANA as a datasource.

I have added an idea at SAP ideaplace for this :-), see above link

[TammyPowlas]

Please check the PAM / Product Availability Matrix at https://support.sap.com/content/dam/library/ssp/infopages/pam-essentials/SBOP_DSTUDIO_16.pdf

See page 5; my guess is it depends on version/SP you have for HANA