MDX trouble in a calculated member

• maretix

  Hall of Fame

  Points: 3835

  More actions

  January 19, 2013 at 9:34 am

  #149679

  Hi to all.

  I use SSAS 2008 R2 and i built a cube.

  In this cube i have a little trouble with a calculated member i have to do.

  My new calculate member is named AllSalesAmountOfWholeCompany

  I need to calculate the total amoun of sales of all company.

  I'd like to be able my users, to display in the same moment sales amount fo a single store of the company and in the same row the total amount of all company...

  (A buyer asked me that and i found interesting to do !!!)

  I built calculate member in MDX and i was able to do that BUT my trouble is to hide the row of stores that has SALES AMOUNT as NULL or 0.....(In the example member BOLOGNA Store...)

  How can i do that ???? Inside Calculation member definition or where ????

  I have the same problem in Excel and SQL SERVER MANAGEMENT STUDIO too...

  I suppose i have to force NULL or 0 my calculated member AllSalesAmountOfWholeCompany, in this way, Excel will display only stores with a Sales Amount > 0.

  But i tried i was not able,,,i am not a super MDX man ,,,i know

  DIM STORES --- Sales Amount of a Month ---- AllSalesAmountOfWholeCompany

  Turin Store ----- 100 --- 120.000

  Bologna Store ----- NULL ---- 120.000

  Venice Store ----- 300 ---- 120.000

  Here is my MDX that it works correctly

  SUM

  (

  {

  ( [Company].[All Company].[COMET],

  [Stores].[Stores].[Stores]

  )

  }

  ,[Measures].[Sales Amount])

  Thanks in advance...

• Mackers

  SSCrazy

  Points: 2935

  More actions

  January 20, 2013 at 8:42 am

  #1579029

  You could use a case statement or iif statement. I'd also use the non_empty_behavior clause as well

  IIF(ISEMPTY([Measures].[Sales Amount] )

  ,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount])

  ,NULL)

  ,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}

  Mack

• Mackers

  SSCrazy

  Points: 2935

  More actions

  January 20, 2013 at 10:09 am

  #1579032

  Think I've got the iif statement the wrong way around

  IIF(ISEMPTY([Measures].[Sales Amount] )

  ,NULL

  ,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) )

  ,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}

  Apologies

  Mack

• maretix

  Hall of Fame

  Points: 3835

  More actions

  January 21, 2013 at 10:39 am

  #1579327

  Ciao

  Many thanks to you ..

  It works !!!

  Thanks again !!!!:-)

• happycat59

  One Orange Chip

  Points: 29386

  More actions

  January 21, 2013 at 8:22 pm

  #1579425

  I think that something like the following might do what you are after...

  with member measures.AllSalesAmountOfWholeCompany AS

  (SUM

  ( { ( [Company].[All Company].[COMET],

  [Stores].[Stores].[Stores]

  )

  }

  ,[Measures].[Sales Amount])

  ), NON_EMPTY_BEHAVIOR = 'Sales Amount'

  select {[Measures].[Sales Amount] ,

  [Measures].[AllSalesAmountOfWholeCompany]

  } on columns,

  {[Stores].[Stores].Members} on rows

  from [Your Cube]

• maretix

  Hall of Fame

  Points: 3835

  More actions

  January 22, 2013 at 7:23 am

  #1579626

  Many thanks.

  I think it is a good soluton too.

  Someone advice me that is it works the same...

  IIF(ISEMPTY([Measures].[Sales Amount] )

  ,NULL

  ,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) )

  ,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}

  Thanks a lot.

  Sorry but even if i use SSAS 2008 R2 from a long time (5 years) , i am not so good in MDX.

  i HAVE TO IMPROVE MY knowledge.