When I search a problem word in a criteria field, i.e. Summary LIKE "text error", why don't I get any results when I know they are there?

Environment:

Custom Reports, Phrase Searching

Issue:

I am trying to run a report to get a list of incidents by reference number that contain a specific error message. When I put the problem word in a criteria field, i.e. Summary LIKE "text error", I get no results, yet I know they are there.

Resolution:

If your report is not returning records that you expect to be in the report, review the report configuration. If you can find a record that you think should be in the report, open that record to verify that it meets the search criteria set in the report.

Specifically, review your report for the following conditions which may result in records not being included unexpectedly:

Logical Expression: Review the logical expression used to join multiple filters in your report. If the expression uses and AND join between filters and you expect it to use an OR join between filters, several records will be excluded from your report.

• From the Home tab and the Data Set group, click the Filters button and review the logical expression.
   

For more information on editing the logical expression, refer to Answer ID 1041: Using the Logical Expression feature in reports.

Join Type Between Tables: By default, tables are joined using an inner join between the tables. This means that the record must be referenced in both tables. For example, if a report uses tables where the incident and staff accounts tables are joined with an inner join, only incidents that are assigned to a staff member are included in the report. Incidents that are not assigned are not referenced in the staff accounts table so those records would be excluded from the report.

Review the data set for the report and determine which join type you need between the tables of your report. For more information on join types, refer to Answer ID 1839: Types of Table Joins Used with Reports and Views.

Text Searches with the LIKE Operator: When creating custom reports, the LIKE operator is designed to match the contents of the Value field exactly. Therefore, you will generally want to include wild cards when searching, i.e. the percent character (%), around the specific word or phrase of interest.

When configuring filters for a report, the Complex Expression operator includes a trailing wildcard in the search. However, if you need to include a preceding wildcard, then you need to include the trailing wildcard as well.

For example, when using the complex expression operator with an incident summary, %error% returns all records that contain error. Using 'error' returns records that begin with "error" because a trailing wildcard is assumed.

By including the % symbol, the report criteria is functionally equivalent to "CONTAINS text error".

Data-type Fields: When working with reports, if you include a date-type field as a run-time selectable filter, be sure to set the operator to "between". This allows you to specify a date range for the value of the field.

If the operator for a date field is set to "equals", then the date associated with the record must match the date and time exactly (to the second) in order for the record to match the report criteria.

For example, if you include the incidents.created field as a run-time filter, if the filter is set to be "incidents.created=05/04/16 12:00 AM", an incident would have to be created exactly at midnight on 5/04/16 to be included in the report.

It is most common to use the "between" operator with date type fields. You can also use the open-ended options including, <, <=, >, or >= as well.