5 Ways to Customize Reports with SQL Server Reporting Services - dummies

5 Ways to Customize Reports with SQL Server Reporting Services

By Mark Robinson

Part of Microsoft SQL Server 2005 Reporting Services For Dummies Cheat Sheet

Add a few lines of extra coding in SQL Server 2005 Reporting Services to customize your reports. Try some of these interesting (and cool) tricks to make your reports either stand out or fit into the existing standards you may be required to fulfill.

Format green bar paper

If you work at a company that still runs mainframe computers, you may have seen the old “green bar” paper stock. This is the wide paper that mainframe computers use to print out their results. The paper is lined alternatively with green and white stripes to assist in reading across the columns of a report.

In SQL Server Reporting Services, to simulate the stripe effect when printing, you need to do conditional formatting on the contents of table cells. To change the BackgroundColor to be an expression that alternates between green and white, use this expression for the BackgroundColor property for all cells in the report line:

=iif(RowNumber(Nothing) Mod 2,"Green","White")

Control page breaks

If you want to better control page breaks within a report, you can do so with expressions on groups in a matrix or table. If you know that you would like to insert a page break on a specific row count, you can specify that in the group expression; that way, you can introduce a hidden page break grouping. Here’s how:

  1. Right-click a row on the table grid and select Edit Group from the pop-up menu.

  2. In the Expression Edit dialog box that appears, enter the following expression to insert a page break after 20 report lines:


Create running totals

In some reporting situations, you may want to express the cumulative total for a report column as it changes row by row. In order to do this, you need to enter an expression for the value in the table cell. Say you want to specify a running total for a field called SalesAmt. The following aggregate function will provide you with running totals:

=RunningValue(Fields!SalesAmt.Value, Sum, Nothing) 

Change the Report Manager folder icon to a corporate logo

You may want to customize the icon at the top left of the Report Manager home page and put your corporate logo in its place. If you browse the source of the Report Manager, home page you’ll see that the file reference for the folder icon is:


Searching around in the program manager, you can locate this file in the directory:

 C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting Services

If you open this file, you’ll find that it’s a 48 x 48 .jpg image. Therefore, if you can make a similar resolution .jpg of your corporate logo and save this new logo file as 48folderopen.jpg, the next time you refresh your Report Manager, you’ll see your corporate logo in the top-left corner of the web page.

Suppress objects or formulas in an Excel rendering of a report

When you don’t want to see a document map or formula in an Excel rendering of your report, you can control the properties of the rendering by using the device information settings when specifying the report using the URL access method.

For example, to suppress a document map from a report in Excel, use the URL link as follows:

http://servername/reportserver?/SampleReports/Sales Order Detail&rs:
Command=Render&rc:Format=HTML4.0 &rcOmitDocumentMap=True

When you want only the data values and no formulas generated in Excel for the entire report, you can use the Device Information Setting of OmitFormulas to suppress formula generation as in the following expression:

http://servername/reportserver?/SampleReports/Employee Sales Summary&EmployeeID=