How to write drilldown MDX query

[Polly]

Dear all,

I have a cube called "Sales" and a dimension called "Product". "Product" dimension contains the following levels:

Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.

Using the following MDX query, I can drill down to see the sub total of each level.

select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on rows
from Sales

I would like to know whether there is a easy way to write drill down statement coz I may need to drill to the thrid, fourth or even sixth level. Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement to do the above action?

Thanks!

Best Wishes,
Polly

[Andrej Hudoklin]

For example if you'd like to show all level and all members you have

[Product].allmembers


HTH,
Andrej

Post by Polly
Dear all,
I have a cube called "Sales" and a dimension called "Product". "Product"
Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.
Using the following MDX query, I can drill down to see the sub total of each level.
select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on rows
from Sales
I would like to know whether there is a easy way to write drill down

...

statement coz I may need to drill to the thrid, fourth or even sixth level.
Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement
to do the above action?

Post by Polly
Thanks!
Best Wishes,
Polly

[Polly]

Sometimes, I need to see all level and all members at specific level. Then, how to write the MDX query? Thanks!

Polly

Post by Andrej Hudoklin
For example if you'd like to show all level and all members you have
[Product].allmembers
HTH,
Andrej

Post by Polly
Dear all,
I have a cube called "Sales" and a dimension called "Product". "Product"
Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.
Using the following MDX query, I can drill down to see the sub total of

each level.

Post by Polly
select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on

rows

Post by Polly
from Sales
I would like to know whether there is a easy way to write drill down

statement coz I may need to drill to the thrid, fourth or even sixth level.
Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement
to do the above action?

Post by Polly
Thanks!
Best Wishes,
Polly

...

[Polly]

Sometimes, I have to show all levels and all members at specific level. Then, how to write this MDX query?

Thanks!

Polly

Post by Andrej Hudoklin
For example if you'd like to show all level and all members you have
[Product].allmembers
HTH,
Andrej

Post by Polly
Dear all,
I have a cube called "Sales" and a dimension called "Product". "Product"
Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.
Using the following MDX query, I can drill down to see the sub total of

each level.

Post by Polly
select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on

rows

Post by Polly
from Sales
I would like to know whether there is a easy way to write drill down

statement coz I may need to drill to the thrid, fourth or even sixth level.
Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement
to do the above action?

Post by Polly
Thanks!
Best Wishes,
Polly

...

[Andrej Hudoklin]

So, if you'd like to show members of one level then you have

[Product].[Product Family].members


HTH,
Andrej

Post by Polly
Sometimes, I have to show all levels and all members at specific level.

Then, how to write this MDX query?

Post by Polly
Thanks!
Polly

Post by Andrej Hudoklin
For example if you'd like to show all level and all members you have
[Product].allmembers
HTH,
Andrej

Post by Polly
Dear all,
I have a cube called "Sales" and a dimension called "Product". "Product"
Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.
Using the following MDX query, I can drill down to see the sub total of

each level.

Post by Polly
select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on

rows

Post by Polly
from Sales
I would like to know whether there is a easy way to write drill down

statement coz I may need to drill to the thrid, fourth or even sixth level.
Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement
to do the above action?

Post by Polly
Thanks!
Best Wishes,
Polly

...

[Mark Job]

Well, if you want it ALL, you could use a recursive function. The following
works on FoodMart 2000

select {[Measures].[Store Sales]} on columns,
DrillDownMember([Product].[Product Family].members,
Descendants([Product].[Product Family].members), RECURSIVE) ON ROWS
from Sales

"To iterate is human; to recurse, divine"

Post by Polly
Dear all,
I have a cube called "Sales" and a dimension called "Product". "Product"
Level 1: Product Family
Level 2: Product Department
Level 3: Product Category
Level 4: Product Subcategory
Level 5: Brand Name
Level 6: Product Name.
Using the following MDX query, I can drill down to see the sub total of each level.
select {[Measures].[Store Sales]} on columns,
DrilldownLevel(DrilldownLevel({[Product].[Product Family].members})) on rows
from Sales
I would like to know whether there is a easy way to write drill down

...

statement coz I may need to drill to the thrid, fourth or even sixth level.
Then, do I use 6 times "DrilldownLevel"? Is there any simple MDX statement
to do the above action?

Post by Polly
Thanks!
Best Wishes,
Polly