SSRS Report with Single and Multi Selection Parameter using MDX Query

• Download demo project - 18.9 KB

Introduction

This article is based on SSRS report which accept parameters. Report parameters get value from dataset through MDX query. Parameter is passed to main report dataset. MDX query fetches report based on parameter value. Business requires dynamic report to make its decision. When report is dynamic, different measures value flow makes report more important. I assume user already has understanding of basic SSRS report development using MDX. Please refer to my article Simple SSRS report using MDX 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 configured 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.

   

4. Create New Dataset GetYear for Sales year value in dropdownlist. MDX code is written below.
   SQL

   WITH
   MEMBER [MEASURES].[SALESYEAR] AS
   MEMBERTOSTR([DATE].[CALENDAR YEAR].CURRENTMEMBER)
   
   SELECT 
   {[MEASURES].[SALESYEAR]} ON COLUMNS,
   [DATE].[CALENDAR].[CALENDAR YEAR] ON ROWS
   FROM [ADVENTURE WORKS]

   

5. Rename GetYear Dataset MDX column with simple field name.

   

6. Create New Dataset GetProduct for productcategory value in dropdownlist. MDX code is writen below.
   SQL

   WITH
   MEMBER [MEASURES].[PRODUCTCATEGORY] AS 
   MEMBERTOSTR([PRODUCT].[PRODUCT CATEGORIES].CURRENTMEMBER)
   
   SELECT 
   [MEASURES].[PRODUCTCATEGORY] ON COLUMNS,
   [PRODUCT].[PRODUCT CATEGORIES].[CATEGORY] ON ROWS
   FROM [ADVENTURE WORKS]

   

7. Rename GetProduct dataset MDX column with simple name.

   

8. Create SSRS report parameter SalesYear.

   

9. Create SSRS report parameter ProductValue.

   

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

    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]

    

11. Rename GetReport dataset MDX column with simple column name.

    

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

    

    

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

    

The 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. The below images describe SSRS report with parameter 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.
   SQL

   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 parameter property to allow multiple selection in SalesYear parameter.

   

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

   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 parameter 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.
   SQL

   --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 mutiple 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 are passed to MDX query to display report based on given parameter value.

History

• 20^th July, 2014: Initial version