If you are unable to create a new account, please email support@bspsoftware.com

 

How to get aggregation for set of members, calculated in datasource, not in repo

Started by Ister, 18 Jan 2016 11:50:19 PM

Previous topic - Next topic

Ister

Hi all, help pls!

I have SSAS Cube as datasource for Cognos Report. And also I have aggregation rules into that cube. That rules are non-addictive through all dimensions, for example its "EBITDA, %". And I want Cognos to use that calculated inside cube values when I prompting a set from any dimension, in my case - from Calendar dimension. So, for example, when I have a set of month (Jan2015, Feb2015), and "EBITDA, %" for each month are (2%, 5%), total value for both months is 4.5%. When I get it directly from SSAS cube by MDX, it returns correctly, so rules works fine. But in Cognos report I get 2%+5% = 7.0% as result, not 4.5%. So Cognos get values for each month separately, and then does total aggregation, as I understand. All aggregations in members is set to automatic. The only workaround that I found, is to make required set of months and put it into slicer filter, and it worked fine, I got 4.5%. But now I need to get 2 measures for 2 different periods and compare it. For example I need to get "EBITDA, %" for (Jan2015, Feb2015) and "EBITDA, %" for (Jan2016, Feb2016), so I cant use slicer now, I need to put measure inside calculated member.

So, I write:
Value1 = total( tuple(["EBITDA, %], [Sum]) within set set([Jan2015], [Feb2015]) )
Value2 = total( tuple(["EBITDA, %], [Sum]) within set set([Jan2016], [Feb2016]) )

But in report I see, that now it queries each month separately again and returns sum of percents of each months.
I tried to write "aggregate" instead "total", but result is still same.
So, can anyone help and offer workaround for that case?

Thanks in advance!