Using a Date Parameter in SSRS with MDX

Posted on March 22, 2011

2



There are two ways of using a date parameter within SQL Server Reporting Services for reports using SSAS as the source. I’d firstly like to thank my friend Boyan Penev for helping me out with some MDX questions I’ve had – please visit his excellent blog:
http://www.bp-msbi.com/

The first method is to use a MDX statement to generate the Date values, and I will be discussing this method in this post.
When using an MDX query for a parameter, we need to haveboth the UniqueName and the Name returned. The UniqueName value is what we will use in the query for our main query that will give us the results to be display a grid or chart, and the Name value will be shown in the dropdown list.

Here is the query we will use for our date parameter:


WITH
MEMBER DateValue
AS
   [Date].[Calendar].CurrentMember.UniqueName
MEMBER DateLabel
AS
   [Date].[Calendar].CurrentMember.Name
SELECT
{
    [Measures].[DateValue],
    [Measures].[DateLabel]
} ON 0,
{
     [Date].[Calendar].[Date]
} ON 1
FROM [Adventure Works]

 

This will generate a list of values:

Date Parameter MDX

Date Parameter MDX

So let’s create a report with a date parameter using the above MDX query.

First create a dataset for the Date values and use the above MDX query. We
will call this dataset dsDate.  We then create a parameter that we will call pDate. We keep the parameter data type as Text.
On the Available values tab we select DateValue for the Value field, and DateLabel as the Label field.

Now we will use the following MDX to create a report based on a date:

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY{[Product].[Subcategory].[Subcategory]} ON 1
FROM (SELECT [Date].[Calendar].[Date].&[20070919] ON 0
		FROM [Adventure Works]
		)
 

We create a new dataset called dsMain and paste the above MDX. We then need to click on
the Query Parameters button and add a default value for our parameter. This
default will be reflected as the default date value in the report.

Back to the query, where we will replace the calendar date column with our parameter value as StrToMember(@pDate,Constrained). The query will now be:

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY{[Product].[Subcategory].[Subcategory]} ON 1
FROM (SELECT StrToMember(@pDate, CONSTRAINED) ON 0
		FROM [Adventure Works]
		)
 

You can see that we use a sub-query for our date parameter and StrToMember with the second argument set to Constrained. This is to prevent Injection attacks, and also improves performance.

We can now execute the query to test if it works.

We then need to click on the Parameter tab on the dsMain dataset and set the parameter value as  @pDate e.g.
We then drag a Table object onto the report and populate it with Subcategory and Internet Sales.

Often times with a report, it is wise to include the parameter values on the report. This is especially helpful when users print a report – without the parameters no one would know what the user selected to get the results on the printout.

To include the date parameter on the report we can simply drag the pDate parameter onto the screen and resize it. We right-click on the parameter we’ve dragged onto the screen and click Expression. We then change the expression from

=Parameters!pDate.Value to =Parameters!pDate.Label

So the report will look like this:

If we change the date parameter and click View Report, the results will change.

Advertisements
Posted in: SSAS, SSRS