0
votes

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

2

2 Answers

0
votes

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

0
votes

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];