Grouping level members

#1

I have a dimension looks like this:

structure

Quote:

-->division

--> region

--> office

and a measure 'amount'.

I need the total amount for regions (e.g.

REGION AMOUNT
region1 1000
region2 2000
region3 500
region4 4000

)

When I try

SELECT {[Measures].[Amount]} ON COLUMNS,
{descendants([Structure].[Total Structure],[Structure].[Region], LEAVES )} ON Rows FROM TotalsAmount

I receive somethings like

REGION AMOUNT
region1 500
region2 200
region4 2000
region1 250
region2 1000
region3 500
region4 1000
region1 250
region2 800
region4 1000

Since I'm new in Olap I don't know how to group the regions to have the total amounts for regions (like a SQL group by). Is there a MDX instruction that do that?

Any help is appreciated
Antonio

#2

If there is not a one-to-many relationship between a division and a region would it not be better to seperate them out into 2 dimensions?

Regards
Jamie

"Antonio Rome" wrote:

Quote:

Issue resolved:
thank you Jamie for you reply but the problem was a little bit more complex, since regions were repeated for each division. I've created a virtual dimension with unique region member from structure dimension, and changed the MDX query in this way:

SELECT {[Measures].[Amount]} ON COLUMNS,
{descendants([Region].[All Region],[Region].[Region name], LEAVES )} ON Rows FROM TotalsAmount

It works fine for me. Thank you again

Antonio

"Jamie" wrote:

If I've understood your problem correctly the following should do it:

SELECT {[Measures].[Amount]} ON COLUMNS,
{[Structure].[Region].members} ON Rows
FROM TotalsAmount

"Antonio Rome" wrote:

I have a dimension looks like this:

structure
|
|-->division
|
|--> region
|
|--> office

and a measure 'amount'.

I need the total amount for regions (e.g.

REGION AMOUNT
region1 1000
region2 2000
region3 500
region4 4000

)

When I try

SELECT {[Measures].[Amount]} ON COLUMNS,
{descendants([Structure].[Total Structure],[Structure].[Region], LEAVES )} ON Rows FROM TotalsAmount

I receive somethings like

REGION AMOUNT
region1 500
region2 200
region4 2000
region1 250
region2 1000
region3 500
region4 1000
region1 250
region2 800
region4 1000

Since I'm new in Olap I don't know how to group the regions to have the total amounts for regions (like a SQL group by). Is there a MDX instruction that do that?

Any help is appreciated
Antonio

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Grouping level members

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Grouping level members - 07-18-2004 , 02:28 PM

RE: Grouping level members - 07-20-2004 , 03:21 AM