Free text parameter in Reporting Services

Let’s say you have a report getting its data from a SSAS cube. In the cube you have a date dimension.

Now. Imagine that you want a parameter pointing to the date dimension and the parameter cannot be a “select” parameter and it must be NON-QUERIED, i.e. the parameter is a free text field.

 

To make this work you first have to create a parameter, lets call it Year. The data type is set to “Text”, available values is set to “None” and Default values is set to “No default value”.

From here there are to alternatives:

1. Build the parameter string manually.
2. Send the string directly to the cube.

 

Solution 1

Build your dataset using the query designer. Drag the Year attribute to the filter pane in query designer. Check the box in Parameters column and you should see a query similar to this when you switch over to “Query mode”.

SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To resolve the error “Could not create a list of fields…” with the detailed error “Query (1, 215) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.” that occurs when you click Ok, you must edit the query string manually.

STRTOSET(@DateCalendarYear, CONSTRAINED) should be edited to STRTOSET(@DateCalendarYear).

When the query is done go to the Parameters tab. For the Parameter DateCalendarYear set the value to ="[Date].[Calendar Year].&[" & Parameters!Year.Value & "]".

This statement returns a string pointing to a member in the Calendar Year hierarchy in the date dimension.

 

Solution 2

This solution is more clean and a little bit simpler. I haven’t done any performance comparisons so I don’t know which one is the fastest.

Create a new dataset using “Query Mode” and use the following query.

Select
[Measures].[Internet Sales Amount] on columns,
Filter(
    {[Date].[Calendar Year].Members},
     [Date].[Calendar Year].member_value = @Year)
      on rows
from [Adventure Works]

This query fetches the correct Years with the MDX filter function, just returning the year corresponding to the parameter.

Go to the Parameters tab and for the parameter Year, select @Year, created earlier in this example.

 

The final result look like this.