MDX: Crossjoin and set evaluation

Question

I'm struggling with this MDX that on rows should return crossjoin of Date and Countries sorted by value. Apparently, "[Date].[Fiscal Year].CurrentMember" in Order function during crossjoin is still returning default member, not the current member from the crossjoin context. Perhaps my understanding how and when the set during crossjoin is evaluated is not correct?

WITH 
  SET [DATE_main] AS 
    {
      [Date].[Fiscal Year].&[2002],
      [Date].[Fiscal Year].&[2003]
    } 
  SET [CUSTOMER_ordered] AS 
    {
      Order
      (
        [Customer].[Customer Geography].[Country].Members,
        (
          [Customer].[Customer Geography].CurrentMember,
          [Date].[Fiscal Year].CurrentMember,
          [Measures].[Internet Sales Amount]
        ),
        BDESC
      )
    } 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS,
  CrossJoin
  (
    [DATE_main],
    [CUSTOMER_ordered]
  ) ON ROWS
FROM [Adventure Works];

Thanks for any advice, Endokr

score 0 · Answer 1 · answered Feb 12 '13 at 23:54

0

To have the expected result you must put the crossjoin inside the order.

answered Feb 12 '13 at 23:54

Benoit

1,995
1
13
18

I forgot to mention that i need [DATE_main] members to be in the order specified by the set: (2002, Australia), (2002, Canada), (2003, Australia), (2003, Canada) etc. Basically need to sort countries in the context of each [DATE_main] member. – Endokr Feb 13 '13 at 08:50

score 0 · Answer 2 · answered Feb 13 '13 at 08:53

This solves my problem but I still would like to know why [Date].[Fiscal Year].CurrentMember does not work as I expected in the crossjoin:

WITH 
  SET [DATE_main] AS 
    {
      [Date].[Fiscal Year].&[2002],
      [Date].[Fiscal Year].&[2003]
    } 
  SET [CUSTOMER_ordered] AS 
    {
      Order
      (
        [Customer].[Customer Geography].[Country].MEMBERS,
        (
          [Customer].[Customer Geography].CurrentMember,
          [Date].[Fiscal Year].CurrentMember,
          [Measures].[Internet Sales Amount]
        ),
        BDESC
      )
    } 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS,
  Generate
  (
    [DATE_main],
    Order
    (
      CrossJoin
      (
        [Date].[Fiscal Year].CurrentMember,
        [Customer].[Customer Geography].[Country].MEMBERS
      ),
      (
        [Measures].[Internet Sales Amount]
      ),
      BDESC
    )
  ) ON ROWS
FROM [Adventure Works];

MDX: Crossjoin and set evaluation

2 Answers2