Limiting Data

In this section:

How to:

Reference:

An OLAP report is limited to values belonging to the parent categories in the dimensions hierarchy. There are several ways to further limit the data that appears in the report.

From the Selections pane or the Control Panel, you can explicitly limit the data in an OLAP report by selecting dimension values and relational operators (such as =, >, <). For a list of the relational operators, see Selection Criteria Relational Operators.

The Selections pane provides the easiest approach since you can choose both dimension values and relational operators with a few mouse clicks, while the report is fully exposed to view.

Changes made in the Selections pane are implemented immediately in the Control Panel (even if the Control Panel is closed), and changes made in the Control Panel are reflected immediately in the Selections pane.

From the report, you can limit data indirectly by drilling down on measures and dimensions to hone in on a subset of information. For details, see Drilling Down On Dimensions and Measures.
Top of page
x
Reference: Selection Criteria Relational Operators

You can define selection criteria in the Selections pane or Control Panel using several relational operators, which are shown in the following tables. The first column displays the operator and the second column provides a description of the operator.

Operator

Displays Records That...

Equal to - Equal to

Are equal to the criteria you specified.

This is the default operator.

Not Equal to - Not Equal to

Are not equal to the criteria you specified.

Less than or equal to - Less than or equal to

Are less than or equal to the criteria you specified.

Less than - Less than

Are less than, but not equal to, the criteria you specified.

Greater than or equal to - Greater than or equal to

Are greater than or equal to the criteria you specified.

Greater than - Greater than

Are greater than, but not equal to, the criteria you specified.

Contains - Contains

Contain the criteria you specified.

Note: This operator is available only for alphanumeric fields.

Not contain - Not contain

Do not contain the criteria you specified.

Note: This operator is available only for alphanumeric fields.

Note: You can select more than one value using the same relational operator.

The following table lists and describes relational operators that are available for selecting a range of dates.

Operator

Displays Records Where...

Within range - Within range

The value in the indicated date field falls within the specified range.

Note: To use this relational operator, you must select the Range check pane in the Date Selection panel.

Not within range - Not within range

The value in the indicated date field does not fall within the specified range.

Note: To use this relational operator, you must select the Range check pane in the Date Selection panel.
x
Procedure: How to Apply Selection Criteria From the Selections Pane

When the Selections pane is turned on, there is one control (drop-down list) for every dimension in the OLAP hierarchy. Note that the name of the dimension field appears as defined in the Master File, even if an alternate column title has been specified)

To limit data for the dimensions that are included in the report:

  1. Click the arrow to the right of the dimension to open the list of values.
  2. Select one or more values from the list (All is the default value).

    To select multiple values, click the desired values while holding the Ctrl key on the keyboard.

  3. Select a relational operator from the button to the left of the dimension to indicate the basis for selection (equals (=) is the default).

    You can toggle through a list of operators. See Selection Criteria Relational Operators.

  4. Repeat steps 1-3 for each dimension whose values you wish to limit.
  5. Click the Run button on the band below the Selections pane.

Tip: To change or eliminate selection criteria, reopen the values list and choose another value or choose All.
Example: Limiting Continents and Regions From the Selections Pane

The following is an example of limiting continents and regions from the selections pane.

  1. Run the Standard Report OLAPREP7.

    As shown in the following image, the Selections pane above the report, the controls for Continent and Region are set to All to show all values of each dimension. You wish to focus on the data for one continent and one region.

    OLAP

  2. In the Selections pane, click the arrow to the right of CONTINENT and select AMERICAS from the list of values. Use the default operator = to limit the data.
  3. Next, click the arrow to the right of REGION and select NORTH AMERICA. Once again, accept the default operator =.
  4. Click the Run button on the band below the Selections pane.

    The output is now limited to data for the selected continent and region, as shown in the following image.

    OLAP

Top of page
x
Procedure: How to Apply Selection Criteria From the Control Panel
  1. Open the Control Panel.
  2. Click the Selection Criteria button at the bottom right of the window.

    The Selection Criteria pane opens.

  3. In the Dimensions pane above the Selection Criteria pane, expand a dimension and click Values.

    A secondary window opens. Select one or more values (press the Ctrl key to multi-select).

  4. Click OK to return to the Selection Criteria pane, where the selected values appear in the drop-down lists.
    • If a Developer has applied selection criteria to the Reporting Object from which you create an OLAP report, you only see the selected acceptable values of the field.
    • If no selection criteria have been applied, you see all the values of the field in the drop-down lists.
  5. In the Selection Criteria pane, click a relational operator next to the dimension to specify the relationship that you want to base selection on. For example, =, >, or <. For a complete list, see Selection Criteria Relational Operators.
  6. Repeat the process for other dimensions whose values you wish to limit.
  7. Click Run to execute your report.
Example: Limiting Continents and Countries From the Control Panel

The following is an example of limiting continents and countries from the Control Panel.

Tip: If you have access to the Selections pane, it provides the quickest way to limit data. For an illustration, see Limiting Continents and Regions From the Selections Pane.

  1. Run the Standard Report OLAPREP8.

    The report shows data for continents and countries. You want to restrict the information to the Countries ARGENTINA and BRAZIL in the Continent AMERICAS.

  2. Click the square icon next to Continent to open the Control Panel, as shown in the following image.

    OLAP

  3. Click the Selection Criteria button at the bottom right to open the Selection Criteria pane.
  4. In the Dimensions pane above the Selection Criteria pane, expand the Geographic Area dimension and click Values under Country.

    A secondary window lists the acceptable values.

  5. In this window, choose ARGENTINA and BRAZIL, as shown in the following image, (hold down the Ctrl key to multi-select values).

    WebFOCUS OLAP Control Panel

  6. Click OK to return to the Selection Criteria pane.
  7. Repeat step 3, but click Values under Continent and choose AMERICAS, then click OK.

    The selected values now appear in the drop-down lists in the Selection Criteria pane, as shown in the following image.

    WebFOCUS OLAP

  8. Verify that you want to use the default operator =, then click the Run button at the bottom of the Control Panel.

    The new report displays the data by Continent, AMERICAS followed by Country, as shown in the following image.

    OLAP

Top of page
x
Procedure: How to Change Selection Criteria From the Control Panel

Tip: If you have access to the Selections pane, it provides the easiest way to adjust or remove selection criteria. See How to Apply Selection Criteria From the Selections Pane.

From the Selections Criteria pane in the Control Panel:

  1. Click the Select button next to the dimension value you wish to modify.

    A secondary window opens.

    To change a value, type the new value in the text pane or select one or more values from the list. (The value you type must be in the same case as the value in the data source.)

    You can input only one value in the text pane. If you select more than one value from the list, only the first value appears. However, all values appear in your report.

    To deselect a value, hold down the Ctrl key while clicking the value.

  2. Click OK to return to the Selection Criteria pane where you can verify the revised value and/or change the relational operator if required.
  3. Click OK again to confirm your choice and return to the main Control Panel window.
  4. Click Run to execute your report.
Top of page
x
Procedure: How to Remove Selection Criteria From the Control Panel

Tip: If you have access to the Selections pane, it provides the easiest way to adjust or remove selection criteria. See How to Apply Selection Criteria From the Selections Pane.

From the Selections Criteria pane in the Control Panel:

  1. Select the criterion you want to remove.
  2. Click the Delete Delete button.

    The selection category is removed from the list.

  3. Click Run to execute your report with all values.
Top of page
x
Applying Selection Criteria to Date Elements

How to:

Reference:

You can apply selection criteria to date elements just as you apply them to other types of elements. The results are limited by the date(s) you select. For example, you can select to view data associated with a particular date or to exclude data from the specified date.

Note: Like other dimension elements, date fields must have been defined in the Master File by a Managed Reporting developer. The Master File specifies the date formats available for selection criteria.

In the Control Panel, you can choose the selection criteria from a Date selection pane that contains the appropriate controls for the date format.

You can also select a range of dates in a designated year by specifying a From and To date. Two relational operators are available for selecting a range of dates:

For more information on supported date formats, see Date Format Limitations. For more information on specifying date formats, see the Describing Data With WebFOCUS Language manual.
x
Procedure: How to Apply Selection Criteria to a Date Field
  1. Open the Control Panel.
  2. Click the Selection Criteria button.

    The Selection Criteria pane opens.

  3. In the Dimensions pane above the Selection Criteria pane, expand the dimension that includes the date field, and click the Values button.

    A secondary window displays controls for the date format of the dimension. For example, if the date format is YYM, only the year and month controls appear. If the format is YYMD, year, month, and day controls appear.

    Note: The date selection pane appears only when a supported date format is provided. See Date Format Limitations.

  4. Specify a date using the spin controls, drop-down lists, or by typing the value.

    If your date format includes edit masking such as Y.M.D, the date appears with forward slashes in the Date selection list pane, the Selection Criteria pane, and the drop-down list at the bottom of the report. However, the date edit mask appears as specified within the body of the report.

  5. Click Add to display the date in the Selections list pane.
  6. Click OK to return to the Selection Criteria pane and verify the selected date.
  7. In the Selection Criteria pane, click a relations button to the left of the date field (for example, =, >, or <) to indicate a basis for record selection.
  8. Optionally, define additional date selection criteria by repeating steps 2-7.
  9. Click Run to execute your report.
Example: Applying Selection Criteria to a Date Field

The following is an example of applying selection criteria to a date file.

  1. Run the Standard Report OLAPREP9.

    As shown in the following images, the multi-page OLAP report shows several years of data about reported problems falling into five categories: incorrect labeling, missing components, physical damage, power failure, remote failure.

    OLAP

    You want to investigate problems reported on June 6, 2001. You can limit data based on a single date from the Control Panel.

    OLAP

    Note: To show the selection of a particular date, a dimension component has been added to the procedure. This dimension places Date Problem Reported in the Time Period dimension hierarchy directly below the root.

  2. Click the OLAP button below the report to open the Control Panel.

    The OLAP button appears at the bottom of this report because the OLAP CONTROL setting was selected. For details, see Setting OLAP Reporting Options.

  3. Click the Selection Criteria button at the bottom of the Control Panel.

    The Selection Criteria pane opens.

  4. In the Dimensions pane above the Selection Criteria pane, expand the TIMEPERIOD hierarchy.
  5. Click Values under Date Problem Reported.

    The pane replaces the Selections Criteria pane, with a drop-down list for each selectable value (Year, Month, and Date) based on the date format of the selected field, as shown in the following image.

    WebFOCUS OLAP Control Panel

  6. Select values. For example:
    1. Change the year to 2010 in the Year pane by using the spin buttons or typing the value.
    2. Select April from the Months drop-down list.
    3. Select 21 from the Days drop-down list.
    4. Click Add to enter these criteria in the input pane.
  7. Click OK to return to the Selection Criteria pane, which now reflects your entries, as shown in the following image.

    WebFOCUS OLAP Control Panel

    The relational operator to the left of the Date pane indicates that your report will contain data only for those rows where date is equal to (=) the values you entered. This default operator is correct for this example.

  8. Click Run to see the problem report for the specified date.

    Your selection criteria are listed beside the OLAP button at the bottom of the report, as shown in the following image.

    OLAP

x
Procedure: How to Apply Selection Criteria to a Date Range
  1. Open the OLAP Control Panel.
  2. Click the Selection Criteria button.

    The Selection Criteria pane opens.

  3. In the Dimensions pane above the Selection Criteria pane, expand the dimension that includes the date field, and click the Values button.

    A secondary window displays controls for the date format of a dimension. For example, if the date format is YYM, only the year and month controls appear. If the format is YYMD, year, month, and day controls appear.

    Note: The Date selection pane appears only when a supported date format is provided. See Date Format Limitations.

  4. Click the Range check pane.

    Inclusive and Exclusive options buttons appear:

    • Choose Inclusive to show the range including the dates specified.
    • Choose Exclusive to show the range excluding the dates specified.

    Note:

    • You can select only one range of dates at a time.
    • You can apply selection criteria to a range of dates only if the date format contains a year. See Date Format Limitations.

    From and To drop-down lists open for all selectable options. By default, the current date appears.

  5. Specify a From date and a To date by using the spin controls and drop-down lists.
  6. Click OK to return to the Selection Criteria pane.
  7. To view both the From and To dates of the range selected, click the down arrow on the drop-down list.
  8. Click a relational operator to the left of the date element in the Selection Criteria pane:
    • Choose Within range Within range operator to display records when the value falls within the specified range.
    • Choose Not within range Not within range operator to display records when the value does not fall within the specified range.
  9. Click Run to execute your report.
Example: Applying Selection Criteria to a Range of Date Fields

The following is an example of applying selection criteria to a range of date fields.

  1. Run the Standard Report OLAPREP9.

    As shown in the following images, your report shows problem information reported over the course of several years.

    OLAP

    The information falls into the following categories: incorrect labeling, missing components, physical damage, power failure, and remote failure.

    OLAP

    You want to restrict the information to problems reported between June 6, 2001 and July 6, 2001. From the Control Panel, you can limit data based on a range of dates.

  2. Click the OLAP button below the report to open the Control Panel.
  3. Click the Selection Criteria button at the bottom right of the Control Panel.

    The Selection Criteria pane opens.

  4. In the Dimensions pane above the Selection Criteria pane, expand the TIMEPERIOD hierarchy.
  5. Click Values under Date Problem Reported.

    The PROBLEM_DATE pane opens over the Selections Criteria pane, with a drop-down list for each selectable value (Year, Month, and Date) based on the date format of the selected field.

  6. Select the Range check pane:
    • Inclusive and Exclusive options buttons appear. To show the range including the dates specified, choose Inclusive (the default).
    • From and To drop-down lists open for all selectable options. By default, the current date appears.
  7. Specify values for the From date. For example:
    1. Change the current year to 2001 by using the spin buttons.
    2. Select June from the Months drop-down list to change the current calendar month.
    3. Select 6 from the Days drop-down list to change the current calendar day.
  8. Specify values for the To date. For example:
    1. Change the current year to 2001 by using the spin buttons.
    2. Select July from the Months drop-down list to change the current calendar month.
    3. Select 6 from the Days drop-down list to change the current calendar day.

      The following image shows the selections.

      WebFOCUS OLAP Control Panel

  9. Click OK to return to the Selection Criteria pane.
    1. To view the range of dates, click the down arrow in the drop-down list, then click OK again.
    2. To report on information within the specified range of dates, accept the default, Within range Within range operator.
  10. Click Run to execute the report, which now only displays problem information from June 6, 2001 to July 6, 2001, as shown in the following image.

    OLAP

    The date element appears at the bottom of the window.

  11. To view the range of dates, click the arrow in the drop-down list.

    OLAP

x
Procedure: How to Add Dates to the Selections List pane
  1. Open the Control Panel.
  2. Click Selection Criteria to open the Selection Criteria pane.
  3. Click the Select button to open the Date selection pane.
  4. Specify the date you want to add by using the spin buttons, drop-down lists, or by typing the value.
  5. Click Add.

    The date appears inside the Selections list pane.

  6. Click OK to return to the Selection Criteria pane.
x
Procedure: How to Delete Dates From the Selections List pane
  1. Open the Control Panel.
  2. Click Selection Criteria to open the Selection Criteria pane.
  3. Click Select to open the Date selection pane.
  4. Select one or more dates that you want to remove from the Selections list pane.
  5. Click Delete to remove the date.
  6. Click OK to return to the Selection Criteria pane.
x
Reference: Date Format Limitations

Note the following limitations when applying selection criteria to date elements:

WebFOCUS