May 29, 2009 at 3:33 am
I want the results to show Name on the rows -which it does - but I do not want it to split out the Reponse Desc on the columns. I want to see a total figure for the [Activated] named set. I am sure I have done this before but cannot remember how?!
Here is my query at the moment;
WITH
SET [Activated] AS '{[CRM DIM Sales Prompt Activity].[Response Desc].&[Not Interested],
[CRM DIM Sales Prompt Activity].[Response Desc].&[Interested],
[CRM DIM Sales Prompt Activity].[Response Desc].&[Cancelled]}'
MEMBER [Measures].[ActivatedCount] AS 'CROSSJOIN([Activated],
[Measures].[CRM Sales Prompt Activity Count])'
SELECT NON EMPTY [CRM DIM Sales Prompt Activity].[Name].MEMBERS ON ROWS,
NON EMPTY ([Activated],
[Measures].[CRM Sales Prompt Activity Count])
ON COLUMNS
FROM [CRMCube]
May 29, 2009 at 7:53 am
You're using a named set rather than a tuple. If you use a calc member you created, it should work. Note I've used a SUM assuming that summing the counts is correct for the rollup. You may have to use an alternate aggregation function.
WITH
SET [Activated] AS '{[CRM DIM Sales Prompt Activity].[Response Desc].&[Not Interested],
[CRM DIM Sales Prompt Activity].[Response Desc].&[Interested],
[CRM DIM Sales Prompt Activity].[Response Desc].&[Cancelled]}'
MEMBER [Measures].[ActivatedCount] AS 'SUM([Activated],
[Measures].[CRM Sales Prompt Activity Count])'
SELECT NON EMPTY [CRM DIM Sales Prompt Activity].[Name].MEMBERS ON ROWS,
[Measures].[ActivatedCount] ON COLUMNS
FROM [CRMCube]
Steve.
May 31, 2009 at 2:26 am
Thanks Steve, I'll give this a go tomorrow.
I have to product several reports which have NoOfAppointments, CancelledAppointments, SuccesfulAppointments etc. By Sales Person and by Area.
At the moment the only fact I have is [Measures].[CRM Sales Prompt Activity Count] the I have to go about using the Reponse Desc field to seperate the activty out each time.
My feeling is that these fields; NoOfAppointments, CancelledAppointments, SuccesfulAppointments should be defined as facts within the cube? Therefore I can bring them straight into the query and slice on Area and Sales Person etc.
What are your thoughts?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply