65.9K
CodeProject is changing. Read more.
Home

SSRS Report with Single and Multi Selection Parameter using MDX Query

Jul 22, 2014

CPOL

3 min read

viewsIcon

44383

downloadIcon

418

This article is based on SSRS report with single and multiple selection parameter controlled through MDX query. SSRS report passes parameter. MDX query reads record from cube based on given parameter.

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

  • 20th July, 2014: Initial version