Drill-Down Examples

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

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:

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:

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:

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: