How to report more than 50,000 rows for individual reports

book

Article ID: 179816

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
How can a report show more than 50,000 rows?

Answer
By default all reports are limited to 50,000 rows by the setting in the coresettings.config file (  <customSetting key="ReportMaxRowCount" type="local" value="50000" />).  Changing this value is not recommended as reports generally should be limited until they have been proven to operate correctly. There may be cases where a valuable report needs to display more than 50,000 rows. This may be done by doing the following:

After creating and verifying the report a manual edit of the SQL in the report will need to be done. Of course manual editing of the SQL report renders it impossible to use the Query Wizard in the future to modify the report (query changes must be done in the raw SQL statement). This is the reason for creating and verifying the report first, so that the Wizard can be used.

The edit is to add the set rowcount statement to the beginning of the report. Then the rest of the query (select ...) is left as normal.

set rowcount 0
select ...

To edit an existing report:

  • Clone the Report to ensure the Default Report's integrity
    • Select the cloned report in the left pane
      • Select the Edit (Pencil) button
    • Edit the SQL script
      • Select the Edit (Pencil) button listed under Queries
    • Input the following on the top line
      • set rowcount 0
    • Apply changes and run the report

Note: This will allow for the report to render until all the data is retrieved, so be advised that it may take longer to render than expected. The 0 can be replaced with any number to affect the total rows returned (example: set rowcount 100000).

N.B. The "set rowcount 0" feature has been removed in SMP 7.x.

For Version SMP 7.1 path = C:\ProgramData\Symantec\SMP\Settings\coreSettings.config (edit file using Notepad++)

 

Related KB.

TECH187740