Troubleshoot Reports Using the Report Analyzer

If you encounter problems running a report, or want to view potential issues after you create a report, you can run the report analyzer to check the report’s database queries.

This helps you fine-tune custom reports to ensure they run as quickly and efficiently as possible. The report analyzer examines your report and provides suggestions as to how you can improve the report by removing database tables, adding filters, and making other changes. The analyzer also shows you the estimated number of rows the report’s query may need to access so you know if the report can run without encountering row limits.

Note: Reports are analyzed using either the report or operational database, depending on which database they're configured to use. See Overview of Report Databases.

Open the report you want to analyze on the report design center.
Click Analyze on the Home tab.
The report’s Search window opens where you can enter the filter and variable values you want the report to use when it's analyzed.
If your report lacks required runtime selectable filters or the Open Report Without Prompting for Search Parameters check box is selected on the report’s Edit Settings window, the Report Analyzer window opens instead of the Search window. See How You Search in Reports.

If you're presented with the report’s Search window, enter the search criteria you want to use and click OK.

The search criteria you specify can change the estimated number of database rows the query must examine.

The Report Analyzer window is similar to the Report Definition window and includes the same functions at the top of the window (see View a Report Definition for information about the Report Definition window). The report analyzer lists the report’s filters, tables, output levels, and the estimated number of rows the report’s query will examine when it runs. If the analyzer finds potential issues with the report’s query, it lists the issues in the Notes column next to the applicable report component.

Report Analyzer Window

Report Component	Potential Issue
Filters	This section lists the expressions used for each report and level filter and identifies whether the filters’ fields are indexed in the database and whether the index is used. Custom fields can be indexed for faster queries, though too many indexes can slow down queries. The following messages can be returned in this section. The filter contains a custom field that does not have an index. To improve the performance of this query, consider adding an index on the custom field. The filter contains a reference to a pseudo field, so no index information is available.
Tables	This section lists the tables that are used in the report, along with the types of joins used to link the data in the tables. Four messages can be returned in this section. This table is in an outer join but there are one or more filters on fields in this table. Make sure this is by design. If not, consider changing the join type to inner or changing these filters to join filters to prevent an implicit inner join. This table isn't used in the report. Consider removing the table from the data set. If you need to filter out rows in the parent table that do not have rows in this table, consider adding a not null filter on the join field in the parent table. This table isn't used in all report levels. To improve performance, consider using report linking instead of drill-downs. With report linking, you can specify the table set for each level. The data set contains the Incident Performance (inc_performance) and the Transactions (transactions) tables, but there are no filters that provide additional joins between the two tables. To increase performance, consider adding a filter that provides an additional join between the two tables.
Levels	This section lists the output levels (also called drill-down levels) that are used in the report. One message can be returned in this section. This level contains one or more columns with a `sum_distinct` expression. Sum_distinct expressions have a negative impact on performance. If possible, consider a different approach that does not involve using a `sum_distinct` expression. You may be able to remove the `sum_distinct` expression by converting any drill-down levels to linked reports.
Rows	This section lists the estimated number of rows that will be examined by the query. Messages display if the number of rows exceeds the threshold values for the operational and report databases (also referred to as replication databases). Three messages can be returned in this section. The messages vary depending on whether your site has a report database in addition to the operational database and the Data Source options specified on the report’s Edit Settings window. The estimated number of analyzed rows for this query is `X`. This value exceeds the threshold of 2,000,000 for the operational database. Consider using the report database for this report. Otherwise, it's likely this will occur automatically. The estimated number of analyzed rows for this query is `X`. This value exceeds the threshold of 2,000,000 for the operational database. Since you have specified the operational database for this report, it's unlikely the report will execute. Consider using the report database for this report. The estimated number of analyzed rows for this query is `X`. This value exceeds the threshold of 5,000,000 for the report database. Consider adding or refining filters to reduce the number of analyzed rows to below this threshold. Otherwise, it's unlikely the report will execute. The report will likely return a large number of rows. Consider using filters instead of paging to navigate the result set. A row estimate isn't available because the report contains sum_distinct expressions. Due to the complex nature of the report, a row estimate isn't available. The estimated number of rows is `X`. However, the underlying query contains a one-to-many relationship so the estimate may not be accurate.

Click X on the top right to close the Report Analyzer window.
Click X on the top right to close the report.