Microsoft SQL Server 2005 Reporting Services For Dummies
Reporting Services 2005, which comes as part of SQL Server 2005, is an intuitive reporting tool that's an extension of the powerful database management system. To develop effective reports, you should know how to view on in Reporting Services and create or change role assignments. Avoid humdrum reports by adding a few custom touches such as your corporate logo or green bar paper.
How to View a Report with SQL Server Reporting Services
Viewing a report with Microsoft SQL Server 2005 Reporting Services requires that your computer have at least a connection to the server that's running the report server. If you've installed the sample reports from the Reporting Services installation CD, you should be able to view a sample report by following these steps:
Connect to the Report Manager by pointing your favorite web browser to http://localhost/reports.
The Report Manager home page appears. You'll see a folder for Sample Reports, as well as any other folders defined on your report server. You'll also see the menu options related to site settings and subscriptions. Click the Show Details button (which then toggles to a Hide Details button) to see who created the folders and when they were last modified.
Select the folder that you want to view and click it.
Report Manager displays the contents of the Sample Reports folder. The folder consists of reports, resources, and (possibly) other folders defined on the report server. Each report in the Sample Reports folder can have a description (if provided), as well as a listing of the last date modified and who modified it.
Select the report that you want to view and click it.
Report Manager displays the report. The report shows all the information based on any parameter values entered, as well as on the security enforced on the report server.
A toolbar option enables export to a different format. This is a way to change the reported information into a spreadsheet or a PDF for sharing and collaborating with others.
Click the Back button when you finish viewing the report.
Alternatively, you can link back to the original report folder by clicking the link on the breadcrumb trail in the top-left of the Report Manager window.
How to Create Role Assignments with SQL Server Reporting Services
SQL Server 2005 Reporting Services includes several predefined roles to accommodate various categories of users. You can see the roles defined in SQL Server Management Studio when you expand the Roles folder within the Security folder in the Report Server.
If you right-click one of these roles and choose Properties from the list that appears, you'll see the detail task permissions checked for that role.
You can create additional roles if the predefined roles are insufficient. You can modify or delete either the predefined roles or the custom roles you create, as long as you don't invalidate the last remaining role assignment for your report server. You can define a new role or edit an existing role within Report Manager of the SQL Server Management Studio.
To create a role assignment in Report Manager, proceed as follows:
Navigate to the Contents page, and open the folder that contains the item for which you want to apply a role assignment.
Click the Properties tab, then click the Security tab, and perform one of the following:
a. If the item uses the security settings of a parent item, click Edit Item Security, click OK, and then click New Role Assignment.
b. If the item already has item-specific security defined for it, click New Role Assignment.
Type the name of a group or user account.
You can specify only one account name for each role assignment.
Select one or more role definitions that describe how the user or group should access the item, and then click OK.
To determine which tasks a role definition supports, click the name of the role definition.
If existing role definitions are insufficient, click New Role to create a new one.
Role definitions can contain either item-level or system-level tasks. You cannot combine tasks from both levels into a single role definition. Because the number of tasks that you can work with is relatively small, you typically don't need a large number of role definitions. Creating or modifying a role definition requires careful consideration. If you create too many roles, the roles become difficult to maintain and manage.
5 Ways to Customize Reports with SQL Server Reporting Services
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:
Right-click a row on the table grid and select Edit Group from the pop-up menu.
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 Files\Microsoft SQL Server\MSSQL.3\Reporting 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:
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=