SQL Server Analysis Services 2000 -- Aggregates not working for calculated members--Need help

• Monday, June 18, 2012 1:56 AM

   

   
  

  0

  Hi All,

  I am new to SSAS. Apologies if my question is pretty simple and answered in some other thread (though i searched and could not find solution to this). 

  In my application, SSAS Cubes are accessed through Excel for generating few reports.In the cube there are calculated members defined on measures which has the aggregate defined as 'SUM'.

  We are getting data of these calculated members if the MDX formed while generating excel report, doesnot use Aggregates. But not getting data if the MDX uses aggregates (i.e., when multiple dimension values are selected).below are the queries for both cases:

  --Data not available

  WITH  MEMBER [DIM1].[ByWeek].[XL_QZX] AS 'Aggregate ( { [DIM1].[ByWeek].[All DIM1].[2012].[10-Mar-12] , [DIM1].[ByWeek].[All DIM1].[2012].[03-Mar-12] } )'  
  SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[DIM3].[All DIM3]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
  ON COLUMNS , {[Measures].[M1 LY], [Measures].[M1 TY],
  [Measures].[M2 LY], [Measures].[M2 TY]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
  ON ROWS  
  FROM [CUBE1] 
  WHERE ([DIM2].[ByDay GMT].[All DIM2].[2012].[March].[Sun 04-Mar-12],
  [DIM1].[ByWeek].[XL_QZX])
  
  --Data available

  SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[DIM3].[All DIM3]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
  ON COLUMNS , {[Measures].[M1 LY], [Measures].[M1 TY],
  [Measures].[M2 LY], [Measures].[M2TY]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
  ON ROWS  
  FROM [CUBE1] 
  WHERE ([DIM2].[ByDay GMT].[All DIM2].[2012].[March].[Sun 04-Mar-12],
  [DIM1].[ByWeek].[All DIM1].[2012].[03-Mar-12])
  

  These calculated members M1 LY,M2 LY are based on values from two dimensions DIM1,DIM2. Part of calculated member as below:

  IIF([DIM1].[ByDay].CurrentMember.level.ordinal > 0,
  
  Sum(Crossjoin({[DIM2].[ByDay GMT].currentmember.lag(364)},
   {[DIM1].[ByWeek].currentmember.lag(52)}),
     [Measures].[M1 TY])
  )
  

  Could any one help in letting me know the reason why the calculated members are appearing blank with multiple dimension value selection. I can provide more details if required.

  Many thanks in advance.
  

  --Iris.

  
  

  • Edited by iris n Monday, June 18, 2012 1:58 AM
  •  
  • Reply
  • Quote