MDX Usage Examples

Drill-Down Examples

The data that is used in these examples is from a company that sells electronics and outdoor and sporting goods equipment.

Example 1

This example drills down on the electronics and outdoor and sporting goods members from the family level.

SELECT
    {[measures].[qty]} on  0,
  drilldownlevel
     (
       {[product].[all product].[electronics],
        [product].[all product].[outdoor & sporting]
       },
        [product].[family]
     ) on 1
FROM sales

Here is the resulting output:

Item	Qty
Electronics	330,977
Auto Electronics	13,862
Computers, Peripherals	78,263
Digital Photography	9,008
Home Audio	38,925
Personal Electronics	31,979
Phones	59,964
Portable Audio	27,645
TV, DVD, Video	47,725
Video Games	23,606
Outdoor & Sporting	304,345
Bikes, Scooters	45,297
Camping, Hiking	63,362
Exercise, Fitness	50,700
Golf	41,467
Outdoor Gear	52,305
Sports Equipment	51,214

Example 2

This example drills down on the electronics and outdoor and sporting goods members to the family level, but it shows only the top two members at each level based on the value of Qty.

SELECT
   {[measures].[qty]} on 0,
  drilldownleveltop
    (
      {[product].[all product].[electronics], 
       [product].[all product].[outdoor & sporting]
      }, 
      2,
      [product].[family],
      [measures].[qty]
    ) on 1
FROM sales

Here is the resulting output:

Item	Qty
Electronics	330,977
Computers, Peripherals	78,263
Phones	59,964
Outdoor & Sporting	304,345
Camping, Hiking	63,362
Outdoor Gear	52,305

Example 3

This example drills down on the electronics and outdoor and sporting goods members to the family level, but it shows only the bottom two members at each level based on the value of Qty.

SELECT
   {[measures].[qty]} on 0,
  drilldownlevelbottom
    (
      {[product].[all product].[electronics], 
       [product].[all product].[outdoor & sporting]
      }, 
      2,
      [product].[family],
      [measures].[qty]
    ) on 1
FROM sales

Here is the resulting output:

Item	Qty
Electronics	330,977
Digital Photography	9,008
Auto Electronics	13,862
Outdoor & Sporting	304,345
Golf	41,467
Bikes, Scooters	45,297

Example 4

This example drills up to the members of the set that are below the category level. It returns only those members that are at the category level or higher.

SELECT
   {[measures].[qty]} on 0,
  drilluplevel
    (
      {[product].[all product].[electronics].[computers, peripherals],
       [product].[all product].[electronics].[tv, dvd, video],
       [product].[all product].[electronics].[video games].[gameplace],
       [product].[all product].[electronics].[video games].[play guy color].[caller],
       [product].[all product].[outdoor & sporting],
       [product].[all product].[outdoor & sporting].[bikes, scooters].[kids' bikes],
       [product].[all product].[outdoor & sporting].[golf].[clubs].[designed],
       [product].[all product].[outdoor & sporting].[sports equipment],
       [product].[all product].[outdoor & sporting].[sports equipment].[baseball]
      }, 
      [product].[category]
    ) on 1
FROM sales

Here is the resulting output:

Item	Qty
Computers, Peripherals	78,263
TV, DVD, Video	47,725
Outdoor & Sporting	304,345
Sports Equipment	51,214

Top of Page