Skip to Content
0

Force Group By for calculations in HANA Calculation View

Nov 30, 2016 at 10:18 AM

899

avatar image

I have a measure called Hours and a dimension called month (which is made up of multiple entries per month). Now if I select sum(Hours) as Total Hours and group by month; I get the expected output.

e.g.

select sum("hours") as "Total Hours", "Month" from My_Calculation_View group by "Month";

returns (as an example):

10, October

11,November

9,December

But is there a way to take this view and create a Calculation View that simply has the columns Total Hours and Month; without requiring the user of the view to do the sum and group by and without requiring me to resort to SQL scripting beyond calculated fields or filters in my Calculation View?

e.g.

select "Total Hours", "Month from My_Calculation_View;

The reason for this is for use in an Overview Page where the behaviour of the aggregation cannot be controlled so I need final values to be in these fields.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Lars Breddemann
Nov 30, 2016 at 12:13 PM
0

Why don't you simply create a SQL view that contains the SUM() and aggregate and have the users query that SQL view instead?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Lars,

I think this might be the best solution personally, but as I'm trying to find solutions for the BI team, I know I get a lot of push back when it leaves the modelling environment and requires SQL to be written (beyond calculated columns and filter statements); hence my desire to try find another approach.

Note - It doesn't mean that I don't push that this is a skill they need to continue modelling in HANA.

Cheers,

Matt

0

I get that, but boy, they'll going to love CDS views...

0
avatar image
Former Member Nov 30, 2016 at 01:38 PM
0

Much simpler sounds the way of Lars by creating such an aggregated SQL view with your statement. But as you want to deliver the aggregated value with the calculation view I would do the following:

Set a aggregation node in your existing calculation view with month as column and hours as aggregated column with sum aggregation. Join your aggregated hours value for each month to the existing output and select distinct the month and your aggregated hours value (which is the same for each month).

Regards, Marcel

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for your answer to this Marcel, though I wasn't clear what you meant by select distinct since I've never seen a "distinct" possibility in the Calculation View modelling tools (which would definitely be advantageous in this scenario). e.g. Exposing this to XSOData would now allow me to enter additional SQL if that's what you meant.

Cheers,

Matt

0
Lars Breddemann
Dec 01, 2016 at 12:03 AM
0

Ok, I needed to confirm this via a simple test, but for graphical calc views with an aggregation node as the top-level node, there is this thing called 'default aggregation'.

Basically you define your aggregation functions in the aggregation & semantics node and then you can query the view without having to repeat the aggregate and grouping information in the select statement.

Share
10 |10000 characters needed characters left characters exceeded