I am trying to create a parameter driven SSRS report agains my cube but I want the parameter to contain only the Key value of the member I am trying to select. When I use the query designer, I get something like this:
SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED)
Where @.PlantStructureDepartment is my paramter and contains the Unique Name of the member like this "[Plant Structure].[Department].&[39952]" where "39952" is the Key value.
What I want is to be able to pass the paramter in as "39952" and build the rest of the string. I tried this:
SELECT ( STRTOSET('[Plant Structure].[Department].&[' + @.PlantStructureDepartment + ']', CONSTRAINED)
but that didn't work.
The reason I'm trying to do this is that we are using SharePoint integrated filtering and we have a number of basic relational reports using the same filters as the reports against the cubes. We would like to keep them all using the integer values.
For sake of completeness, here is the full query as it sits right now:
SELECT NON EMPTY { [Measures].[FTT Percent] } ON COLUMNS, NON EMPTY { ([Plant Structure].[Prod Line].[Prod Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED) ) ON COLUMNS FROM [FTT]) WHERE ( IIF( STRTOSET(@.PlantStructureDepartment, CONSTRAINED).Count = 1, STRTOSET(@.PlantStructureDepartment, CONSTRAINED), [Plant Structure].[Department].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I am fairly new to MDX and would appreciate any help or suggestions.
Here is a copy of the query modified to use Adventure Works but illustrating what you requested. Where I've used '1', you would use your report parameter value.
Hope that helps,
Bryan
Code Snippet
SELECT
NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[SubCategory].[SubCategory].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED) ) ON COLUMNS
FROM [Adventure Works])
WHERE (
IIF( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED).Count = 1,
STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED),
[Product].[Category].currentmember
)
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
|||Bryan,
Thanks for the reply. I think you are stating the same thing that I tried already. When I hardcode the value as you suggest, the query works. However, when I set up the parameter, I get this message:
Parser: The query contains the Department parameter, which is not declared. (msmgdsrv)
This is because the paramter isn't declared in the data tab of the report. When I declare the parameter, I get this error:
Query (6, 23) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. (Microsoft SQL Server 2005 Analysis Services)
The parameters page of the Data tab only allows you to tie a parameter to a dimension of the cube. I think I need a way around that.
Thanks again for the idea. If you have another, I'd appreciate it.
Chris
|||The CONSTRAINED keyword is there to prevent an injection of a function call into the statement. While I think this is a good idea, I get the same error as you do when no injection is occuring. While I would recommend digging into the MDX statement a little deeper to understand the issue, I have in the past simply removed the CONSTRAINED keyword.
Not sure this is the best of ideas, but .....
Bryan
|||That's what it was.
Thanks!
|||You have CONSTRAINED in several areas, do you need to remove them all. Can you post your modified code?
Thanks,
Shari
|||I don't have access to my query right at the moment (I'm at a different location) but suffice it to say, I removed all of the CONSTRAINED flags and it worked. To the point made earlier, I'm not sure if it was a good idea, but it got me around the issue I was having.|||I tried that, removing all the CONSTRAINED but got another error about STRTOSET function expects a tuple set, string was supplied .... . I am anxious to see your query if you could share (when you have access).
Thanks,
Shari
