Add a column to the MDX Query with a tuple

Question

I have an MDX Query running on the SSAS cube that returns lots of object codes and the period balances for them. I was able to add multiple period balances by using a crossjoin on the rows, however I would like to add one more row with the period end balance for the last fiscal period, and can't seem to figure out a way to do it.

The initial query is

  select 
    non empty 
        {
            [Object Code].[Object Code Number].[Object Code Number]
            *
            [Object Code].[Object Code Description].[Object Code Description]
            *
            [Object Code Pathing 1E 1R].[1E_R1 Value].[1E_R1 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R2 Value].[1E_R2 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R3 Value].[1E_R3 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R4 Value].[1E_R4 Value]
        }
  on rows,
        {
            [Measures].[Current Period Balance]
        }
        *
        {
        [Date].[Fiscal].[Fiscal Period].&[2016]&[1]:[Date].[Fiscal].[Fiscal Period].&[2016]&[7]
        }
     on columns
  from [Finance]

and when I am trying to add one more column

 select 
    non empty 
        {
            [Object Code].[Object Code Number].[Object Code Number]
            *
            [Object Code].[Object Code Description].[Object Code Description]
            *
            [Object Code Pathing 1E 1R].[1E_R1 Value].[1E_R1 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R2 Value].[1E_R2 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R3 Value].[1E_R3 Value]
            *
            [Object Code Pathing 1E 1R].[1E_R4 Value].[1E_R4 Value]
    }
 on rows,
        {
        [Measures].[Balance At Period End]
        *
        [Date].[Fiscal].[Fiscal Period]&[2016]&[7]
        },
        {
            [Measures].[Current Period Balance]
        }
        *
        {
        [Date].[Fiscal].[Fiscal Period].&[2016]&[1]:[Date].[Fiscal].[Fiscal Period].&[2016]&[7]
        }
     on columns
 from [Finance]

I get the Parser: The statement dialect could not be resolved due to ambiguity. error

and if I add it like

        {
            [Measures].[Current Period Balance],
            [Measures].[Balance At Period End]
        }
        *
        {
        [Date].[Fiscal].[Fiscal Period].&[2016]&[1]:[Date].[Fiscal].[Fiscal Period].&[2016]&[7]
        }
     on columns

I get Period end Balances for all periods, and this is not needed in the report, I only need the Balance at Period End for the very last period

whytheq whytheq · Accepted Answer · 2016-01-29T08:23:03

Here is your first piece of troublesome code:

crossjoin (
[Measures].[Current Period Balance]
 ,{
  [Date].[Fiscal].[Fiscal Period].&[2016]&[1]
 ,[Date].[Fiscal].[Fiscal Period].&[2016]&[2]
   ,[Date].[Fiscal].[Fiscal Period].&[2016]&[3]
  }
), //<<1
 crossjoin(
   [Measures].[Balance At Period End]
  ,{[Date].[Fiscal].[Fiscal Period].&[2016]&[3]}
 ) on columns
from [Finance]

At point 1 you have closed the first crossjoin and then put a comma - this is a syntax error.

You could try moving that brace from 1 to the end of the statement:

crossjoin (
   [Measures].[Current Period Balance]
  ,{
     [Date].[Fiscal].[Fiscal Period].&[2016]&[1]
    ,[Date].[Fiscal].[Fiscal Period].&[2016]&[2]
    ,[Date].[Fiscal].[Fiscal Period].&[2016]&[3]
   }
  , //<<1
  crossjoin(
     [Measures].[Balance At Period End]
   ,{[Date].[Fiscal].[Fiscal Period].&[2016]&[3]}
  ) 
 ) on columns  //<<now closing initial crossjoin here
from [Finance]

Ok I just tested the above via the following and it is not a valid approach:

SELECT 
  CrossJoin
  (
    [Measures].[Internet Sales Amount]
   ,{
      [Date].[Calendar].[Date].&[20060628]
     ,[Date].[Calendar].[Date].&[20060629]
    }
   ,CrossJoin
    (
      [Measures].[Internet Order Quantity]
     ,{[Date].[Calendar].[Date].&[20060629]}
    )
  ) ON COLUMNS
 ,[Product].[Product Categories].[All] ON ROWS
FROM [Adventure Works];

We get the following error:

Query (2, 3) The Measures hierarchy is used more than once in the Crossjoin function.

You could switch to the following structure, creating a set of tuples. This does run:

SELECT 
  {
      [Measures].[Internet Sales Amount]
    * 
      {
        [Date].[Calendar].[Date].&[20060628]
       ,[Date].[Calendar].[Date].&[20060629]
      }
   ,(
      [Measures].[Internet Order Quantity]
     ,{[Date].[Calendar].[Date].&[20060629]}
    )
  } ON COLUMNS
 ,[Product].[Product Categories].[All] ON ROWS
FROM [Adventure Works];

Result:

Add a column to the MDX Query with a tuple

1 Answers