Posted: 4/23/2012
I have been struggling with a particular MDX for quite a while now...and am no closer to a resolution!!
Let me start by saying that MDX is definitely my strong point.
For starters, I'm trying to run this MDX as dataset in SSRS query builder. When I try to run the query I get the following error:
Failed to parse the query to detect if it is MDX or MDX (MDXQueryGenerator)
Here is the query:
WITH MEMBER [Measures].[YTD GL Entry Amount] AS Aggregate ( YTD ( [Time].[Year - Month].CurrentMember ) , [Measures].[GL Entry Amount] ) MEMBER [Measures].[YTD GL Monthly Budget Amount] AS Aggregate ( YTD ( [Time].[Year - Month].CurrentMember ) , [Measures].[GL Monthly Budget Amount] ) SELECT { [Measures].[GL Entry Amount] ,[Measures].[GL Monthly Budget Amount] ,[Measures].[Budget Versus Actual - Monthly] ,[Measures].[YTD GL Entry Amount] ,[Measures].[YTD GL Monthly Budget Amount] } ON COLUMNS , { [DIM GLACCOUNT].[GLACCOUNT DEPT DESC].[GLACCOUNT DEPT DESC].ALLMEMBERS* [DIM GLACCOUNT].[GL ACCOUNT BASE TYPE CATEGORY DESC].[GL ACCOUNT BASE TYPE CATEGORY DESC].ALLMEMBERS* [DIM GLACCOUNT].[GL ACCOUNT BASE TYE DESC].[GL ACCOUNT BASE TYE DESC].ALLMEMBERS* [Time].[Year].[Year].ALLMEMBERS* [Time].[Month].[Month].ALLMEMBERS } , { CROSSJOIN( {[DIM GLACCOUNT].[GL ACCOUNT BASE TYPE CATEGORY DESC].[GL ACCOUNT BASE TYPE CATEGORY DESC].ALLMEMBERS*}, [DIM GLACCOUNT].[GL ACCOUNT BASE TYE DESC].[GL ACCOUNT BASE TYE DESC].ALLMEMBERS* ) } , {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]} DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT StrToSet (@SelectedMonth ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@CalendarYear ,CONSTRAINED ) ON COLUMNS FROM ( SELECT StrToSet (@Department ,CONSTRAINED ) ON COLUMNS FROM [ACCOUNTING] ) ) ) CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE
If anyone can provide any insight, that would be greatly apreciated!!
Posted: 4/24/2012
You have a syntax error. Go to http://mdx.mosha.com and paste the query into the window and click Format. Line 35 is where it blows up. But the problem is really the lines after that:
{ CROSSJOIN( {[DIM GLACCOUNT].[GL ACCOUNT BASE TYPE CATEGORY DESC].[GL ACCOUNT BASE TYPE CATEGORY DESC].ALLMEMBERS*}, [DIM GLACCOUNT].[GL ACCOUNT BASE TYE DESC].[GL ACCOUNT BASE TYE DESC].ALLMEMBERS* ) } , {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]} DIMENSION PROPERTIES MEMBER_CAPTION ,MEMBER_UNIQUE_NAME ON ROWS
I believe thats the part you need to straighten out.