SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset

Yesterday I had a chat with my boss about drillthrough reports. I told him that with SSRS 2008 it can be done easily. Yep, I was right in terms of a SQL Server data source (or other relational I presume) but it cost me almost a day to figure out how to do it if you have two reports based on an SSAS OLAP cube!

The usual method does not work because you cannot pass a parameter like in the SQL way. No, MDX is much different. Forget the multidimensional query designer because though it’s useable in case of a simple parameterized report but it just makes things messy in our case (it’s just my opinion and experience). I’m just at very beginner in MDX that’s one reason I suffered so much with this issue.

Based on the Adventure Works DW 2008 SSAS database (available on Codeplex such as the underlying SQL Server database) I created an example.

Case and the desired result

Two reports:

1.     Displays Internet Sales Amount and Internet Tax Amount by using the Customer dimension and the Customer Geography hierarchy with drilldown implemented for the Geography from the Country level to the State Province level. Very simple:

2.     A detailed report which displays the Internet Sales Amount and the Internet Tax Amount for Cities in the chosen State Province:

What I wanted to do is to click to a State in Report1 to see the detailed levelled Report2 only for that state.

Ingredients and “cooking” instructions

Report1 – Country and State level - InternetSales

·         A basic MDX query (can be done with the query designer) that contains the needed Measures and 2 levels (Country, State-Province) from the [Customer].[Customer Geography] hierarchy.

·         Some tweaking in that MDX manually to include the unique name and the displayed name of the actual State-Province:

WITHMEMBER [Measures].[StateUniqueName] AS [Customer].[Customer Geography].CurrentMember.UniqueNameMEMBER [Measures].[StateName] AS [Customer].[Customer Geography].CurrentMember.Name  SELECT          NON EMPTY {[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount], [Measures].[StateUniqueName], [Measures].[StateName]} ON COLUMNS,NON EMPTY {([Customer].[Customer Geography].[State-Province].ALLMEMBERS )} ON ROWS  FROM [Adventure Works]

·         Sample result (executed in SSMS[1]):

·         StateUniqueName is needed as a parameter for the MDX in Report2 and StateName will be used in Report2 only as a caption in the header.

·         Now you have your dataset with 6 fields.

·         Create a simple report like I did (I cheated, I used the wizard...) with drilldown i.e. create a group and set the Visibility settings for the details to be toggled by Country.

·         Right click on State-Province in the report designer then choose Text Box Properties:

·         In the next windows click Action, set Go to report, from the drop down menu under Specify a report choose Report2 (it has to exist to do that), Add two parameters, put a name (it has to be the same you want to use in Report2 as name of the parameters and choose [StateUniqueName] and [StateName] as values.

·         That’s all for Report1. Of course you can customize field formats and things like that but that’s not relevant for our case and can be done easily.

Report2 - Drillthrough - details for cities in [StateName]

This is the trickier part.

·         Parameters:

o   Create two parameters (make sure you use the same names as above!):

§  CustomerStateProvince

§  StateName

o   I hide both of them because it’s not needed to be displayed. However you can leave them Visible for troubleshooting if you want.

o   Otherwise you can leave everything as default (no Available Values no Default Values are needed)

·         Dataset:

o   Create a new dataset (I used the wizard before and afterwards edited everything)

o   The Query should be a string expression so click the fx button:

o   Now comes another MDX part. You have to write the MDX statement first and you’ll pass it here as a string concatenating with the string value of the parameter from Report1. I created an example query in SSMS before tweaking it here.

WITHMEMBER [Measures].[City] AS [Customer].[Customer Geography].CurrentMember.Name SELECT({[Measures].[Internet Sales Amount], [Measures].[City]}) ON COLUMNS,NONEMPTY(          {[Customer].[Customer Geography].[State-Province].&[OH]&[US].Children},          {[Measures].[Internet Sales Amount]}) ON ROWS FROM [Adventure Works]

o   If it gives the desired result for one State you just have to find where to pass the parameter. I gave a yellow background to it.

o   My final version of the expression of the Dataset in Report1 is the following[2]:

="WITH MEMBER [Measures].[City] AS [Customer].[Customer Geography].CurrentMember.Name SELECT  ({[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount], [Measures].[City]}) ON COLUMNS,                NONEMPTY( {" + Parameters!CustomerStateProvince.Value + ".Children},                                {[Measures].[Internet Sales Amount]}) ON ROWS

FROM [Adventure Works]"

o   On the Fields pane of the Dataset Properties you have to manually add the fields that your query gives back (no automatic recognition):

o   Field source should be like this (I tried simple [Measures].[Internet Sales Amount] but it wasn’t enough.

§  <?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Internet Sales Amount]" />

§  <?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[City]" />

§  <?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Internet Tax Amount]" />

o   Other panes of the Dataset Properties can be left as they are (even the Parameters one!)

Final words

And that’s it. Report2 cannot be used without Report1 but simply because I didn’t allow any available or default values to its parameters.

I’ve uploaded a copy of my packages here (check the Attachments below the footnotes, above comments) with a Word2007 version of the post (you can find both in the ZIP file).

I hope you find it valuable. I googled a lot about it yesterday and the real valuable help was Gurvan Guyader’s blogpost. He also attached his packages to the post which proved to be a GREAT help!



[1] You can use Mosha’s MDX studio, too which is a very great tool (and free J). I highly recommend it!

[2] You must type a SPACE before SELECT and FROM otherwise syntax error because an Enter is not enough!

Published Wednesday, March 31, 2010 2:50 AM by MartinIsti

Comments

# re: SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset

Wednesday, March 31, 2010 11:15 AM by Rob Farley

You don't need to tweak the MDX like that, you can just refer to Fields!State_Province.UniqueName in your report instead of .Value

# re: SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset

Thursday, April 1, 2010 11:15 AM by simonsabin

You can also use strtomember and strtoset to avoid using dynamic MDX. You just specify parameters as you would for SQL

# re: SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset

Friday, April 2, 2010 1:24 AM by MartinIsti

Thanks for the ideas, I've tried those as well but somehow I didn't manage to do it either with the UniqueName or the STRTOMEMBER. I assume that somehow I made a mistake. Nevertheless this MDX tweaking was the only solution I could come up with but I'll try to implement your ideas next week!

# One solution on parameters between reports. | Learning Microsoft Business Intelligence

Pingback from  One solution on parameters between reports. | Learning Microsoft Business Intelligence

# Passing parameters to SSRS from Clarion | Ziadie

Thursday, November 20, 2014 10:07 PM by Passing parameters to SSRS from Clarion | Ziadie

Pingback from  Passing parameters to SSRS from Clarion | Ziadie

# pass field value as parameter ssrs | Yafuso Answers

Tuesday, December 9, 2014 4:18 AM by pass field value as parameter ssrs | Yafuso Answers

Pingback from  pass field value as parameter ssrs | Yafuso Answers

# MDX split string? | Zima Answers

Thursday, December 11, 2014 12:30 AM by MDX split string? | Zima Answers

Pingback from  MDX split string? | Zima Answers

Powered by Community Server (Commercial Edition), by Telligent Systems