Reporting Services Tips and Tricks

Choosing and installing SQL Server Reporting Services (SSRS) is only the first step in satisfying the bevy of report consumers in your organization. Now, you need to produce dazzling reports that use advanced features for displaying data in the myriad ways users want—and still get your other work done. Here are some tips and tricks for efficiently creating valuable, flexible reports that perform well, plus a sampling of some third-party tools you can use to extend SSRS's functionality.

Creating a Report Template

When you create a series of reports, you'll inevitably find report items that are common to all the reports, such as the report header, an image, a page count in the footer, or even a parameter. Creating a template that contains a skeleton of common report items can save you time and eliminate some of the repetition that comes with report writing.

Related: Reporting Services Quietly Develops

To create a report template, add a new report to your report server project in SQL Server Business Intelligence Development Studio (BIDS). Using the Report Designer, place all the common report items in the report. Then, give the report a name such as myReportBasedOnATemplate to indicate it's a template, as Figure 1 shows, and save the Report Definition Language (RDL) file to C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies ProjectItemsReportProject. Your template file will now appear in the Visual Studio installed templates list when you add a new report to your project, and the new report will contain all the common items defined in your template.

If you're a fan of the Report Designer's Report Wizard, which guides you through the report-creation process, you can add your own report styles to the Report Wizard. Simply edit the StyleTemplates.xml file located in the directory C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesBusiness Intelligence WizardsReportsStyles.

Using Custom Assemblies

SSRS lets you apply simple conditions to different report items (e.g., values, formatting, styles) by using expressions and report-level custom code blocks.

However, when writing complex logic or sharing functions across multiple reports, it's best to write the functionality as a custom code assembly that you reference from your report. By placing common custom functions, such as special calculations in a Microsoft .NET assembly, you gain the full power of .NET, better debugging capabilities compared with the Visual Basic (VB) expressions embedded in a report, and the ability to maintain your functions in a single spot rather than across multiple reports. Additionally, report expressions are limited to a subset of Visual Basic .NET (VB.NET), while custom assemblies can leverage either C# or VB.NET.

You reference the custom assembly from your report by using the References Tab in the Report Properties dialog box. Alternatively, you can place the assembly reference in a report template file so that the functions in your assembly are available to all reports that are based on your template. After you place the reference in the template, you can reference the functions in your assembly as you would any report-level code block—for example, by using a reference such as =Code.myAssemblyFunction().

When using custom assemblies, make sure you understand how to modify Code Access Security (CAS) in the SSRS policy configurations file so that you can execute the assemblies. CAS bases security on code, instead of users, so that you can appropriately secure expressions, code blocks, and assemblies that your reports use. (For details about SSRS and CAS policies. You also need to mark your custom assembly as AllowPartiallyTrustedCallersAttribute, which will remove any implicit LinkDemands on your assembly and allow code execution.

Adding Fields to Page Headers and Footers

You might want your report's page header to include more than just an image and a title. For example, you might want to add an employee ID or some other database field value to the page header. However, if you've ever tried to add a field from a data set to the page header, you know that SSRS rejects the action. But there's a workaround: Place the field value in an item in the body of the report, then refer to that report item in the header (for example, =ReportItem!txtFie ldValue.Value).

This solution has one catch: If your header appears on each page, the report body field that you reference in the header needs to appear on each page as well. Also, make sure the field repeats with each page break in either HTML or PDF rendering; otherwise, the field won't appear in the header. Note that this tip also works for adding fields to a report's page footer.

Setting Page Sizes

Despite the hype of the paperless office, users print reports. Because most users export reports to PDF if they're going to be printed or emailed, it's a good idea to plan the size and orientation of the report in advance so that it's printer friendly.

The items placed on a report often dictate size and orientation, but you can define page size to optimize viewing reports rendered in different formats. To set the report size for a report rendered as PDF, for example, use the PageSize property settings. For HTML viewing, specify InteractiveSize property settings. Both PageSize and InteractiveSize are properties of the Report object and appear in the Properties window. Setting the interactive page size will expand the visible report size within the browser but won't affect the report size when it's rendered in other formats, such as PDF. Remember, even if the HTML version of the report doesn't have any page breaks, the PDF version likely will have them. When setting the report size, note that SSRS defines by default a 1-inch margin on all four sides of a report, which is generally more than most reports need and eats into valuable report space.

Boosting Productivity and Performance

It's fairly easy to write a query to fetch data for a report, display the data in one of the various report controls, and deploy the report to users. But what happens when even the best-written query takes too long and slow report performance begins to irritate users? There's no panacea for a poorly written query, but the following SSRS tricks can improve the overall performance of your reports.

Use snapshots to avoid bottlenecks. You can avoid the bottlenecks of long-running reports by creating a report snapshot to run nightly or during periods of low activity on your system. A snapshot automatically runs the report based on a defined schedule and stores the results in the report server database. You can then render reports from the snapshot instead of from the production database, easing the burden on your server.

If you want to enable report users to filter results from the snapshots, you need to design the report so that it uses report filters rather than a query parameter. A snapshot runs for one set of query parameters (if present), and if those values change when a user executes the report, the snapshot will be invalid. I discuss report filters a little later.

Define pagination to hide overhead. For reports returning a large number of records for display in a table or list, you can use page-breaking pagination to hide much of the processing overhead from the user. Several report controls feature properties for inserting page breaks before or after the control or before or after groups within the control. For example, if a user requests a report that returns 1000 rows of data, you can use page breaking to render the initial page of results while the report server renders the rest of the pages in the background. Without pagination, the user would have to wait to view the report until SSRS had rendered all 1000 rows.

Implement filters for performance. As noted earlier, if you use report-level filtering on data regions and data groupings rather than using query parameters as filters, SSRS can filter results from an execution snapshot, saving you a trip to the production database to collect report data. When you use a query parameter as a filter, SSRS passes the report parameter value to the query that generates the data set so that the query can perform the filtering. In contrast, when you use a filter on a data region, for example, the report parameter value is implemented through an expression on the data region's Filters tab, as Figure 2 shows. To see how to add filters, read the Microsoft article "How-to: Add a Filter (Report Designer)."

Enable drilldowns for detail. Instead of giving users a report that shows all the data at once, design a summary report that presents a subset of data and let users drill down to get more detail. Such a report design—using master detail reports that take advantage of SSRS's navigation capabilities—lets individual users dig deeper into data they're interested inwhile avoiding returning huge reports to everyone. Many parts of a report control have a Navigation property, which you can use, for example, to create a hyperlink on the control for passing specific values, as Figure 3 shows, or to navigate to another report.

Use rectangles for consistency and efficiency. Consider grouping report items in rectangles for more controlled and consistent formatting and easy object movement. With this strategy, you can hide several items at once and in one place, you can place multiple text boxes contained in a rectangle into a single table cell, and you have more pagination options. Place report items inside a rectangle by dragging and dropping or cutting and pasting them. To verify that an item is inside the rectangle, check the report item's Parent property.

Refresh data sets. When a stored procedure, view, or query on which a report is based changes, you must update the report data set to reflect the changes. You can re-execute the data set to update it by clicking the exclamation mark on the Dataset toolbar. Then, click the data set Refresh button on the Dataset toolbar. The refresh operation will use any parameters entered during the execution and will add or remove any fields or parameters changed in the underlying procedure, view, or query. This process automatically discovers any changes, eliminating the need to change parameters on both the Report menu and the Dataset dialog box.

Take advantage of free reports. Microsoft provides many free SSRS code samples, including valuable Report Packs that give you predefined reports for reporting against Microsoft IIS, SQL Server Integration Services (SSIS), Exchange Server, Windows SharePoint Services, Great Plains, and other Microsoft applications. Report Packs are not only useful out of the box, but also serve as examples for how to build your own custom reports.

Related: Exporting SSRS Reports—For Free

You can find another set of free reports in the SSRS samples, including easy-to-install execution log reports that show report-log activity in your environment. The execution log reports require you to create a small database and SSIS package (provided in the sample) to hold execution data. The 11 SSRS samples include reports such as the must-have Average Report Execution Time, Longest Running Reports, and Reports By Month.

Embed basic JavaScript code in a report. You won't find this tip in any Microsoft documentation, but a few lines of JavaScript code can enhance report interactivity. Using the expression ="javascript:history.back()" in a text box provides a handy Back button, for example, and ="javascript:void(window. open('url'))" lets you open a link without leaving a report.

Implement dynamic images for a scorecard feel. Report consumers love colorful reports, and including images is a great way to add pizzazz and highlight important values on a report. Conditionally presenting images, such as a stoplight for a value out of range or a check mark for a value that meets or exceeds expectations, is a valuable way to communicate information to users and give your report a "scorecard" feel. You can conditionally show or hide an image by using expressions for the image's Visibility: Hidden property.

Display selected parameter values in the report header. If you've ever looked at a report after you've exported it to PDF (or any other rendering format), you understand the importance of displaying certain parameter values in the report header. Without being able to see the built-in or custom parameter selections, a report consumer has no idea what the parameters were when the report was run. You can prevent user headaches by placing expressions (such as =Parameters!ParamName.Value or =Join(Parameters!MultiParamName.Value, ", ") in a few text boxes in the page header to show which multiselect report parameter values were selected when the report was executed.

Use dynamic grouping for control. Although you can't control everything on a report by using expressions, you can control grouping this way. In some cases, you can even avoid writing an additional report by using expression-based grouping. For example, you could use expressions to design a report that features a grouping on Region and let users drill down on the value for a specific region. You can use the same design technique to then let users drill down on values for specific sites in that region. Just set up a parameter called GeoGroup and give it unique values such as RegionID and SiteID, as Figure 4 shows. In the data region, set your grouping to the expression =Fields(Parameters!GeoGroup. Value).Value, as Figure 5 shows. Now the data region's grouping is parameter-based, making the report reusable and more flexible.

Go further with ReportViewer controls. Many SSRS implementations don't go beyond deploying a report to the Web and giving users a URL for access. But for developers who want to create customized parameter selection or integrate reports into an existing application, the Visual Studio 2005 ReportViewer controls are the place to start. There are two freely redistributable ReportViewer controls: one for Web applications and one for Windows applications. You can configure both controls to run in remote mode (which retrieves and displays reports from the report server) or local mode (which processes a report based on an .rdlc file deployed directly on the client). An advanced feature of the ReportViewer controls is the ability to consume a DataTable or public properties on enumerable objects such as the report's DataSource. Details about ReportViewer controls.

Tapping Into Third-Party Tools

SSRS is still a relatively new product, but it leverages the open RDL standard for defining reports. As the product matures, more third-party vendors will offer tools that support and extend its functionality. Here is a sampling of tools that already can enhance your SSRS reports and offer additional functionality.

OfficeWriter. If you've worked much with SSRS, you've probably had report consumers ask if they can export a report to Microsoft Word or add a formula when they export a report to Microsoft Excel. You currently can't perform these functions with SSRS, but you can with Software Artisan's OfficeWriter. OfficeWriter creates templates that let you export SSRS reports to either Word or Excel,while preserving the functionality of a true Word or Excel document.

OfficeWriter also lets end users write reports from within Word or Excel (without using Visual Studio) and publish the resulting RDL file to the report server.

Dundas controls. Dundas software now supplies advanced controls for SSRS. The Dundas Chart for Reporting Services includes advanced chart visualizations and additional chart types and statistical formulas. If you're going to do more than basic charting or want your charts to stand out, Dundas Chart for Reporting Services is the way to go. Dundas also offers three other components for SSRS: Dundas Gauge, which adds dashboard and scorecarding features to reports; Dundas Calendar, which adds date-related visualization elements; and Dundas Map, which adds map visualization features.

Barcode control. IDAutomation has leveraged its .NET Forms Control to render barcodes inside an SSRS report. Get more information about the company's .NET Barcode Forms Control.

SSRS Can Fit Your Needs

Because reporting is a key task in every organization, SSRS is a very useful tool.SSRS is designed to help you quickly and efficiently develop and deploy reports, but the more product experience and knowledge you have, the more value you can deliver to your users. You'll soon begin collecting your own SSRS tips and tricks and developing workarounds to fit your needs.

Learn more: Reporting Services Scripter