Export (0) Print
Expand All

Running Large Reports

SQL Server 2000

If you are working with a large report, you must choose report generation, rendering, and delivery options that can accommodate large documents.

Report size is largely determined by the row set that comes back from the query. The non-data portion of a report consists of layout information and a query, and is not a determining factor in the final size of a large, generated report.

To estimate the size of a report after it is processed, review the row count that is returned from the query. If it is many thousands or hundreds of thousands of rows, you should follow the recommendations in this topic. You can also deliver the report to a file share and check the size of the file. To determine the size of an unprocessed report (that is, the report definition), check the size of the .rdl file.

Note  For reports that contain volatile data, report size can change dramatically from one report run to the next. In this case, you must monitor the data source to determine how data volatility affects your report and whether you need to follow the steps prescribed in this topic.

Configuration Recommendations

Recommendations for report execution, report history, and report access include the following items:

  • Design the report to support pagination. The report server sends a report one page at a time. If the report includes pagination, you can control how much data is streamed to the browser. For more information, see Working with Multiple Pages. If adding page breaks is not an option, you should configure the report to run as a report execution snapshot. This recommendation is described next.
  • Configure the report to run as a report execution snapshot. Do not set a time-out value for report execution. Use a schedule to determine when the report data is refreshed.

    Configuring a report to run as a report execution snapshot prevents it from running on demand. Never run a large report on demand because it will almost never succeed. The HTML rendering format that is used to initially render a report opens a report in a browser, and most browsers cannot accommodate very large documents. For example, a report that contains 5,000 rows of data almost certainly cannot be viewed in a browser in a single page.

  • Configure the report to use a shared data source if you want flexibility in determining whether the report is processed. One advantage to using a shared data source is that you can disable it so that it cannot be used to get data for the report. Disabling the data source prevents report processing.

    Regardless of whether you use a shared data source or report-specific data source, you must use stored credentials for the data source connection. Stored credentials are required for the report distribution strategy described later in this topic.

  • Disable report history (optional) if you want to conserve disk space. The recommendations for report distribution that are offered later in this topic provide an alternative to storing a large report in report history. To disable report history, clear all the check boxes on the History properties page.
  • Configure the report to use item-level security. Limit access to users who define the subscription and manage the report.

    Specifying item-level security allows you to control access to the report. By default, users can open any report that they can view in the folder hierarchy. Even if you configure a report to run as a snapshot, users who can view the report item in a folder can open the report. If the report is very large, the report will hang the browser when a user opens the report in Report Manager.

    To restrict access to the report, edit item security by replacing the default role assignments with new ones that allow access to just those users who need to create the subscription or manage the report.

Rendering Recommendations

Before you configure report distribution, it is important to know which rendering clients can accommodate large documents. The recommended format is Acrobat (PDF), but you can choose from any format that supports pagination. You can specify the format when you define how the report is distributed. For more information about format recommendations, see Exporting Reports.

Distribution Recommendations

Report distribution is achieved through subscriptions. Subscription and delivery features are important for working with large reports. Through a subscription definition, you control how the report is distributed and rendered. You can use either a standard subscription or a data-driven subscription to deliver the report. Recommendations for subscription and delivery include the following:

  • Configure a subscription to use Acrobat (PDF).
  • Configure a subscription to use file share delivery. Use a desktop application to work with the report after it is generated. Set permissions on the file share to determine who can view the report. Note that once the report is on the file share, it is no longer controlled or secured by Reporting Services.

If you want to be notified when the report is updated, create a second subscription that uses e-mail delivery to send a notification only.

See Also

Distributing Reports Through Subscriptions

Report Size and Limits

Setting Report Execution Properties

Specifying Credential and Connection Information

Managing and Working with Published Reports

© 2014 Microsoft