SSRS报表钻透
SSRS Drillthrough for SSAS cubes, how to pass a parameter to an MDX dataset
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
§ StateNameo 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 ROWSFROM [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!
