Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2011-04-19   |   Comments (11)   |   Related Tips: > 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:

report parameters of ssas

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:

get values from a query

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:

data source properties of ssas

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:

using the reportmonth parameters

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

the available values dialog specifies the dataset

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:

a typical mdx query

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:

the completed ssas cube 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


Last Updated: 2011-04-19


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, November 12, 2014 - 1:37:46 PM - Raymond Barley Back To Top

In the section Create Report Dataset Filtered by ReportMonth Parameter I haver the following MDX:

FROM ( SELECT ( STRTOSET(@ReportMonth) ) ON COLUMNS FROM [Adventure Works]) 
You have to specify how to set the value of the ReportMonth parameter; i.e. the ReportMonth parameter in the MDX query gets its value from what report parameter?  You have to fill in the Parameters dialog where I map the ReportMonth report parameter to the ReportMonth parameter in the MDX query.
 
Based on your error message I think your MDX query has a parameter named currentMonth and the dataset properties parameters dialog doesn't have that specified in the Parameter Name column or the Parameter Value column has reportMonth in it and there is no such report parameter. 

Wednesday, November 12, 2014 - 3:27:51 AM - pascal Back To Top

Hi Ray,

Just having a practise with your ssrs mdx tip but i'm having the below error when I try to create the last dataset to retrieve data

 

"Parser: The query contains the currentMonth parameter, which is not declared. (Microsoft SQL Server 2012 Analysis Services)"

 

Any help with appreciated,

Many thanks,

Pascal
 

 


Thursday, March 06, 2014 - 11:06:11 AM - Ray Barley Back To Top

To set a dynamic default value for a report parameter do the following:
- Right click on the parameter and select Parameter Properties
- Click Default Values
- Click the Expression button (fX)
- Enter the MDX expression

The MDX expression should return the member uniquename.  You can get the member unique name
format by opening SQL Server Management Studio and doing a New Query on your cube.  For example
in the AdventureWorksDW2012 cube the Date dimension has a Date.Calendar hierarchy where the Calendar Year
member unique name looks like this for the calendar year 2005:

[Date].[Calendar].[Calendar Year].&[2005]

="[Date].[Calendar].[Date].&[" + FORMAT(NOW(), "yyyy") + "]"


Thursday, March 06, 2014 - 9:50:24 AM - ola Back To Top

Hello Ray,

  Am working on a parameterless report, but i need to know how to make my MDX query be dynamic to report current dates with hard coding the dates in the reports every other month. For English monthname,Quarters, and Year.

Thanks

Ola


Thursday, January 09, 2014 - 8:00:30 AM - rhona Back To Top

 SELECT NON EMPTY { [Measures].[Last Actual Value] } ON COLUMNS, NON EMPTY { ([Posting Date].[Referred YYYY].[Referred YYYY].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Posting Date].[Referred YYYY].[All] } ) ON COLUMNS FROM [Healthstat]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 

Hi I have a parameter in a reports set up.  I want it to display the years showing the most recent year first.  It is using the dataset with the code above to populate the paramter.  where in this would I put the order by to get it to show the year descending?

Hope that makes sense


Tuesday, April 16, 2013 - 2:58:53 PM - Raymond Barley Back To Top

Do you have a dimension that has site and store in it?  If you did or can create one then you should be able to get this to work without resorting to MDX.  I've basically given up on MDX.  I've never been able to master it and I have tried.


Tuesday, April 16, 2013 - 12:26:34 PM - Charly Hdez Back To Top

I neeed to develop a report similar to this one, that promts you for site (like texas) in a cascading list, like the one you show inthis example, but after you select one site another cascading list will prompt you for a store (like store a, store b, store c) ther cach here is the the second cascading list must only show the stores related to the site bouth store and site are dinesions in my cube, i was able to do the MDX query for the first promt but the second one as become a head each, i tried the following among other things but all it does is show me the selected site and all of the stores regadles of the site, can you help?

 

WITH MEMBER [Measures].[ParameterCaption] AS [Stores].[Store].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] AS [Stores].[Store].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[ParameterLevel] AS [Stores].[Store].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} 

ON COLUMNS, NON EMPTY {([Logical Site].[Logical Site].[Logical Site].ALLMEMBERS *  [Lob].[LOB].[LOB].ALLMEMBERS)}

ON ROWS FROM (SELECT ( STRTOSET(@Site))  ON COLUMNS  , [Stores].[Store].ALLMEMBERS ON ROWS FROM [BUILDERS])


Tuesday, January 15, 2013 - 8:23:31 AM - Chandra Back To Top

Hi Ray,

I have a new requirement for cascade parameters in a ssrs report. I want to use relational data (SQL) for the report query and want to use SSAS (MDX) for cascade parameters.

Table Booklist: Book_ID, Publisher, Subject, Author, Title,PublishDate, Price

SSAS Database has following dimension:Publisher, Subject, Author

The cascade parameters are:Publisher,Subject, Author (in this order)

Again, the cascade parameters will use MDX query to get valid data from SSAS Cube. Here's a challenge 2. All the parameter list will have an added value 'Other'. When user selects 'Other', all possible values populate in that dropdown and resets other parameters accordingly.

Can you please help!!

Thanks,

Chandra

 

 


Wednesday, March 21, 2012 - 4:01:16 PM - christie Back To Top

Great site and great tips, thank you very much


Wednesday, November 09, 2011 - 6:06:40 PM - Steve Back To Top

Hi,

 

I have followed this step by step and I keep gettting an error Parser:The query contains the ReportMonth parameter, whcih is not declared. When I add the parameter via the @ icon it wants a deafault value the only thing it accepts a value that is from the cube calendar dimension then the report populates with that value and no drop down box.

 

any thoughts

Thanks

Steve


Tuesday, April 19, 2011 - 12:25:21 AM - Pascal Back To Top

If you have control over your date dimension it is relatively easy to add flags for commonly used selections. I use Current Month, Quarter and Year, which means this type of reporting sub-query is relatively easy.

Alternatively, using the text parsing in Reporting Services and the functionality you can build the same text as you are populating the table with in code, which I've seen some of our developers use.

However, I do like the idea of having a default parameters selection for Analysis Services. It seems like a useful addition.


Learn more about SQL Server tools