How to build SSRS reports from SSAS cube data using MDX.

Problem: How to build SSRS reports from SSAS cube data using MDX.

Solution:

After building a cube in SSAS, the next thing to do is create reports of the analysis services cube data.

SSRS is the Microsoft business intelligence reporting tool where reports can be created using t-sql, mdx or dmx queries. To create a report from cube data there are two options either use multi-dimensional language(MDX) or data mining language(DMX).

To learn about how to write MDX queries please go through this article. MDX queries

Also, SSRS provides query designer where drag and drop measures is allowed to build queries on top of a cube.

Options are almost similar to what you get from a T-SQL dataset, creating parameterized datasets, filtered datasets.

Example:

In this example, I would like to show we can create a SSRS report from SSAS Cube.

Step 1: launch visual studio and create an SSRS Project and create new data source to connect with cube.

Step 2: Now create a new report and go through the wizard and select the data source.

Step 3: after selecting the data source, next step is to build the data set by clicking on the query builder.

Step 4: for building the query drag and drop the data fields needed for report.

Step 5: After adding the data set, next add the type of report to show.

Step 6: Select columns rows and details from available fields and click on the drill down features to enable toggling.

Step 7: Next step is to go through designer for formatting all the content in SSRS designer and go to preview results.

Results: