Working MDX query doesn't work as a Calculated Member

0
votes

I have made the following mdx query to calculate a running total using some references I found on internet which works when I ran it on management studio.

With member measures.test as

( sum([Card Issue date].[YearQuarterMonth].[Calendar Year Month].&[2014]&>>[2014-10] :[Card Issue date].[YearQuarterMonth].CurrentMember , [Measures].[LoyaltyMembers]) )

select measures.test on 0,

[Card Issue date].[YearQuarterMonth].[Calendar Year Month] on 1

from [SalesDW_v1]

But the following calculated member I constructed from this gives null values.

CREATE MEMBER CURRENTCUBE.[Measures].[Subscriptions Running Total]

AS (

sum([Card Issue date].[YearQuarterMonth].[Calendar Year Month].&[2014]&[2014-10] :[Card Issue date].[YearQuarterMonth].CurrentMember , [Measures].[LoyaltyMembers]) )

,VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Demographics',FORMAT_STRING ="#,##0;-#,##0";

I would be glad, If someone could help me locate the problem.

Thank you very much,

Andreas

1
ssasmdx

1 Answers

0
votes

Looks like your member will work only for October, 2014, because of

[Card Issue date].[YearQuarterMonth].[Calendar Year Month].&[2014]&[2014-10] :[Card Issue date].[YearQuarterMonth].CurrentMember

Since you added it as calc member, when you add it from mdx designer, query gives null, if you also add time axis - it will show values for October till CurrentMember

You should avoid adding such members to cube UI, better embed them as report dataset query.