Calculated Member

I'm trying to add a calculated member to my SSAS 2005 cube. I can calculate what I want using MDX, but I'm having trouble converting my MDX query to the something SSAS understands as a calulated measure.

This is my MDX query.

Working MDX

SELECT

{([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)} ON COLUMNS

,{[Employee].[Emp Full Name].allmembers}ON ROWS

FROM [Employee Scorecard]

WHERE ([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34])

Here is my calculated member

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

[EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

I would also like to add a similar member where the state name is not Texas. How would I do that as well?

I changed my scope. I forgot the "This =" . But it still dosn't work.

Non working calculated member

CREATE MEMBER CURRENTCUBE.[MEASURES].[TX Docs Ready <=10 Days]

AS ([Measures].[# of Activities]

,[Geography].[State Name].&[TEXAS]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([Measures].[TX Docs Ready <=10 Days]);

This = ([EN To Activity Turn Time].[Turn Time Name] = [EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]);

end scope;

|||

It appears that you want to sum the activity count for a set of activity turn times, filtered by a specific segmentation and special activity. I would start off by creating a base measure (which you could set to invisible if you want)

Code Snippet

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

AS

SUM([EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34],

([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

Then creating a TX specific version

Code Snippet

Create MEMBER CurrrentCube.Measures.[TX Docs Ready <=10 Days]

AS

(Measures.[Docs Ready <=10 Days],[Geography].[State Name].&[TEXAS])

Then the non TX can simply be the total, minus the TX measure. You could calculate this by summing a set of all states except TX, but this should be faster

Code Snippet

Create MEMBER CurrrentCube.Measures.[Not TX Docs Ready <=10 Days]

AS

Create MEMBER CurrrentCube.Measures.[Docs Ready <=10 Days]

- Measures.[TX Docs Ready <=10 Days]

|||

I see what your saying. However, I think that by using "Scope" the code would be more readable. I've been playing with it and this is what I have so far.

I can get this to work and it returns values. But it's not exactly what I want.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS ([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

),

VISIBLE = 1;

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = [EN To Activity Turn Time].[Turn Time Name].&[34];

end scope;

This = [EN To Activity Turn Time].[Turn Time Name].&[2];

end scope;

What I really want, is something like this

Code Snippet

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

But when I run this code all I get is empty values.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

scope ([Geography].[State Name].&[TEXAS]);

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

This = Sum({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

end scope;

What am I doing wrong with this scope?

|||

The scope statement is not about readability, it is about restricting the sbucube over which a calculation is performed. I am pretty sure that the second assignment you are doing will just override the previous one that you did for Texas and by summing the measure that you are scoping over, it could be trying to recurse over itself. What I think you want is something more like the following. Assuming that you want to sum over Activity 2-32 if it's not Texas and 2-34 if it is Texas.

Code Snippet

scope ([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

scope ([Geography].[State Name].&[TEXAS]);

This = Aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]});

end scope;

end scope;

|||

It dosn't like the aggregate. I've tried both of these and the results are the same as if I never did the scope. I've also tried Sum(Aggregate()) and Sum().

Code Snippet

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},[MEASURES].currentmember);

This = aggregate({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]});

I feel like I'm really really close but I'm clearly missing an important concept.|||

I'm not exactly sure what the problem is. We are making it hard for the calc engine, telling it that this measure is the sum of itself. Normally I would do an approach like the following.

Code Snippet

-- this is the default calc

CREATE MEMBER CURRENTCUBE.[MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]

AS SUM({[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[32]},([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

)),

VISIBLE = 1;

-- override the calculation for TX

scope ([Geography].[State Name].&[TEXAS]);

([MEASURES].[Docs Ready <= 7 Days (10 in TX)(Fast Track)]) = SUM(

{[EN To Activity Turn Time].[Turn Time Name].&[2]:[EN To Activity Turn Time].[Turn Time Name].&[34]}

,([Measures].[# of Activities]

,[Special Activity Measure].[Special Activity Measure Name].&[Ready to Close]

,[Segmentation].[Segmentation Name].&[Fast Track]

));

end scope;

If this does not work, could you post a simple example of what results you are seeing and what you were expecting.

sql