Add column in existing MDX.

See more:

SSAS

MDX

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.