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.

Reference: Selection Criteria Relational Operators

You can define selection criteria in the Selections panel or in the OLAP 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

Icon

Displays Records That...

Is Equal To

Equal to

Are equal to the criteria you specified.

This is the default operator.

Is Not Equal To

Not Equal to

Are not equal to the criteria you specified.

Is Greater Than

Greater than

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

Is Greater Than or Equal To

Greater than or equal to

Are greater than or equal to the criteria you specified.

Is Less Than

Less than

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

Is Less Than or Equal To

Less than or equal to

Are less than or equal to the criteria you specified.

Contains

Contains

Contain the criteria you specified.

Note: This operator is available only for alphanumeric fields.

Does 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 for selecting a range of dates.

Operator

Icon

Displays Records Where...

From (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 box in the Date Selection panel.

Not From (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 box in the Date Selection panel.

Procedure: How to Apply Selection Criteria From the Selections Panel

When the Selections panel 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. Equal (=) 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 Run on the band below the Selections panel.

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 Panel

  1. Run OLAPREP7.
    OLAP

    The Selections panel above the report shows that 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.

  2. In the Selections panel, 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 Run on the band below the Selections panel.

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

    OLAP

Procedure: How to Apply Selection Criteria from the OLAP Control Panel

  1. Open the OLAP 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 multiselect).

  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 OLAP Control Panel

Tip: If you have access to the Selections panel, it provides the quickest way to limit data. See an illustration in How to Apply Selection Criteria From the Selections Panel.

  1. Run 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 OLAP Control Panel.
  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 multiselect values.)
    WebFOCUS OLAP Control Panel
  6. Click Ok to return to the Selection Criteria pane.
  7. In the Dimensions pane, 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 Run at the bottom of the OLAP Control Panel.

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

    OLAP

Procedure: How to Change Selection Criteria from the OLAP Control Panel

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

From the OLAP Control Panel:

  1. Click the Selection Criteria button at the bottom right.

    The Selection Criteria pane opens.

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

    A secondary pane opens.

    1. 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.
    2. To deselect a value: Hold down the Ctrl key while clicking the value.
  3. Click Ok to return to the Selection Criteria pane where you can verify the revised value and/or change the relational operator if required.
  4. Click Ok again to confirm your choice and return to the main OLAP Control Panel window.
  5. Click Run to execute your report.

Procedure: How to Remove Selection Criteria from the OLAP Control Panel

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.

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

From the OLAP Control Panel:

  1. Click the Selection Criteria button at the bottom right.

    The Selection Criteria pane opens.

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

    The selection category is removed from the list.

  4. Click Run to execute your report with all values.

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 dates 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. The Master File specifies the date formats available for selection criteria.

In the OLAP 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 TIBCO WebFOCUS® Language manual.

Procedure: How to Apply Selection Criteria to a Date Field

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

From the OLAP Control Panel:

  1. Click the Selection Criteria button.

    The Selection Criteria pane opens.

  2. In the Dimensions pane above the Selection Criteria pane, expand a dimension that includes a date field, and click Values directly below that field.

    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.

  3. 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.

  4. Click Add to display the date in the Selections list pane.
  5. Click Ok to return to the Selection Criteria pane and verify the selected date.
  6. 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.
  7. Optionally, define additional date selection criteria by repeating steps 2-7.
  8. Click Run to execute your report.

Example: Applying Selection Criteria to a Date Field

  1. Run OLAPREP9.

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

    OLAP

    You want to investigate problems reported on June 6, 2001. You can limit data based on a single date from the OLAP 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 OLAP Control Panel.

    Note: The OLAP button appears at the bottom of this report because the OLAP CONTROL setting was selected.

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

    The Selection Criteria pane opens.

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

    A new pane appears for Date Problem Reported, replacing the Selection Criteria pane. The pane includes a drop-down list for each selectable value (Year, Month, and Date), as shown in the following image.

    WebFOCUS OLAP Control Panel
  6. Select values. For example:
    1. Change the year to 2010 in the Year field by using the spin controls 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

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 Values directly under the desired field.

    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 box.

    Inclusive and Exclusive option 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 the From (within range) Within range operator to display records when the value falls within the specified range.
    • Choose the Not From (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

  1. Run OLAPREP9.

    As shown in the following images, the 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 OLAP Control Panel, you can limit data based on a range of dates.

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

    The Selection Criteria pane opens.

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

    A new pane appears for Date Problem Reported, replacing the Selection Criteria pane. The pane includes a drop-down list for each selectable value (Year, Month, and Date).

  6. Select the Range check box:
    • Inclusive and Exclusive option 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 controls or by typing in the text box.
    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 calendar day.
  8. Specify values for the To date. For example:
    1. Change the current year to 2001 by using the spin controls or by typing in the text box.
    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 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 (the From (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

Procedure: How to Add Dates to the Selections List Pane

From the OLAP Control Panel:

  1. Click Selection Criteria.

    The Selection Criteria pane opens.

  2. In the Dimensions pane above the Selection Criteria pane, expand the dimension that includes the date field, and click Values under the desired field.
  3. Specify the date you want to add by using the spin controls, drop-down lists, or by typing the value.
  4. Click Add.

    The date appears inside the Selections list pane.

  5. Click Ok to return to the Selection Criteria pane.

Procedure: How to Delete Dates From the Selections List pane

From the OLAP Control Panel:

  1. Click Selection Criteria.

    The Selection Criteria pane opens.

  2. In the Dimensions pane above the Selection Criteria pane, expand the dimension that includes the date field, and click Values under the desired field.
  3. Select one or more dates that you want to remove from the Selections list pane.
  4. Click Delete to remove the date.
  5. Click Ok to return to the Selection Criteria pane.

Reference: Date Format Limitations

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

  • The Date selection pane does not support Julian dates. However, if you are using Julian dates, the Date controls still open.
  • Dates containing only a day format (D, I2D, A2D) are not supported from the Date selection pane. Instead, the data source provides a list of values.
  • The Range check box is enabled on the Date selection pane when the date format contains one of the following formats:
    • Any smart date format. For example, YMD, MDY, YYMD, MDYY, Q, M
    • A4YY
    • I4YY
    • I8YYMD
    • A8YYMD
    • I6YYM
    • A6YYM