How to write drilldown MDX query

#1

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

#2

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

[Product].allmembers

HTH,
Andrej

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote

Quote:

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?

Quote:

Thanks!

Best Wishes,
Polly

#3

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

Polly

"Andrej Hudoklin" wrote:

Quote:

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

[Product].allmembers

HTH,
Andrej

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message
news:137275B4-4FF3-4174-921E-FB9E9FDDBD33 (AT) microsoft (DOT) com...
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

#4

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

Thanks!

Polly

"Andrej Hudoklin" wrote:

Quote:

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

[Product].allmembers

HTH,
Andrej

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message
news:137275B4-4FF3-4174-921E-FB9E9FDDBD33 (AT) microsoft (DOT) com...
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

#5

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

[Product].[Product Family].members

HTH,
Andrej

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote

Quote:

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

Thanks!

Polly

"Andrej Hudoklin" wrote:

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

[Product].allmembers

HTH,
Andrej

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote in message
news:137275B4-4FF3-4174-921E-FB9E9FDDBD33 (AT) microsoft (DOT) com...
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

#6

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"

"Polly" <Polly (AT) discussions (DOT) microsoft.com> wrote

Quote:

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?

Quote:

Thanks!

Best Wishes,
Polly

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

How to write drilldown MDX query

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

How to write drilldown MDX query - 07-19-2004 , 08:59 PM

Re: How to write drilldown MDX query - 07-20-2004 , 03:21 AM

Re: How to write drilldown MDX query - 07-20-2004 , 03:47 AM

Re: How to write drilldown MDX query - 07-20-2004 , 03:49 AM

Re: How to write drilldown MDX query - 07-20-2004 , 04:24 AM

Re: How to write drilldown MDX query - 07-20-2004 , 06:26 PM