Skip to content
Tags

,

Free text parameter in Reporting Services

2011/09/28

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.

image

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]

image

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.

image

 

The final result look like this.

image

Advertisements
One Comment
  1. kruhaha permalink

    Hi. Thanks for your post but I cant seem to make this work in VS 05 ssrs on this part –

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: