Customize Dropdown List in SSRS Report using a SSAS Cube Data Source

By: Ray Barley | Comments (11) | Related: > Reporting Services Parameters

Problem

I have a requirement to develop a report that uses a SQL Server Analysis Services Cube as a data source. The report must allow the user to select the desired month from a dropdown list. The dropdown list must include the last 24 months of available data with the default value being the current month. The administrator must have the ability to specify the current month and change it as necessary. I can create the report, but I'm having some difficulty with implementing the report month parameter. Can you give me some ideas on how to do this?

Solution

Based on the requirements, you need to provide an MDX query to retrieve the list of months to populate the report parameter dropdown list and another MDX query to retrieve your data and filter it by the month selected.

In this tip we will walk through the following steps:

Create a table in a relational database where the administrator can store the current month
Create a CurrentMonth report parameter to retrieve the current month from the relational table
Create a dataset based on an MDX query that contains the last 24 months that will be used to populate the ReportMonth report parameter dropdown allowing the user to select the month
Create a ReportMonth parameter to allow the user to select a month for the report
Create an MDX query to retrieve some data filtered by the report month parameter

I'll use the Adventure Works cube that was published for SQL Server 2008 R2 as the basis for the report. I'll use Report Builder 3.0 to create a sample report.

Storing the Current Month Default Value

Let's create a table in a relational database to store default values for our reports. The table schema is as follows:

CREATE TABLE [dbo].[ReportParameters]
(
 [Name] [nvarchar](50) NOT NULL,
 [Value] [nvarchar](50) NOT NULL
)

The above table will be used to store any sort of parameter defaults that we need. Insert a row with the Name = 'CurrentMonth' and the Value = '[Date].[Calendar].[Month].&[2007]&[12]'. The Value represents the member unique name of our current month which is December 2007. The member unique name is what Analysis Services uses to uniquely identify a dimension member. When you browse the Date dimension in the Adventure Works cube you can see the member unique name as shown above.

Retrieving the Current Month into a Report Parameter

The following steps are required in Report Builder to retrieve the current month value for our report and store it in a report parameter:

Create a data source that points to the relational database that contains the ReportParameters table
Create a dataset that retrieves the current month value
Create a hidden parameter and set its default value to the current month value retrieved in the dataset

Report Builder has a Report Data section as shown below:

using ssas cube as a data source implementing the report month parameter

Right click on Data Sources in the Report Data section, select Add Data Source, and fill in the dialog as shown below:

in the report data section of ssas select add data source

Right click on Datasets in the Report Data section, select Add Dataset, and fill in the dialog as shown below to add a query to retrieve the current month value:

add a query to retrieve the current month value

Right click Parameters in the Report Data section, click Add Parameter, and fill in the dialog as shown below:

Note that the Hidden radio button is selected under visibility; we are using this parameter just as a way to retrieve the current month value. Click Available Values on the Report Parameter Properties dialog and fill in as shown below:

click available values on the report parameter properties

Click Default Values on the Report Parameter Properties dialog and fill in as shown below:

At this point we have defined a hidden report parameter that will retrieve the current month value from the ReportParameters table.

Create Dataset for the Report Month Parameter Dropdown

We need to create a dataset that contains the last 24 months based on the current month that we assigned to the CurrentMonth parameter in the previous section. Right click on Data Sources in the Report Data section, select Add Data Source, and fill in the dialog as shown below:

Right click on Datasets in the Report Data section, select Add Dataset, and fill in the dialog as shown below:

the mdx query is a standard for a dataset that populates a dropdown list

The full text of the query is shown below:

 WITH
MEMBER [Measures].[ParameterCaption] AS
    [Date].Calendar.CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS
    [Date].[Calendar].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS
    [Date].[calendar].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
    {
        [Measures].[ParameterCaption] ,
        [Measures].[ParameterValue],
        [Measures].[ParameterLevel]
    } ON COLUMNS,
    {
        LASTPERIODS(24, StrToMember(@CurrentMonth))
        } ON ROWS 
FROM [Adventure Works]

The above MDX query is standard for a dataset that populates a dropdown list based on a cube dimension with the exception of LASTPERIODS(24, StrToMember(@CurrentMonth)). LASTPERIODS is an MDX function that retrieves a number of periods based on a specified period. In this case we want the last 24 periods relative to the current month value retrieved from the ReportParameters table; this value is available to us in the CurrentMonth parameter. StrToMember is an MDX function that converts a string to a member (the CurrentMonth parameter value is a string that must be converted to a member).

We have to associate the CurrentMonth parameter with the @CurrentMonth placeholder in the MDX query. Click Query Designer on the Dataset Properties dialog, click the toolbar icon to display the Query Parameters, and fill in as follows:

in query designer click the toolbar icon to display the query parameters

The toolbar icon is the icon that you see on the Query Parameter window title. We are associating the CurrentMonth parameter specified earlier with the @CurrentMonth placeholder in the MDX query. You have to enter a value for the Default; this value would only be used if the CurrentMonth parameter did not have a value. In order for our report to run correctly, the CurrentMonth parameter must always retrieve a value from the ReportParameters table.

Create the ReportMonth Report Parameter

The ReportMonth parameter will allow the user to select a month from the dropdown list of 24 months. Right click Parameters in the Report Data section, click Add Parameter, and fill in the dialog as shown below:

Click on Available Values in the Report Parameter Properties dialog and fill in as shown below:

The Available Values dialog specifies the dataset that provides the list of 24 months with the default value as specified in the ReportParameters table.

Click Default Values in the Report Parameter Properties dialog and fill in as shown below:

choose the default values for this parameter

The Default Value is set to the CurrentMonth report parameter defined earlier.

Create Report Dataset Filtered by ReportMonth Parameter

In this last step we need to create a dataset that retrieves the data for our report and filters it based on the month selected in the ReportMonth parameter dropdown. Right click on Datasets in the Report Data section, select Add Dataset, and fill in the dialog as shown below:

The above MDX query is a typical one, but we have to enter it as text in order to use our custom ReportMonth parameter. The part of the MDX query that filters based on the ReportMonth parameter is shown below:

FROM ( SELECT ( STRTOSET(@ReportMonth) ) ON COLUMNS FROM [Adventure Works])

The FROM clause filters the results based on the ReportMonth parameter which is a string and must be converted to a set by using the MDX function StrToSet.

Click on Parameters on the Dataset Properties dialog to associate the ReportMonth parameter with the @ReportMonth placeholder in the MDX query:

associate the reportmonth parameter with the @reportmonth placeholder in the mdx query

Sample Report

The following is the output shown when running the report:

Select Month is the prompt for the ReportMonth parameter which shows the default value of December 2007 as retrieved from the ReportParameters table. Clicking the dropdown will display the list of the last 24 months.

Next Steps

Customizing report parameters that use an SSAS cube is a bit of a tedious job, but sometimes necessary based on the requirements that you have been given.
You can download the sample report here.
The Adventure Works cube used in the sample report is available from this CodePlex site.
You can download Report Builder 3.0 here

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Customize Dropdown List in SQL Server Reporting Services Report using a SSAS Cube Data Source