03 November 2011

Adding parameters to my MDX query

 In my last post I've discussed a recursive MDX query. In this post I'll describe how I've parametrized the date for this query.
As you remember, in that query there was a set defined like this:
SET [OrderedFUM] AS (
    ORDER (([Dim Portfolio Client].[Dim Client Group Id].children,
        [Dim Date].[Id].&[20110630]),
        [Measures].[FUM Value], BASC),
        ISEMPTY ([Measures].[FUM Value] )=0)

In real life, of course, I don't want to make a specific date; I want to add the date as a parameter to my MDX query. The requirement is for "sum per client per month" as the data is stored on a monthly basis. After lots of mocking around I've done the following, and I recommend following the same procedure for anyone else who is trying to parametrize an MDX query in SSRS:
  1. Create a new report in BIDS.
  2. Define a data connection.
  3. Copy the MDX query "as is" into the Query Designer (the DataSet part)
  4. Define a parameter in the "parameters" tab
In order to achieve that I have to use SSRS: After I've created the Data Source and the Data Set and added the MDX "as is" into the Query Designer, I've created the report and formatted it to my liking. Then I've added the parameter in the "parameters" section. I called it "Month" and made it of data type text.
Now I need to add the "date" in the SET as a parameter, not a constant. Therefor, I had to change the defintion of the measure "OrderFum" set as follows:
SET [OrderedFUM] AS (
    ORDER (([Dim Portfolio Client].[Dim Client Group Id].children,
        STRTOMEMBER(@month)),        [Measures].[FUM Value], BASC),
        [Measures].[FUM Value]<>0)

And in order to make the code more readable (=less references to parameters), I changed the other reference to "dim date" in the RANK function from

[Dim Date].[Id].&[20110630]


[Dim Date].[Id].currentmember.

And now in the "parameter" box I need to insert the following very clear code
[dim date].[id].&[this is where the day of the month actually gets in - the actual parameter]
For Example:
[dim date].[id].&[20110930]
You would expect that I would use the "@month" parameter differently, and chances are it could be true.While unfortunately there are changes in using StrToMember function between Sql Server 2008 R2 to Sql Server 2005, the StrToMember Functions are quite complicated, as Mosha clearly explains. In order to make this code a bit more usable, I just added [Dim Date].[id].&[20110930] as a default parameter; the beauty is, that if my business analyst wants to play around with this, he just needs to change the date, not the entire text in the text box. Of course it is prone to human error; I, for once, forgot that it is [Dim Date] and not [DimDate].... And yes, I've tried StrToValue but to no avail.
For conclusion: definitely not intuitive, get ready to bang your head in the wall several times during using parametrised MDX with SSRS.

No comments:

Post a Comment