YTD in MDX Query

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  bi query languages (mdx, dmx)   » YTD in MDX Query

YTD in MDX Query

Topic RSS Feed

Posts under the topic: YTD in MDX Query

Posted: 4/23/2012

Jedi Youngling 7  points  Jedi Youngling
  • Joined on: 3/19/2012
  • Posts: 1

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

Jedi Master 5620  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 237

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.

 



 


Page 1 of 1 (2 items)