Filtering data in SSRS Report Builder by passing datetime parameters using DateTime Picker to MDX Query

0
votes

I'm a noob with MDX, but I've gotten SSRS to work with parameters with regular SQL queries and stored procedures. I want to filter the cube with a start and an end date, because there is a lot of data in there and the report takes too long using filters. In SSRS I am using the date time picker control to pick the dates.

Here's my MDX Query from SSRS after I set up the parameter, using the query designer:

 SELECT NON EMPTY { [Measures].[Detail Presentation Count], [Measures].[Duration], [Measures].[DurationSeconds] } ON COLUMNS, NON EMPTY { ([Cube View Detail Presentation Country Dimension].[Country Alpha2].[Country Alpha2].ALLMEMBERS * [Cube View Detail Presentation Country Dimension].[English Short Name].[English Short Name].ALLMEMBERS * [Presentation].[Name].[Name].ALLMEMBERS * [Presentation].[Revision].[Revision].ALLMEMBERS * [Presentation].[ID].[ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromCubeViewDetailPresentationTimeDimensionCreated, CONSTRAINED) : STRTOMEMBER(@ToCubeViewDetailPresentationTimeDimensionCreated, CONSTRAINED) ) ON COLUMNS FROM [DetailPresentation]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I've tried passing in various formats of the date value from the SSRS parameter, but none work, and the query will not run. The dates are in this format: 2011-01-04 06:10:38.000000.

What's the format of the date I need to pass, or do I need to build up more than this a date string for the MDX query to work?

1
reporting-servicesparameter-passingmdx

1 Answers

0
votes

I think I figured this out, basically with MDX you don't send in a value as the parameter, you send dimension members.

Let's say we we want to filter by month, normally I would do something like this for an SSRS parameter:

Month(Parameters!StartDate.Value)

But, this will not work. Instead MDX is expecting something like this:

[Cube View Detail Presentation Time Dimension].[Month Number].&[3]

So, from a SSRS parameter standpoint it would be:

=“[Cube View Detail Presentation Time Dimension].[Month Number].&[“ + MONTH(Parameters!StartDate.Value) + “]”

This works provided the dimension member exists. For example, if you send in 15 for a month, expect it to fail because there are only 12 months in the system.