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. On the Edit Report Attributes page, click Restrict by SQL WHERE Clause.
The Restrict by SQL WHERE Clause page appears.
2. Click Show columns.
The Select Table Columns dialog box appears listing the column names and types.
3. Click the name of the column to include in the WHERE clause.
4. 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.
5. 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.