Grouping Dimensions in MDX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Grouping Dimensions in MDX

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-15 : 14:24:07

Could anyone tell me how I would go about doing this.

I currently have a dimension with 4 members, I need to break it out into 2 seperate groupings along the columns in the result set.

My attempt so far...

With Set [A] as 
	'{[Dim Network].[Network Name].[Ntw1]}'
Set [B] as 	
     '{[Dim Network].[Network Name].Members - [Dim Network].[Network Name].[Ntw1]}'
Select 
{
	CrossJoin(
		Union([A], [B])
		,[Product].[Program].Children - [Product].[Program].[NOT SPECIFIED]
		,[Product].[Rating Category].Children
		,Union(
			[Measures].[PMPM]
			,[Measures].[Cost / Event]
			,[Measures].[Days / 1000]
			,[Measures].[Events / 1000]
			,[Measures].[ALOS]
			 )
			)	
} on 0,
{
	NonEmptyCrossJoin([Event].[Level1].Children,[Event].[Finance Level3].Children)
} on 1
From [DM]
Where
(
	{[Dim Service Date From].[Cal Year].[2009]}
	,{[Dim Claim Flag].[Is Resubmitted].[N]}
	,{[Dim Claim Status].[Claim Status].[PAID]} 
)

What I am looking for is the members to group up, so the top row actually says "A" ....data "B" ....data

I do not want to see the individual members.

Thanks all.

Edit:

This would be the equivilent to a case statement.

Case when [Network Name] = 'Ntw1' then 'A'
Else 'B' end as Network

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources