August 31, 2009 at 4:27 am
This seems to be more of an issue within SSAS than SSRS. Within SSMS I can query the Analysis Service Database with the following.
SELECT ( STRTOSET("Initiative", CONSTRAINED) ) ON COLUMNS
FROM [AS_DB]
-> results are returned, as 'Initiative' is an option within one of the dimensions
SELECT ( STRTOSET("Standard Request", CONSTRAINED) ) ON COLUMNS
FROM [AS_DB]
-> Error: Parser: The syntax for 'Request' is incorrect.
Using the option of specifying the member in the dimension doesn't really work because the SSRS report needs to use multi-select parameter. I know this is autogenerated MDX, but this will give you an idea if what I'm trying to achieve.
SELECT NON EMPTY { [Measures].[Id] } ON COLUMNS,
NON EMPTY { ([Date_Closed].[Month Short Name With Year].[Month Short Name With Year].ALLMEMBERS * [SC Fields].[Contact Sla].[Contact Sla].ALLMEMBERS * [SC Fields].[Category_Object_Id].[Category_Object_Id].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@SCFieldsCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( -{ [SC Fields].[Contact Sla].&[] } ) ON COLUMNS FROM ( SELECT ( { [SC Fields].[Status].&[Closed] } ) ON COLUMNS FROM ( SELECT ( { [Date_Closed].[Year Name].&[2009] } ) ON COLUMNS FROM [MWC OVSD])))) WHERE ( [Date_Closed].[Year Name].&[2009], [SC Fields].[Status].&[Closed], IIF( STRTOSET(@SCFieldsCategory, CONSTRAINED).Count = 1, STRTOSET(@SCFieldsCategory, CONSTRAINED), [SC Fields].[Category].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I have tried searching but I can't seem to find a way of enclosing the dimension label text for when there is a space in it.
September 2, 2009 at 5:01 pm
Solved this myself.
There were a few key discoveries on the way.
Right click > Dataset parent > Show Hidden Datasets .. allowed me to see the query behind the SSRS automatically generated parameters. My own custom created dataset did not have attributes like 'ParameterValue', 'ParameterValueIndented' & 'ParameterLevel'.
Why not just use the SSRS generated parameter? I didn't want the 'All'/'Unknown' option in the drop down. This was fixed by changing the fields displayed from ALLMEMBERS to CHILDREN given that PARAMETERLEVEL=1 was all that I wanted.
August 26, 2010 at 5:06 pm
How did you solve the problem?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply