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.
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 Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies ProjectItems\ReportProject. 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 Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssembliesBusiness Intelligence Wizards\Reports\Styles.
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, read the Microsoft
article "Understanding Code Access Security in Reporting Services" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_
prog_extend_install_25ia.asp.) 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)" at http://msdn2.microsoft.com/en-us/library/ms156270.aspx.
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.