Using Measures in the WHERE clause in MDX/Reporting Services

Question

Post reply

Using Measures in the WHERE clause in MDX/Reporting Services

Sam Peascod

SSCrazy

Points: 2497
More actions
November 28, 2007 at 5:18 pm

#119580

I have four measures which I'd like to be able to alternate between on reports (the measures are along the lines of units sold, dollars sold, cost of sales). I expect to use a parameter to pick from any of those values.
I'd have thought the simplest thing to do would be to use the parameter in the WHERE clause. Thus, my query would look something like:
SELECT [Time].[Months].[October 2007] ON COLUMNS,
[Items].AllMembers ON ROWS
FROM [MyCube]
WHERE (STRTOMEMBER(@Measure))
Reporting Services doesn't allow anything but the measures dimension on the first axis, so I'd have to move the Time dimension. This query works in the SSMS.
What's the easiest way to do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rakesh-439259 SSC Enthusiast Points: 131 More actions · Answer 1

Instead of using where clause you can provide an Expression in the Table column like

=iif(Parameters!Param.Label="First",

Fields!Reseller_Extended_Amount.Value,

iif(Parameters!Param.Label="Second",Fields!Reseller_Gross_Profit.Value,

Fields!Reseller_Freight_Cost.Value))

assuming that Reseller_Extended_Amount,Reseller_Gross_Profit.Value and Fields!Reseller_Freight_Cost are 3 measure u would like to view.

Depending on the Parameter Selection of either First, Second or Third option the values inthe report chage.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 2

Thanks Rakesh. I see where you're coming from, but to me it has a couple of inefficiencies:

1. It means I have to return all those values in the data set, when I only want one in every three of them

2. Maintaining such a formula in every one of the cells in my report (there could be 100+ cells using this information) could be a nightmare

So, I think I'll go down this path instead:

1. Write calculated measures in my query using WITH MEMBER, but include a parameter specifying the measure I wish to calculate against (along with a time parameter)

2. Include these calculated measures on the COLUMNS axis

I'd prefer to have these created in the cube itself (as I need to use the same calculations in a few other reports), but it would be a huge string of nested IIFs to get all the combinations of time dimensions and measures. This way seems to be the best trade-off I'm aware of between flexibility and maintainability.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

Actually, you can abstract it to the cube. This'll just take a little ingenuity:

First, you'll need a "Slicer" or "Expressions" attribute. This can exists in either an existing dimension, or in a new dimension (i.e. [Expressions].[Slicer]). (You'll need to have at least 1 record feeding it.)

In your cube script, create your slicer expressions:

Create Member [Expressions].[Slicer].[All].[Units Sold]

, Format_String="#,#";

...

Now, in the measures dimension, create a measure expression:

Create Member [Measures].[Slicer Fact]

as "You haven't selected a proper fact";

Scope [Measures].[Slicer Fact];

[Expressions].[Slicer].[All].[Units Sold] = [Measures].[Units Sold];

[Expressions].[Slicer].[All].[Dollars] = [Measures].[Dollars];

[Expressions].[Slicer].[All].[Cost] = [Measures].[Cost];

End Scope;

In your report, use a parameter for [Expressions].[Slicer].[All].Children (on the WHERE statement) and place [Measures].[Slicer Fact] on your columns.

Hope this helps.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 4

Thanks for the fantastic response Dave. In the meantime I've stumbled upon Chris Webb's take on this:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!412.entry

I implemented this way (it's excruciatingly close to what I had, but couldn't get to work), though will be sure to try your way next time - I have a few of these to do. Thanks again.

Sam

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 5

Chris is always a fantasitic source of information. I used this approach myself a couple of years ago, but ran into some additional limitations. (For example, I have many calculated members now and I didn't like the cross dependancy on number of members.)

Also, you can extend Chris's technique somewhat, by abstracting the first list to a lookup table (i.e. in SQL Server). A bit more maintanance, but much finder control over what appears in the fact list (and no cross depandancy on number of members). But it's still a hack of a different colour.

Some additional points:

In MSRS, I always add FORMATED_VALUE to the cell properties and use the ".FORMATTED_VALUE" (not ".VALUE") as the report cell value. Better to get your formatting from the cube where possible.

I'd like to echo Chris's comments on how well MSRS understands OLAP concepts. In general, you want to use the formatted value from the cube, and you have to use the function "Aggregate()" to ensure you're pullling from the datascope and not using the controls aggregate functions. The default processing for OLAP should use these as defaults. I've commented on this gap myself (back in the beta days), but this change hasn't made it's way into the product set yet.