Using the DatePicker in SSRS with MDX

Posted on March 22, 2011

4



This post, continuing on from the previous post https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/, shows how to use the DatePicker functionality within SSRS as a parameter for an MDX query.
First we set up the Date parameter, which we will call pDate. We change the data type to Date, and leave all other tabs with the default settings.

We then create our main data set, dsMain. We will use the same query as the previous post:


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]
		)

Paste the query into the Query  editor and click on Query Parameters, setting
a default value:

We modify the MDX script to:


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

We now click on the Parameter tab on dsMain and click on Expression for the value. We set the value to:

=”[Date].[Calendar].[Date].&[“& Format(CDate(Parameters!pDate.Value),”yyyyMMdd”) + “]”

 

Just as in the previous post we want to display the Date parameter in the report. To do that we need to format the DatePicker result. We drag the pDate parameter onto the report, and edit the expression. Set the expression to

= Format(Parameters!pDate.Value, “dd MMMM yyyy”)

For more information about date formats see: http://technet.microsoft.com/en-us/library/aa720651(VS.71).aspx

We can now run the report and see the results:

 

Advertisements
Posted in: SSAS, SSRS