Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Parameterising by Measures in Reporting Services 2005

with 7 comments

If you’ve been reading this blog for a while, you probably know that I’m not the greatest fan of the way that support for MDX has been implemented in RS2005. Anyway, here’s a little tip that might soften the pain…
 
In RS2005 you are always forced to put the Measures dimension on the columns axis in your queries if you’re using a data source of type ‘Microsoft SQL Server Analysis Services’ (you can still get the glorious AS/RS2K experience if you use an OLEDB connection instead along with the AS OLEDB Provider). While this is irritating at the best of times, it does have one serious consequence: how can you parameterise a query by Measures? It doesn’t look like the RS dev team thought this would be a valid scenario, but in fact in my experience there are plenty of times where you do want to do it. It is possible though, and here’s a solution:
 
First of all, you need to create a query that will give you a list of measures on rows. The following AdventureWorks example shows how to do this:
 

WITH
SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)
MEMBER MEASURES.MeasureUniqueName AS
MEASURES.
ALLMEMBERS.ITEM(
RANK(
[Date].[Date].
CURRENTMEMBER, MYSET
)-1
).
UNIQUENAME
MEMBER
MEASURES.MeasureDisplayName AS
MEASURES.
ALLMEMBERS.ITEM(
RANK(
[Date].[Date].
CURRENTMEMBER, MYSET
)-1
).
NAME

SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,
MYSET
ON 1
FROM [Adventure Works]

It relies on there being a level on a hierarchy somewhere in your cube that has more members on it than there are measures, so it’s a bit of a hack, but this isn’t a problem most of the time and is the easiest way of solving the problem in MDX. You just create a set with the same number of members in as you have measures, put that on rows in your query, and then using calculated measures return the name/unique name of the measure which has the same rank in the set Measures.AllMembers as the currentmember on rows (Date.Date in this case) has in that set.
 
After you’ve created a new report parameter tied to this resultset you can create the query you want to parameterise. Once again the need to have measures on columns needs to be worked around – this time you need to create a calculated measure in your WITH clause, put that on columns, and then parameterise the definition of that calculated measure. Here’s an example:
WITH
MEMBER MEASURES.SELECTEDMEASURE AS STRTOMEMBER(@MyMeasure, CONSTRAINED) SELECT NON EMPTY { MEASURES.SELECTEDMEASURE} ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE
 
Before this will work though, you need to declare the query parameter used by this query. To do this, click on the ‘query parameters’ button in the toolbar above where your MDX appears (it looks like an @ symbol overlaid on a table) and then fill in the following values:
  • Parameter as the name of your parameter, without the leading @. So in our example it would be MyMeasure
  • Dimension needs to be left blank – the Measures dimension doesn’t appear on the dropdown list
  • Hierarchy needs to be blank too
  • Multiple Values needs to be left unchecked
  • Default must be entered, but you can use an expression such as MEASURES.DEFAULTMEMBER

Thanks go to my colleague Tony for working this last bit out.

You can then bind this query to a table in your report and select different measures to slice by. Hope this helps!

 
 
 

 

Written by Chris Webb

December 5, 2005 at 2:02 pm

Posted in Reporting Services

7 Responses

Subscribe to comments with RSS.

  1. Chris,I already posted this on your "Rant: Reporting Services and Analysis Services" blog entry but thought I should duplicate…After a little tinkering I discovered that if you create a datasource in RS2005 of type OLE DB then choose the provider as "Microsoft OLE DB Provider for Analysis Services 9.0", the functionality from RS2000 is preserved.. Yay!I imagine you would have already worked this out but for all those who come across this blog in the future….

    Aranda

    January 18, 2006 at 2:44 am

  2. Oops… just realised that you were well aware of this since you say "you can still get the glorious AS/RS2K experience if you use an OLEDB connection instead along with the AS OLEDB Provider"Please ignore previous comment :)

    Aranda

    January 18, 2006 at 2:47 am

  3. Can you do this for the rows axis of a query?

    Gabriel

    February 21, 2006 at 9:08 am

  4. Gabe, do you mean have a parameter which controls which dimension appears on the rows axis? If so, then yes, that should be possible. I\’m a bit pressed for time today (and for the rest of this week), but here\’s roughly what the MDX would look like:

    WITH
    SET MYROWS AS STRTOSET("[Date].[Day Name]" + ".MEMBERS")
    MEMBER MEASURES.DEMODIM AS
    MYROWS.ITEM(
    RANK([Date].[Date].CURRENTMEMBER, [Date].[Date].MEMBERS)-1
    ).ITEM(0).UNIQUENAME
    MEMBER MEASURES.DEMOMEASURE AS
    (MEASURES.[INTERNET SALES AMOUNT], STRTOMEMBER(MEASURES.DEMODIM), [Date].[Date].[All Periods])
    SELECT {MEASURES.DEMODIM, MEASURES.DEMOMEASURE } ON 0,
    HEAD(
    [Date].[Date].MEMBERS
    , COUNT(MYROWS)
    )
    ON 1
    FROM [Adventure Works]
     
    You\’d parameterise the string containing the name of the dimension/hierarchy you wanted to see in your report, and make sure you had put the dimension/hierarchy with the most members in your cube on rows. I\’ll think about this problem a bit more and maybe do a full blog entry next week…

    Chris

    February 21, 2006 at 2:10 pm

  5. Thanks a lot for thius suggestion. I\’ve implemented in a recent project and was very helpfull !!!
    Aser

    Aser

    January 10, 2008 at 10:35 pm

  6. Hi Chris, tried to use the "Microsoft OLE DB Provider for Analysis Services 9.0" datasource to put measures on rows for a work report. It kind of works – except that it treats all the measures as a single Field of the Dataset. By that, I mean you can\’t format the measures individually and as I had a mixture of money, counts and percentages it gave me a problem. Good stuff though, I also used the measures drop down in another report.
    Sean.

    Sean Wood

    July 15, 2008 at 3:29 pm

  7. Wonderful article!!!Exactly what I was looking for.Sam

    Itz

    September 22, 2009 at 9:03 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers