Read OLAP cube and display in SSRS report with parameter

Introduction

This article is based on SSRS report which accept parameters.Report paramters gets value from dataset through MDX query. Parameter is passed to main report dataset.MDX query fetches report baesd on parameter value. Business require dynamic report to make its decision. when report is dynaimc , different measures value flow makes report more important. I assume user have already understanding of basic SSRS report development using MDX.Please refer my article Read OLAP cube and display in simple SSRS report for basic SSRS report development using MDX query.

Background

SSRS report with parameter is simple in T-SQL code. but in MDX query , it requires extra effort to convert parameter into MDX field. MDX query accepts query paramater design through Query parameter window. MDX uses strtomember function to convert single SSRS parameter into MDX parameter. MDX uses strtoset function to convert multiple SSRS parameter into MDX parameter.

Using the code

This article assumes Cube is already configure in SQL Server Analysis service engine.Sample report is based on Adventure Works cube under AdventureWorksDW2008R2 SSAS database.

I have described step by step process to show SSRS report with parameter in MDX query below.

1. Create New report server project for sample report

2. Create New report file to display SSRS report

3. Define Datasource for SSAS cube.

3. Create New Dataset GetYear for Sales year value in dropdownlist. MDX code writen below.

 WITH
MEMBER [MEASURES].[SALESYEAR] AS
MEMBERTOSTR([DATE].[CALENDAR YEAR].CURRENTMEMBER)

SELECT 
{[MEASURES].[SALESYEAR]} ON COLUMNS,
[DATE].[CALENDAR].[CALENDAR YEAR] ON ROWS
FROM [ADVENTURE WORKS]

4.Rename GetYear Dataset MDX column with simple field name

5. Create New Dataset GetProduct for productcategory value in dropdownlist. MDX code writen below.

 WITH
MEMBER [MEASURES].[PRODUCTCATEGORY] AS 
MEMBERTOSTR([PRODUCT].[PRODUCT CATEGORIES].CURRENTMEMBER)

SELECT 
[MEASURES].[PRODUCTCATEGORY] ON COLUMNS,
[PRODUCT].[PRODUCT CATEGORIES].[CATEGORY] ON ROWS
FROM [ADVENTURE WORKS]

6.Rename GetProduct dataset MDX column with simple name

7.Create SSRS report parameter SalesYear

8.Create SSRS report parameter ProductValue

9.Create new dataset for report. which will accept report parameter and convert it into MDX column.

 WITH 
MEMBER [MEASURES].[PRDSUB] AS 
MEMBERTOSTR([Product].[Category].CURRENTMEMBER),
FORMAT_STRING = 'String'

MEMBER [MEASURES].[SALESYEAR] AS
MEMBERTOSTR([DATE].[CALENDAR YEAR].CURRENTMEMBER),
FORMAT_STRING = 'String'

SELECT 
{
[MEASURES].[SALESYEAR],
[MEASURES].[PRDSUB] ,
[MEASURES].[ORDER COUNT],
[MEASURES].[SALES AMOUNT],
[MEASURES].[INTERNET ORDER COUNT],
[MEASURES].[INTERNET SALES AMOUNT]
} ON COLUMNS,
{[Date].[Calendar Year].&[2005],[Date].[Calendar Year].&[2006]} *
{
[Product].[Category].&[4],
[Product].[Category].&[1]
}
ON ROWS
FROM [ADVENTURE WORKS]

10.Rename GetReport dataset MDX column with simple column name

11.Define Query parameter for MDX query and assign parameter name same as SSRS report parameter.

12.Design report layout in report RDL file and assign column of GetReport dataset to it.

Above SSRS report with parameter allows only single value selection at a time. we can also define report parameter in multi selection mode.MDX query uses strtoset function to convert multiple selected parameter value into MDX required input.below images describe SSRS report with paramter allowing multiple selection of values.

1.Add new Report file in existing project to perform multiple parameter value selection based report.

2.Add new dataset GetYear to multiple selection of SalesYear in dropdownlist.

 WITH
MEMBER [MEASURES].[SALESYEAR] AS
MEMBERTOSTR([DATE].[CALENDAR YEAR].CURRENTMEMBER)

SELECT 
{[MEASURES].[SALESYEAR]} ON COLUMNS,
[DATE].[CALENDAR].[CALENDAR YEAR] ON ROWS
FROM [ADVENTURE WORKS]

3.Modify SSRS report paramter property to allow multiple selection in SalesYear parameter.

4.Add new dataset GetProduct for multiple selection of ProductCategory in dropdownlist.

 WITH
MEMBER [MEASURES].[PRODUCTCATEGORY] AS 
MEMBERTOSTR([PRODUCT].[PRODUCT CATEGORIES].CURRENTMEMBER)

SELECT 
[MEASURES].[PRODUCTCATEGORY] ON COLUMNS,
[PRODUCT].[PRODUCT CATEGORIES].[CATEGORY] ON ROWS
FROM [ADVENTURE WORKS]

5.Modify SSRS report paramter property to allow multiple selection in ProductCategory parameter.

6.Create new GetReport dataset for main report. which will accept report multiple parameter value and convert it into MDX column.

 --MDX TO GET SSRS REPORT ON YEAR AND PRODUCT WISE PARAMETER VALUE WITH MULTIPLE SELECTION
SELECT 
{
[MEASURES].[ORDER COUNT],
[MEASURES].[SALES AMOUNT],
[MEASURES].[INTERNET ORDER COUNT],
[MEASURES].[INTERNET SALES AMOUNT]
} ON COLUMNS,
{ strtoset(@SalesYear) } * { strtoset(@ProductCategory) }  ON ROWS
 FROM [ADVENTURE WORKS]

7.Rename GetReport dataset MDX column with simple column name

8.Define Query parameter with multiple value for MDX query and assign parameter name same as SSRS report parameter.

9.Design report layout in report RDL file and assign column of GetReport dataset to it.

Points of Interest

This project will enhance reading of Cube data through MDX and display in SSRS report. This project also uses SSRS parameter with single and multiple selection.parameters passes to MDX query to display report based on given parameter value.

History

No updates available