Querying Filtered Data

About this task

The Query Filtered Data function is similar to the calculated data query, with the addition of a search filter on the data. The search filter allows you to filter the actual data retrieved with a filter condition. This function is useful when trying to retrieve all data for a specific Batch ID, Lot Number, or Product Code and for filtering data where certain limits were exceeded, such as all data where this temperature exceeded a certain value.

When querying filtered data, you can use a Filter Expression instead of FilterTag, FilterMode, and FilterValue parameters. You can use multiple filter conditions in the filter expression. For more information and examples on filter expression, refer to Advanced Topics.

Note: Do not use the Desc option for the Output Range in the Filtered Data Query dialog box. Using this option may cause the Excel Add-In to become unstable. If you use this option and find that Excel is unstable, try minimizing the Excel application window, expose the Filtered Data Query dialog box, and close the dialog box. Excel should then function normally.

Procedure

  1. Select Query Filtered Data from the Historian menu.
    The Historian Filtered Data Query dialog box appears.
  2. Select a server from the drop-down list. If you do not specify a server, the Add-In automatically uses the default server.
    To set the selected server as default, ensure the Set Server to Default option is enabled.
  3. Select a tag on your worksheet and, while they are selected, click in the Tag Name field of the dialog box.
    This enters the tag names for you automatically. You can also enter the tag names manually in the Tag Name field of the dialog box.
    Note: While entering multiple tag names manually, separate each tag name with a colon. If your tag name has a colon within it, then you should select the tag names via cell references only.

    Do not use wildcards in this field. If you use a tag mask instead of a tagname, Historian only returns the first possible match.

    Optionally, you can select the tags from the Advance Tag Search dialog. For more information, refer to Searching a Tag Using Advance Search Criteria..

  4. In the Query Time section enter values of time in the Start Time and End Time fields.
    You can also use relative time entries to this field.
  5. In the Query Criteria String, enter the query criteria along with the # symbol.
    For example, if the query criteria string is to retrieve only good data quality values, then you should specify #ONLYGOOD as the Query Criteria String. For more information, refer to the Query Modifiers topic.
  6. In the Sampling Type section, select a type from the drop-down list. For a list of available sample types, refer to the Sampling Types topic.
  7. The Calculation Field is active only after you select Calculated Sampling as the Sample Type. You can select a Calculation Algorithm type from the drop-down list.
  8. In the Sampling Interval section, select either the By Interval or By Samples option.
    • The By Interval option displays two entry fields, Interval and Time Unit. Enter values in both. For example, to sample at 10 minute intervals, enter 10 in the interval field and select Minutes in the Time Unit field.
    • The By Samples option displays a Number of Samples field. To specify a number of samples for the data query, enter a number in this field. For example, to query 100 samples, enter 100 in this field.
  9. Enter the state value. The State Value is active only after you select Sampling Type as Calculated and Calculation Field as State Count or State Time.
  10. In the Output Display field, select one or more parameters for the output.
    Click a name to select it. To select multiple individual tags, press the Control key and click the tagnames. To select a sequence of tags, press the Shift key and click the first and last tagname of the sequence.
  11. In the Filter Definition section, enter filter parameters in the fields for Filter Tag, Filter Expression, Filter Comparison, Include Date Where Value Is Equal To, and Include Times.
    These fields are optional. If you do not enter any values, the query returns all values without filtering. See the Filter Parameters for Data Queries section for more information.
  12. Click in the Output Range field and select a range of cells in a single row or column to determine where the returned data is placed.
  13. Select either Columns or Rows for the output display.
    Selecting Columns displays a table of values with parameters arranged in columns with header labels at the top. Selecting Rows rotates the table 90 degrees.
    Note: When selecting multiple tags, the orientation of the return data is based on the orientation of the selected tags and the Row/Col selection is ignored.
  14. Select either Ascending or Descending to set the order of the retrieved data in either ascending or descending order.
  15. Click OK to initiate the query. Click Cancel to abort the operation and close the dialog.
    Note: For an Array Tag, each element is displayed in separate rows with the tag name and index.

    TagStats Calculation mode is not supported.