Click here to Skip to main content
12,698,600 members (24,202 online)
Click here to Skip to main content
Add your own
alternative version

Stats

13.9K views
165 downloads
6 bookmarked
Posted

Simple SSRS Report using MDX Query

, 18 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 18 Jul 2014
Article Copyright 2014 by Ajit Kumar Thakur Microsoft
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid