Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculated Member only gives out null Values, Why ? Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 10:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 30, 2013 1:33 AM
Points: 10, Visits: 15
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
Post #1496018
Posted Thursday, September 19, 2013 4:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 3,143, Visits: 1,366
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
Post #1496301
Posted Thursday, September 19, 2013 5:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 30, 2013 1:33 AM
Points: 10, Visits: 15
Thanks a lot
Post #1496317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse