How to do filtered aggregations by a filter field in olap cube?

2
votes

Currently I have one field in a SQL DB table called Amount. This field will be used in a Reporting Services report that uses SSAS OLAP Cube behind to display the winners and the payments. The column payment of the report must use this field (I mean Amount), filtered by the status X and the column winners must use this field (Amount too) filtered by status Y. How can I do this in the cube to use the same measure represented in two columns with their values filtered?

I tried to use the measure expressions but I don't know if this trick could help. I also heard about calculated measures but don't know how it works.

Any ideas?

1
sql-server-2008mdxssrs-2008ssasolap-cube

1 Answers

2
votes

Add a dimension [STATUS] to your cube containing the members [X] and [Y]. This can be a hidden dimension. Add [Amount] as a measure and hide this measure also.

Then, add 2 calculated measures to your cube:

[Measures].[Payment] with MDX expression:

([Status].[Status].[X], [Measures].[Amount])

And [Measures].[Winners] as

([Status].[Status].[Y], [Measures].[Amount])