Skip to Content
-1

Force Group By for calculations in HANA Calculation View

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 30, 2016 at 12:13 PM

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

    Add comment
    10|10000 characters needed characters exceeded

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

    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

    Add comment
    10|10000 characters needed 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

  • Dec 01, 2016 at 12:03 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded