Home > SSAS, SSRS Expert > SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube

SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube

I wasn’t sure if I should put this blog under SSRS or SSAS category. SSAS category won by default because I do not have many SSAS posts.

In my last SSAS #20 post, I blogged about how to use the out-of-box Analysis Services processing tasks in SSIS to process dimensions, partitions, and measure groups.

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.

image

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.

image

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. 

image

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.

image

 

Step 2 – Create a Data Source to use the above shared data source

In the Report Data tab, click New and select Data Source…

image

Make sure you select the data source you just created.

image

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

image

Step 3– Create a Dataset to use the above shared data source

Right click the data source and select Add Dataset…

image

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.

image

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.

image

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.

 

image   

image

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.

image

image

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.

Hope this blog post will motivate you to create your first cube and first SSRS report to access a cube. 

Categories: SSAS, SSRS Expert Tags: , , ,

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

%d bloggers like this: