Parameterized Reporting Services Reports with Analysis Services as a Data Source
Microsoft Analysis Services is a commonly used data source for Microsoft Reporting Services reports. The combination provides an environment for creating and rendering interactive reports that are highly performant, flexible and rich in functionality. One of the features of Reporting Services that provides end users with the ability to interact with the data is the use of parameters within the MDX queries that allow users to request different data sets. Just about anyone who has created a Reporting Services report has had an opportunity to create parameterized queries, which might look something like the following:
SELECT NON EMPTY { [Measures].[Internet Freight Cost] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer Geography].[Customer].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@Date, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])
WHERE ( IIF( STRTOSET(@Date, CONSTRAINED).Count = 1,
STRTOSET(@Date, CONSTRAINED), [Date].[Calendar].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
OR
SELECT NON EMPTY { [Measures].[Internet Freight Cost] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer Geography].[Customer].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@Group, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])
WHERE ( IIF( STRTOSET(@Group, CONSTRAINED).Count = 1,
STRTOSET(@Group, CONSTRAINED), [Customer].[Customer Geography].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The STRTOSET() function is what really enables the use of parameters in the MDX queries used within Reporting Services. The convention for parameters in Reporting Services is to place the “@” symbol in front of the parameter name. I’ve been around Analysis Services and MDX long enough to know that DATE is the name for an attribute type (making it a reserved word) and GROUP is an MDX Keyword. (I know, what was I thinking using a reserved word and a keyword as parameter names?) When using either of the above MDX queries with Analysis Services 2005 or Analysis Services 2008, the queries would return the data as expected. Being the intrepid soul that I am, I therefore very confidently expect that when the data source is upgraded from Analysis Services 2008 to Analysis Services 2008 R2 that my report will reliably return the data sets that I expect. Unfortunately, what I’m going to get are the error messages below:
Parser: The syntax for ‘Date’ is incorrect
Parser: The syntax for ‘Group’ is incorrect
This is a known issue with the query parser in Analysis Services 2008 R2 (the published KB article is https://support.microsoft.com/kb/2638216). The issue was addressed in Service Pack 1 Cumulative Update 4 (build number 10.50.2796) and is not present in the SQL Server 2012 Analysis Services release. I’d be surprised if everyone reading this blog were familiar with all of the reserved words and keywords used with Analysis Services, so rather than simply saying “you can look ‘em up in Books On Line”, you should be able to find them at the following hyperlinks:
Comments
- Anonymous
April 18, 2012
Thanks for this write up with pointers to related articles.