Steps to create a SSRS report with MDX accessing a Cube

In this blog post, I’ll show you the out-of-box features in SSRS you can use to create a report that accesses your cube.
I know I am skipping a lot of steps, such as how I designed the star schema for my data mart, how I designed my SSIS package to load data into the data mart, and how I created and deployed my Analysis Services database.
Analysis Services database – asLMRUWDashboard
For the purpose of this blog post, I’ll just show you my final product, asLMRUWDashboard, an Analysis Services database. I have 5 dimensions, and two of them have user defined hierarchies. It’s a very simple and small database. Even with 5 dimensions, the size of the AS database is less than 1 MB.

5 Parameters in the final SSRS report
My final SSRS report will have 5 parameters. The first 2 will be from my Data dimension, and other 3 are from the Site dimension, the Treatment dimension, and the Review Type dimension. I am ignoring the Lien dimension. I am also ignoring the attribute hierarchies for now.

Data in a Matrix
I need my data to display the date dimension horizontally, and other dimensions vertically. I will need a matrix to do this.

Step 1 – Create a shared data source for Analysis Services database asLMRUWDashboard
I am using BIDS 2008. The steps should be the same or similar in BIDS 2005. (I am down playing the difference between BIDS 2005 and 2008. But the truth is you will never want to use BIDS 2005 again if you have ever put your hands on BIDS 2008, especially when it comes to using Matrix.)
This is a straightforward step. Make sure you choose Analysis Services as the Type of the source.

Step 2 – Create a Data Source to use the above shared data source
In the Report Data tab, click New and select Data Source…

Make sure you select the data source you just created.

Now you should have a data source created that points to the AS.

Step 3– Create a Dataset to use the above shared data source
Right click the data source and select Add Dataset…

In the Dataset Properties window, ignore the query for now. It’s my final MDX query. I didn’t hand write it. Instead, click the Query Designer button.

What shows up is actually the cube browser, which is the same cube browser you have seen in SQL Server Management Studio and in Analysis Services project in BIDS.

To create parameters for your report, just simply drag and drop dimensions/attributes onto the top portion of the Query designer, and make sure you check Parameter. The Query Designer will automatically create parameters and default datasets for each check mark.
Then start to drag and drop the measure(s), and dimensions into the bottom portion. The bottom portion is only showing data in a tabular format. do not worry about this yet. You will have opportunity to put your data in a matrix later.
Step 4 – Examine the Parameters that have been created.



Parameters and parameter values are automatically created.
Step 5: Create a matrix with three row groups, and one column group.
The three Row Groups have parent/child relationships. The Column group will be from the Date.



Preview your report. You will see the report parameters. Start to create and format the sub totals in the matrix. You will have a very impressive report.