Specify report restrictions

You can restrict a report to data that meets a specified condition by using a SQL WHERE clause. For example, you might want the report to include only males who reside in the United States and are between 18 and 55 years old. The application applies the report restriction, if specified, after generating the report, but before displaying the report.

For an interactive report, the query run as part of the report determines which cases are included in the report. A SQL restriction, on the other hand, acts on the data for those cases. For example, suppose you want a report that shows drug counts for only suspect drugs. If you include a suspect drug indicator as a query variable, the report runs against all cases including suspect drugs. Some of these cases might also have non-suspect drugs. However, you could specify a SQL restriction to include only suspect drugs in the report.

Note:

Columns of the type CLOB are truncated in reports. Therefore, if a column referenced in the restriction has the type CLOB (as is typical with narrative text), the application applies the restriction to the truncated values for that column.

1. In the left navigation pane, hover on the Data Analysis icon (), then click Report Definitions.
2. On the Report Definitions page, accept the case series shown or click Browse to the right of Case Series to display the Select Case Series page and select a case series.
3. Click a Report Definition's Row Action menu (), then click Edit.
4. On the Edit Report Columns page, click the Report Attributes radio button.
5. On the Edit Report Attributes page, click Restrict by SQL WHERE Clause.
6. Click Show columns.

   The Select Table Columns dialog box appears listing the column names and types.

7. Click the name of the column to include in the WHERE clause.
   • Note the column type, so that you know what syntax is valid for that column in the WHERE clause.
   • On the Restrict by SQL WHERE Clause page, the column you selected appears in the SQL WHERE clause field in square brackets. The Select Table Columns page remains open, so that you can select other columns.
8. In the SQL WHERE clause field, complete the SQL condition. You might, for example, select the AGE column (placing AGE into the field), and then type “>=65” to restrict the report to patients who are at least 65.
9. When you are satisfied with the WHERE clause, click Apply.

   If the SQL syntax is valid, the Edit Report Attributes page appears, showing the SQL WHERE clause and the number of rows that meet the criteria of the WHERE clause, or

   if there are errors in the SQL syntax, an error message appears when you try to run the report.

   Note:

   To know the list of supported SQL functions, see SQL functions.