Force Group By for calculations in HANA Calculation View

[MattHarding]

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.

[lbreddemann]

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.

[marcel_scherbinek]

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

[MattHarding]

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

[lbreddemann]

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

[MattHarding]

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

[lbreddemann]

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