Mdx query incosistent with cube browser

Question

I have built a multidimensional SSAS cube with some calculations. I have a calculated member in which I use the the parallelperiod function to calculate the previous month value according to the following code :

Sum
(
  (EXISTING 
    [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[date].MEMBERS)
 ,(
    ParallelPeriod
    (
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[month]
     ,1
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].CurrentMember
    )
   ,[Measures].[Net Amount]
  )
)

When I run the following query it works as expected.

SELECT 
  {
    NetAmountSamePeriodLastMonth
   ,[Measures].[net amount]
  } ON COLUMNS
 ,[Stores Dim].[Store Code].Children ON ROWS
FROM [SalesDW_v1]
WHERE 
  {
    [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
   ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
  };

But when I browse the cube to get the same data with the same date filters It gives wrong numbers. The generated from browser mdx script is :

SELECT 
  NON EMPTY 
    {
      [Measures].[Net Amount]
     ,[Measures].[NetAmountSamePeriodLastMonth]
    } ON COLUMNS
 ,NON EMPTY 
    {[Stores Dim].[Store Code].[Store Code].ALLMEMBERS}
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    {
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
    } ON COLUMNS
  FROM [SalesDW_v1]
)
WHERE 
  [TimeDim Transactions].[Year -  Quarter -  Month -  Date].CurrentMember
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

Something more, the browser shows correct numbers when one date is selected.

Is there a way to modify the calculated member in order to work correctly at the browser. I present the data on a power view report, but what i get is the incorrect numbers I see at the browser.

Below the code generated by browser when I use only one date member :

SELECT 
  NON EMPTY 
    { [Measures].[Net Amount]
     ,[Measures].[NetAmountSamePeriodLastMonth] 
    } 

    ON COLUMNS 
    ,NON EMPTY 
    { ([Stores Dim].[Store Code].[Store Code].ALLMEMBERS ) } 
    DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME ON ROWS 

   FROM 
       ( 
        SELECT (
                {[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00] } 
               ) ON COLUMNS 

  FROM [SalesDW_v1]
       )
  WHERE ( [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00] ) 
CELL PROPERTIES VALUE
, BACK_COLOR, FORE_COLOR
, FORMATTED_VALUE
, FORMAT_STRING
, FONT_NAME
, FONT_SIZE
, FONT_FLAGS

1) When you say wrong, how wrong is that? Is that a close figure or a weirdly large figure? 2) Please paste the MDX generated when you use only one date. — SouravA
It is a weirdly large figure. I expect ~30000 and I get ~1600000. I edited the question to provide the code generated by the browser when I use only one date member. Thanks — Andreas Dest

SouravA SouravA · Accepted Answer · 2015-02-24T09:14:37

The problem is that the scope(for [TimeDim Transactions] dimension) is not getting set correctly when you are having two dates in filter. But, in case of single date, the scope sets correctly, and hence the answer matches.

When one date is selected

See the clause associated with slicer. It is [TimeDim Transactions].[Year - Quarter - Month - Date].[Date].&[2014-12-05T00:00:00] )

When two dates are selected

The slicer becomes: [TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember

Note that a sub-select does not set a context. So when you say that you want to filter by [TimeDim Transactions].[Year - Quarter - Month - Date].CurrentMember, you are not actually filtering at all, since the current member ins absence of scope is the [All] member. That's why you are having a weirdly large figure.

To get rid of the issue, try having the dates in the axes, as below, instead of slicer, so that the scope gets selected.

 ,NON EMPTY 
    {[Stores Dim].[Store Code].[Store Code].ALLMEMBERS} * 
    {
      [TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-04T00:00:00]
     ,[TimeDim Transactions].[Year -  Quarter -  Month -  Date].[Date].&[2014-12-05T00:00:00]
    }

  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS

Mdx query incosistent with cube browser

1 Answers