Click here to Skip to main content
14,240,765 members

Simple SSRS Report using MDX Query

Rate this:
5.00 (2 votes)
Please Sign up or sign in to vote.
5.00 (2 votes)
18 Jul 2014CPOL
This tip contains simple step by step solution to read SSAS cube data using MDX query and display in SSRS report.

Sample Image - maximum width is 600 pixels

Introduction

This tip is for beginners who want to read data from SSAS cube. Numbers are available in Cube under Dimension and Measure Group. MDX (multi dimension expression) query is used to read data from SSAS cube. This article will show how cube data can be displayed through SSRS report.

Background

Data warehouse contains several fact and dimension tables. These tables contain huge record set. Accessing facts/measures from these tables in small relational database is easy. It can be done using T-SQL query. But when dataset is very large, it becomes complex to display in SSRS report. SSRS reports take many hours to display such report. So that these heavy data set are implemented in SSAS cube. MDX query is used to read data from cube. MDX query gets record set very quickly. Hence large report takes very small time to display in SSRS report.

Using the Code

To use this tip, one must have available cube in SQL Server Analysis Service Engine. Below images show how cube should be available in analysis service.

In simple SSRS report with MDX query, we are reading data from Adventure Works Cube. Below MDX query will be used in simple SSRS report to get Internet sales data.

SELECT NON EMPTY 
{ 
[Measures].[Internet Freight Cost], 
[Measures].[Internet Sales Amount], 
[Measures].[Internet Average Sales Amount], 
[Measures].[Internet Average Unit Price]
} ON COLUMNS FROM [Adventure Works]

Below images describe step by step process to show MDX result in SSRS report.

  1. Create New report server project.

  2. Create New data source points to SQL Server analysis service database.

  3. Create New Data set for Internet sales report using MDX query.

  4. Create New report file to display MDX result.

  5. Design table and assign required column in SSRS report.

Points of Interest

This is very simple project to learn reading of Cube data through MDX and display in SSRS report.

History

  • 18th July, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ajit Kumar Thakur Microsoft
Database Developer Microsoft
India India
I am Ajit Kumar Thakur. I am Consultant in Microsoft Global Delivery India. I work on Database (SQL Server), BI (SSAS, SSRS,SSIS),and Windows Powershell. I have 8 years of experience in Database and BI project development.

Comments and Discussions

 
-- There are no messages in this forum --
Tip/Trick
Posted 18 Jul 2014

Stats

26.2K views
199 downloads
6 bookmarked