May 26, 2011 at 4:46 pm
Hello Forum,
Its happy friday here but I am stuck with this problem.
I am generating a SSRS report using SSAS Revenue cube, but the problem is I have a dimension called office with attributes office code, office name.
Can you tell me how can write the mdx statement to generate a calcuated member (Office_Segment) in the SSRS query designer. Basicaly i need to search the office name for string " ECA " and categorise it into ECA Office Segment. Hope I am making sense.
I have tried something like below, it is not working. Even thought the office name does not have the search string its is showing up in the category. And also One more requirement of the report is that the office segment should be a parameter, they can select to see the reveune for all three segments or just one.
WITH MEMBER [Measures].OfficeSegment AS CASE [OFFICE DIM].[OFFICE NAME].Currentmenber WHEN instr([OFFICE DIM].[OFFICE NAME].Properties( 'MemberCaption' ),' ECA ')>0 THEN 'ECA' WHEN instr([OFFICE DIM].[OFFICE NAME].Properties( 'MemberCaption' ),' ECD ')>0 THEN 'ECD' WHEN instr([OFFICE DIM].[OFFICE NAME].Properties( 'Member_Caption' ),' CCD ')>0 THEN 'CCD' ELSE 'Other' END SELECT [Measures].OfficeSegment on 0 , NON EMPTY [OFFICE DIM].[OFFICE NAME].Members ON 1 FROM [Revenue DM]
Any ideas guys, all your suggestions are highly appreciated.
June 1, 2011 at 10:38 am
Query scoped named sets may be what you are after:
http://msdn.microsoft.com/en-us/library/ms145487.aspx
gsc_dba
June 1, 2011 at 5:47 pm
Hi,
Thanks for the reply. I have ran the below Mdx statement as you suggested, but its giving and "type mismatch" error. Can you pleaes let me know how to go about it.
WITH SET [OfficeSegment] AS
CASE
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECA ") <> 0))' THEN 'ECA'
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECD ") <> 0))' THEN 'ECD'
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " CCD ") <> 0))' THEN 'CCD'
ELSE 'Other'
END
SELECT
{[Measures].[MINUTES]} ON COLUMNS,
[OfficeSegment] ON ROWS
FROM [Revenue DM]
-Shilpa.
June 2, 2011 at 3:04 am
The example doesnt use a case statement:
WITH SET [ChardonnayChablis] AS
'Filter([Product].Members, (InStr(1, [Product].CurrentMember.Name, "chardonnay") <> 0) OR (InStr(1, [Product].CurrentMember.Name, "chablis") <> 0))'
SELECT
[ChardonnayChablis] ON COLUMNS,
{Measures.[Unit Sales]} ON ROWS
FROM Sales
Have you tried to retrieve just one at a time without case statement...?
gsc_dba
June 2, 2011 at 11:43 pm
Sorry, How do I group the office name into three different segments withouth the case statement?
June 3, 2011 at 4:39 am
gsc_dba
June 3, 2011 at 8:35 am
Or UNION the three SETS:
WITH SET [OfficeSegmentECA] AS
Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECA ") <> 0))
SET [OfficeSegmentECD] as
Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECD ") <> 0))
SET [OfficeSegmentCCD] as
Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " CCD ") <> 0))
UNION( OfficeSegmentECA, OfficeSegmentECD, OfficeSegmentCCD )
gsc_dba
June 3, 2011 at 9:08 am
sharonrao123 (6/1/2011)
Hi,Thanks for the reply. I have ran the below Mdx statement as you suggested, but its giving and "type mismatch" error. Can you pleaes let me know how to go about it.
WITH SET [OfficeSegment] AS
CASE
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECA ") <> 0))' THEN 'ECA'
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " ECD ") <> 0))' THEN 'ECD'
WHEN 'Filter([OFFICE NAME].Members, (InStr(1, [OFFICE NAME].CurrentMember.Name, " CCD ") <> 0))' THEN 'CCD'
ELSE 'Other'
END
SELECT
{[Measures].[MINUTES]} ON COLUMNS,
[OfficeSegment] ON ROWS
FROM [Revenue DM]
-Shilpa.
This syntax is wrong
It should read:
WITH
SET [OfficeSegment] AS
CASE
WHEN
Filter
(
[OFFICE NAME].MEMBERS
,
Instr(1,[OFFICE NAME].CurrentMember.Name," ECA ") <> 0
)
THEN 'ECA'
WHEN
Filter
(
[OFFICE NAME].MEMBERS
,
Instr(1,[OFFICE NAME].CurrentMember.Name," ECD ") <> 0
)
THEN 'ECD'
WHEN
Filter
(
[OFFICE NAME].MEMBERS
,
Instr(1,[OFFICE NAME].CurrentMember.Name," CCD ") <> 0
)
THEN 'CCD'
ELSE 'Other'
END
SELECT
{[Measures].[MINUTES]} ON COLUMNS
,[OfficeSegment] ON ROWS
FROM [Revenue DM];
gsc_dba
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply