Calculated Member only gives out null Values, Why ?

• gb 86916

  Mr or Mrs. 500

  Points: 562

  More actions

  September 18, 2013 at 10:46 am

  #298793

  I Need to do the following calculation in mdx (i hope the code is straightforward, if clarification is needed please tell)

  ([Orders].[Customer].CurrentMember, [Measures].[InvoiceAmount]) / (Root([Orders]), [Measures].[InvoiceAmount])

  but all I'm getting is NULL Values, can anybody see what is Wrong ? Thanks to all of you in advance, your always great help is much appreciated.

  SQL Server 2012

• Mark Fitzgerald-331224

  SSCrazy Eights

  Points: 8489

  More actions

  September 19, 2013 at 4:39 am

  #1651434

  You do not state the context of the query (SSAS cube design calculations tab, SSMS query or SSRS report dataset)

  ----- SSAS Scoped assignment

  DECLARE MEMBER CURRENTCUBE.[Measures].[PercentRoot-Scoped]

  AS "N/A", VISIBLE = 1, FORMAT_STRING="0.00%";

  SCOPE ([Measures].[PercentRoot-Scoped], [Geography].[City].[City].MEMBERS);

  THIS = ([Geography].[City].CURRENTMEMBER, [Measures].[Internet Sales Amount]) /

  (ROOT([Geography]),[Measures].[Internet Sales Amount]);

  END SCOPE;

  ----- SSMS and SSRS

  WITH MEMBER [Measures].[PercentOfRoot] AS

  ([Geography].[City].CURRENTMEMBER, [Measures].[Internet Sales Amount]) /

  (ROOT([Geography]),[Measures].[Internet Sales Amount]),

  FORMAT_STRING = '0.00%'

  SELECT

  {[Measures].[Internet Sales Amount], [Measures].[PercentOfRoot]} ON COLUMNS,

  NON EMPTY [Geography].[City].[City].MEMBERS ON ROWS

  FROM

  [Sales Cube]

  It may be that the ROOT is not available (isaggregateable?) or that the ROOT value is NULL (calculation assignment?).

  Fitz

• gb 86916

  Mr or Mrs. 500

  Points: 562

  More actions

  September 19, 2013 at 5:20 am

  #1651447

  Thanks a lot