Drilling Down On Dimensions and Measures

Example: Drilling Down on Dimensions in a Report

This report you are about to run uses data from a hierarchy that contains three dimensions, each of which has three elements. The report is sorted by the specified field from each dimension. The following table outlines three dimensions, Time Period, Location, and Product, each containing three elements.

The report will show data at different levels in each dimension. Quarter is down one level in its dimension, Store Name is at the lowest level in its dimension, Product Type is the top level in its dimension. This determines how much farther you can drill down within each dimension. If you drill down on a value of Quarter, the report shows information broken down by Month within that Quarter. The Quarter column itself will no longer appear.

1. Run OLAPREP2.

   In this quarterly report, drill-down hyperlinks are active for both dimensions and measures.

   
   

   

   
   
2. Click Q1 in the quarterly report to see a monthly report, as shown in the following image.
   
   

   

   
   

   Since Month is the bottom level in its dimension, if you drill down on a month value, you will no longer see the month column. However, you will see the data that relates to the selected month in subsequent columns.

3. Click 01 in the MONTH column to see details for January.

   As shown in the following image, the January report displays Product Type, Quantity, and Line Cost Of Goods Sold for each store.

   
   

   

   
   

   Next, see what happens when you drill down in the Location dimension (in this case, on a value of Store Name in the second column of the report). When you drill down on a dimension column other than the first, the output is affected to the right and left of that column.

4. Click Back in your browser to return to the monthly report.
   
   

   

   
   
5. Click AV VideoTown in the second column.

   Since Store Name is the lowest level in its dimension, the Store Name column no longer appears, nor does the Time Period column to its left. Nevertheless, both the Store Name (AV VideoTown) and the current time period (January) set the context for the information you see, which now consists of types of Product Type, Quantity, and Line Cost of Goods Sold for AV VideoTown in January, as shown in the following image.

   
   

   

   
   

Example: Drill Down on Measures in Reports

By drilling down on a measure, you expose the next level of detailed information associated with that measure for each displayed dimension in the hierarchy. In other words, when you drill down on a measure, the current dimension is used as a limiting criterion. The rest of the hierarchy is then expanded based on that limitation.

Remember that a measure contains quantitative information about fields in each dimension.

In this example, Quantity and Line Cost of Goods Sold provide data about products at particular stores during particular time periods.

1. Run OLAPREP2.

   Notice that quantity of sales for all digital products at AV VideoTown in the first quarter of the year is 22,206. You want to find out how much each digital product contributed to the total quantity.

2. Click 22,206 under Quantity.

   As shown in the following image, the report now displays total quantity for digital products sold at AV VideoTown broken out by MONTH, Product Category, and Product Name. Notice that Store Name no longer appears. Since it is the lowest level of the Location dimension, there is no lower level of detail.

   
   

   

   
   

   Since all relevant information is now visible, no further drill downs are possible and the measure is no longer represented as a hyperlink.

   Next, verify this behavior at another level in the hierarchy.

3. Click Back in your browser to return to the original report.
4. Click Q1 to see the monthly breakdown for that quarter.
5. Click AV VideoTown.

   You are now looking at Product Type sold, Quantity sold, and Line Cost Of Goods Sold at AV VideoTown.

   
   

   

   
   
6. Drill down on 1,426 under Quantity.

   Note: When you drill down on a measure value, results may differ depending on the combination of sort fields in the report. The examples that follow show several variations.

   Product Type: Digital serves as the limiting criterion. Therefore, the expanded hierarchy shows the next level of detail for each digital product, as shown in the following image.

   
   

   

   
   

   This level is composed of digital product categories and the names of the products in each category. The report displays the detailed data for each element in the Product Type dimension (in this case, the product categories and product names that compose the quantity figure of 1,426). The total Quantity and the Line Cost Of Goods Sold are now broken down by product.

Example: Drilling Down on a Measure in a Report with ACROSS Fields

When you drill down on a measure in a report with at least one dimension Across field and no By fields, all Across fields are removed from the report and all of the dimension elements under the removed Across fields become By fields from left to right in the resulting report. This convention ensures that the maximum number of Across values supported by WebFOCUS is not exceeded.

The values that appear for the new By fields are controlled by internally generated selection criteria. The measure values in the resulting report depend on the values of the new By fields.

1. Run OLAPREP3.
   
   

   

   
   

   In the report, RISK_CLASS and Continent are dimension Across fields on which you can drill down.

2. Click the Balance value 671,290 under RISK_CLASS Low and Continent AMERICAS.

   The report now looks like the following image.

   
   

   

   
   

   In the new report, the RISK_CLASS and Continent fields are removed based on two internally generated criteria: IF RISK_CLASS EQ 'Low' and IF Continent EQ 'AMERICAS'.

   The only dimension element under RISK_CLASS is Risk_Factor. The dimension elements under Continent are Region and Country. These become By fields in the new report, from left to right. The data displayed for the measures in the resulting report are those that satisfy the values in the current By fields.

Example: Drill Down on a Measure When BY/ACROSS Fields Are Under the Same Dimension

When you drill down on a measure in a report with at least one By and one Across dimension field under the same root dimension, both the By and Across fields are hidden and the subordinate elements in the same dimension become By fields in the new report. In effect, the report is filtered based on the values of the dimensions. As a result, the sorting controlled by both hidden and visible dimensions remains in effect.

1. Run OLAPREP4.
   
   

   

   
   

   In the report, Continent is a By field and Region is an Across field. Both are in the Geographic Area dimension.

2. Click the CANADA_DOLLAR value of 56,280,934 in the Continent row for AMERICAS under the Region CENTRAL AMERICA.

   The report now looks like the following image.

   
   

   

   
   

   In the new report, data is filtered based on the internally generated criteria: IF Continent EQ 'AMERICAS' and IF REGION EQ 'CENTRAL AMERICA.' (Continent and Region are no longer visible.)

   REGION is replaced by the last element in the Geographic Area dimension, Country, which becomes the controlling By field in the report. The data displayed for the measures are those that satisfy the values in the current By field.

Example: Drill Down on a Measure When BY/ACROSS Fields Are Under Different Root Dimensions

When you drill down on a measure in a report with at least one By and one Across dimension field from different root dimensions, the By fields are broken down to their last dimension level, then the Across fields are broken down.

The original By and Across fields are removed. The dimension elements under the removed By fields become the first set of By fields from left to right. The dimension elements under the removed Across fields follow the first set of By fields from left to right.

1. Run OLAPREP5.
   
   

   

   
   

   In the report, Continent is a By field from the Geographic Area dimension and RISK_CLASS is an Across field from the Risk dimension.

2. Click the CANADA_DOLLAR value of 67,021,020 in the Continent row for EUROPE under the RISK_CLASS High.

   The report looks like the following image.

   
   

   

   
   

   In the new report, the Continent and RISK_CLASS fields are removed based on the internally generated criteria: IF CONTINENT EQ 'EUROPE' and IF RISK_CLASS EQ 'High'.

   The By field (Continent) is broken down to its last dimension element. Then, the Across field (RISK_CLASS) is broken down to its last dimension level. The resulting By fields in the report, from left to right, are Region, Country, and Risk Factor. The data displayed for the measures satisfy the values in the current By fields.