SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculated Member only gives out null Values, Why ?


Calculated Member only gives out null Values, Why ?

Author
Message
gb 86916
gb 86916
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4449 Visits: 1650
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
gb 86916
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 15
Thanks a lot
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search