March 26, 2009 at 10:04 am
I am trying to convert a report that queries relational data to one that queries OLAP data from a Cube. The report is parameterised which is preventing me from being able to use a particular dimension level to group on within the report.
My MDX query to build the dataset that the report uses is (abbreviated):
SELECT NON EMPTY { [Measures].[Batch Count], [Measures].[Reporting Quote Count] } ON COLUMNS
FROM ( SELECT ( STRTOSET(@DimUsersParentReportingGroup, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimPortalPortals, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimProductProducts, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimServiceTypesServiceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@YearMonth, CONSTRAINED) ) ON COLUMNS FROM [Quotes]))))) WHERE ( IIF( STRTOSET(@YearMonth, CONSTRAINED).Count = 1, STRTOSET(@YearMonth, CONSTRAINED), [Year Month].currentmember )...........rest of WHERE clause
If I try to add [Year Month].[Year Month] ON ROWS to the MDX query, I get an error message telling me that 'The Year Month hierarchy already appears in the Axis 1 axis'.
How do I get around this problem?
Thanks
Lempster
March 26, 2009 at 7:08 pm
You already have that in one of your many sub-select statements 'SELECT ( STRTOSET(@YearMonth, CONSTRAINED) ) ON COLUMNS'. You could simply remove that and place this parameter into your ROWS and it appears that you are going to need to remove the WHERE clause portion where you are referencing this also, not sure what that part is needed for.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 27, 2009 at 4:09 am
Thanks Dan,
I added the parameters using the graphical interface and the MDX query was simply what was constructed as a result of doing that. Being a relative novice at MDX - hence my other post asking about good MDX books - I wasn't sure how to edit the query to get what I wanted. But thanks to you I'm now making progress!
Cheers
Lempster
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply