2

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?

José Amparo
  • 79
  • 5

1 Answers1

2

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])
Rudolf
  • 199
  • 8
  • +1 - I've experienced some performance issues with this approach on larger cubes though, so if yours is very large consider if this could be done as a calculation in the DSV, to materialise these values? – Meff Dec 01 '10 at 08:56