SUPPORT / SAMPLES & SAS NOTES
 

Support

Usage Note 37628: Understanding how filtering affects OLAP navigation, member availability, and totaling in SAS® Web Report Studio

DetailsAboutRate It

Overview

In SAS Web Report Studio, you can create OLAP reports by selecting a SAS® Information Map that uses a cube as its data source. The information map determines what subset of data from the cube is available to the entire report. Data that is available at the report level is determined by the following:

  • which measures and hierarchies from the cube are included in the information map.
  • whether a filter (called a "section filter") that further subsets data from the cube is applied to the information map.

Within the report, tables and graphs also can be filtered. In this case, the filter (called a "child filter") subsets the data that is available to the entire report. The data available to the entire report might be all data available at the information map level, or it might be data that is available after a section filter is applied at the information map level.

Figure 1 illustrates these "nested" relationships, assuming that a section filter is applied at the information map level. (You can have a child filter without a section filter.)

Data availability

Figure 1

These relationships affect navigation, member availability, and total calculations in the report, as explained in this three-section usage note. The information map used for the examples is based on a cube with two hierarchies and one measure. The examples focus on how filtering affects results from the GEOGRAPHIC hierarchy, which has three levels: REGION, STATE, and CITY.

Section I: How filtering affects navigation

The table in Figure 2 has no section filters or child filters applied. Access to the cube data is determined by the measures and hierarchies selected for the information map. For the GEOGRAPHIC hierarchy, this means that all regions, states, and cities are included in the results. You can navigate up and down all available levels.

Table has no filters applied

Figure 2

When a child filter for AR, FL, CA, and TX is applied to the table, you see the results in Figure 3. These results are a subset of the data that is available to the entire report.

Only SOUTHEAST and SOUTHWEST are displayed

Figure 3

If you drill into AR, a drill path appears at the top of the table. The drill path enables you to navigate back up to the region level, even though only two of the regions are available. (SOUTHEAST and SOUTHWEST are the only two regions associated with the four filtered states.)

The drill path enables you to navigate back up to higher levels

Figure 4

In Figure 5, the child filter has been removed and replaced by a section filter for the same four states (AR, FL, CA, and TX). In this case, the available data is restricted at the information map level, which means that only data for AR, FL, CA, and TX is available to the entire report.

A section filter restricts results to Arkansas, Florida, California, and Texas

Figure 5

You can drill or expand into one of the states, as shown in Figure 6, but the drill path does not enable you to navigate up to the region level because region data is no longer available to the entire report.

The drill path does not enable you to navigate up to the region level

Figure 6

Tip: At this point, with the section filter applied, your child filter selections would be limited to AR, FL, CA, and TX.

Section II: How filtering affects member availability

The table in Figure 7 has no section filters or child filters applied. Access to the cube data is determined by the measures and hierarchies selected for the information map. For the GEOGRAPHIC hierarchy, this means that all regions, states, and cities are included in the results.

Table has no filters applied

Figure 7

Because there is no section filter applied, all levels are available to choose from when creating a child filter on the table. Figure 8 shows the Filter and Rank dialog box.

Filter and Rank dialog box

Figure 8

However, the selection of available values changes after you apply a section filter at the information map level. In Figure 8, the section filter restricts results so that only data from the SOUTHEAST and SOUTHWEST is available to the entire report.

A section filter subsets results to include only the SOUTHEAST and SOUTHWEST regions

Figure 9

With the section filter applied, your child filter selections are limited to SOUTHEAST and SOUTHWEST, as shown in Figure 10.

Filter and Rank dialog box

Figure 10

Section III: How filtering affects total calculations

The table in Figure 11 has no section filters or child filters applied. Access to the cube data is determined by the measures and hierarchies selected for the information map. For the GEOGRAPHIC hierarchy, this means that all regions, states, and cities are included in the results.

Table has no filters applied

Figure 11

When a child filter for AR, FL, CA, and TX is applied to the table, you see the results in Figure 12. These results are a subset of the data available to the entire report.

image label

Figure 12

If you add visual totals and subtotals to the table, you see the results in Figure 13 because visual calculations are based on the values that are currently displayed in the table.

Visual totals based on values displayed in the table

Figure 13

However, if you elect to see parent totals instead of visual totals, you see the results in Figure 14. The subtotal values (identified by All GEOGRAPHIC: SOUTHEAST and All GEOGRAPHIC: SOUTHWEST) are the same as the visual totals shown previously, but the total values (All GEOGRAPHIC) are based on the aggregated values for all states in all regions. This is accurate because parent totals are based on all of the values that are available to the entire report.

Parent totals are based on all of the values that are available at the information map level

Figure 14

In Figure 15, the child filter has been removed and replaced by a section filter that restricts results to the same four states: AR, FL, CA, and TX.

A section filter restricts results to Arkansas, Florida, California, and Texas

Figure 15

If you add visual totals and subtotals to Figure 15, you see results that are similar to Figure 13, because the calculations are based only on the values that are currently displayed in the table.

Visual totals based on values displayed in the table

Figure 16

In Figure 17, visual totals have been replaced by parent totals. However, unlike Figure 14, these parent total values are the same as the visual total values. This is accurate because the results have been restricted at the information map level so that only values for AR, FL, CA, and TX are available to the entire report.

Parent totals are based on all of the values that are available at the information map level

Figure 17



Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Web Report StudioMicrosoft Windows XP Professional4.29.2 TS2M0
Microsoft Windows Server 2003 Datacenter Edition4.29.2 TS2M0
Microsoft® Windows® for x644.29.2 TS2M0
z/OS4.29.2 TS2M0
Microsoft Windows Server 2003 Standard Edition4.29.2 TS2M0
Microsoft Windows Server 2003 Enterprise Edition4.29.2 TS2M0
Windows Vista4.29.2 TS2M0
64-bit Enabled AIX4.29.2 TS2M0
64-bit Enabled Solaris4.29.2 TS2M0
HP-UX IPF4.29.2 TS2M0
Linux for x644.29.2 TS2M0
Solaris for x644.29.2 TS2M0
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.