hi,
I need to add a column in existing MDX and its values should be 1,1,1,1, and 2,2,2 like below. Please refer below values which are under column 'measure code'.
Top line income 1
Products (GL) 1
Net Interest Income (NII) 1
Non Interest Revenue (NIR) 1
Products ( Non GL) 1
Net Interest Income (NII) 1
Non Interest Revenue (NIR) 1
WIP (No. of Prospects Logged) 2
WIP (Prospects Total Amount) Exposure 2
WIP (Prospects Total Amount) Deposits 2
Accepted Payouts in Progress (volume) 2
Accepted Payouts in Progress Exposure 2
Approval Rate (%) 2
Rejection Rate (Not taken up) (%) 2
and so on.
How to achieve this in existing query?
MDX:
WITH
MEMBER [Measures].[Measure_Key] AS [ScoreCardMeasures].[ScoreCard].MEMBER_KEY
MEMBER [Measures].[Measure_Group_Key] AS [ScoreCardMeasures].[ScoreCard].PARENT.MEMBER_KEY
MEMBER [Measures].[Structure_Level] AS [CustomerStructure].[Provincial Structure].LEVEL_NUMBER
SET [ReportLevel]
AS FILTER([Report Levels].[Report Level].[Report Level],
CINT([Report Levels].[Report Level].Properties( "Structure Level" )) = CINT([Measures].[Structure_Level]))
MEMBER [Measures].[Sequence] AS [ScoreCardMeasures].[ScoreCard].Properties( "Sequence" )
MEMBER [Measures].[Indent] AS [ScoreCardMeasures].[ScoreCard].Properties( "Font Indent" )
MEMBER [Measures].[Weight] AS [ScoreCardMeasures].[ScoreCard].Properties( "Font Weight" )
MEMBER [Measures].[Header] AS [ScoreCardMeasures].[ScoreCard].Properties( "Values Header" )
MEMBER [Measures].[Header1Caption] AS [ScoreCardMeasures].[ScoreCard].Properties( "Header Name1" )
MEMBER [Measures].[Header2Caption] AS [ScoreCardMeasures].[ScoreCard].Properties( "Header Name2" )
MEMBER [Measures].[Header3Caption] AS [ScoreCardMeasures].[ScoreCard].Properties( "Header Name3" )
MEMBER [Measures].[Actual] AS [Measures].[Measure Value]
MEMBER [Measures].[Target] AS [Measures].[Measure Target]
MEMBER [Measures].[Average] AS [Measures].[Average Value]
SELECT
{
[Measures].[Measure_Key],
[Measures].[Measure_Group_Key],
[Measures].[Structure_Level],
[Measures].[Sequence],
[Measures].[Indent],
[Measures].[Weight],
[Measures].[Header],
[Measures].[Header1Caption],
[Measures].[Header2Caption],
[Measures].[Header3Caption],
[Measures].[Actual],
[Measures].[Target],
[Measures].[Average]
} ON COLUMNS,
{
FILTER(
[ReportLevel] *
ORDER(
DESCENDANTS(STRTOMEMBER(@Financial_Measure),
[ScoreCardMeasures].[ScoreCard].[Measure Code], LEAVES),
[Measures].[Sequence], ASC),
[Measures].[Active Indicator] <> 0)
} ON ROWS
FROM
[ScoreCard]
WHERE
(STRTOMEMBER(@SiteStructure), STRTOMEMBER(@Time))
What I have tried:
I have just started learning MDX and very new to that.