MDX

Question

MDX

Oliver Bradonjic

Valued Member

Points: 62
More actions
January 6, 2009 at 3:32 am

#203622

Hi everyone,
How can J write MDX script to get this set. This example is from Adwenture Works AS 2005 projects, Employees hierarchy.
Level 02 Level 03Level 04Level 05
Ken J. SánchezBrian S. WelckerAmy E. AlbertsJae B. Pak
Ken J. SánchezBrian S. WelckerAmy E. AlbertsRachel B. Valdez
Ken J. SánchezBrian S. WelckerAmy E. AlbertsRanjit R. Varkey Chudukatil
Ken J. SánchezBrian S. WelckerStephen Y. JiangDavid R. Campbell
Ken J. SánchezBrian S. WelckerStephen Y. JiangGarrett R. Vargas
Ken J. SánchezBrian S. WelckerStephen Y. JiangJillian Carson
Ken J. SánchezBrian S. WelckerStephen Y. JiangJosé Edvaldo. Saraiva
Ken J. SánchezBrian S. WelckerStephen Y. JiangLinda C. Mitchell
Ken J. SánchezBrian S. WelckerStephen Y. JiangMichael G. Blythe
Ken J. SánchezBrian S. WelckerStephen Y. JiangPamela O. Ansman-Wolfe
Ken J. SánchezBrian S. WelckerStephen Y. JiangShu K. Ito
Ken J. SánchezBrian S. WelckerStephen Y. JiangTete A. Mensa-Annan
Ken J. SánchezBrian S. WelckerStephen Y. JiangTsvi Michael. Reiter
Ken J. SánchezBrian S. WelckerSyed E. AbbasLynn N. Tsoflias

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Woodard Old Hand Points: 327 More actions · Answer 1

We don't have the adventureworks database loaded at work but I ran this against one of our parent-child hierarchies and it returned values similar to what you're looking for.

WITH

MEMBER [Measures].[Level 02] AS '[Dimension].[Hierarchy].currentmember.parent.parent.parent.name'

MEMBER [Measures].[Level 03] AS '[Dimension].[Hierarchy].currentmember.parent.parent.name'

MEMBER [Measures].[Level 04] AS '[Dimension].[Hierarchy].currentmember.parent.name'

MEMBER [Measures].[Level 05] AS '[Dimension].[Hierarchy].currentmember.name'

SELECT {[Measures].[Level 02], [Measures].[Level 03], [Measures].[Level 04], [Measures].[Level 05] } ON COLUMNS,

[Dimension].[Hierarchy].[Level 05].MEMBERS ON ROWS

FROM [DATABASE]

TangoVictor SSChampion Points: 10975 More actions · Answer 2

since I don't know MDX by memory, what I do is set up a pivot in Excel and make it look the way I want. Then, using a plug in, described below, right click on the pivot table and select OLAP Pivot Table Extensions...

Select the MDX tab and view the MDX for the pivot table currently displayed.

Getting the plug in:

First download OLAP Pivot Table Extensions (OPTE)

http://www.codeplex.com/OlapPivotTableExtend

You may also need to install Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime, the OPTE install will let you know if you need this.

http://www.microsoft.com/downloads/details.aspx?FamilyId=8315654B-A5AE-4108-B7FC-186402563F2B&displaylang=en#filelist

Hope this helps.

Oliver Bradonjic Valued Member Points: 62 More actions · Answer 3

Thank you all for your replay.

The scrip works. But is it posible to write script if you don't know how much level's self join hierarchy have.

I install OLAP Pivot Table Extensions (OPTE) on XP SP2, Office 2007 SP1, VS 2005 pro SP1 and it not work. The error is Old format ...

Frank Woodard Old Hand Points: 327 More actions · Answer 4

Perhaps something like this then?

WITH

MEMBER [Measures].[ParameterCaption] AS ''space([Dimension].[Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL) + [Dimension].[Hierarchy].CURRENTMEMBER.MEMBER_CAPTION''

MEMBER [Measures].[ParameterValue] AS ''[Dimension].[Hierarchy].CURRENTMEMBER.UNIQUENAME''

MEMBER [Measures].[ParameterLevel] AS ''[Dimension].[Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL''

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

descendants({[Dimension].[Hierarchy]},0,self_and_after) ON ROWS

FROM [Database]

Oliver Bradonjic Valued Member Points: 62 More actions · Answer 5

My problem is not to return set with all members in one column, I wont to return set with one column for every level in hierarchy. The Employ hierarchy is self join hierarchy, and I don't know how much level hierarchy have in a moment of execution my report server reports. :crazy: