QL Server Reporting Services versions 2000 and 2005 (SSRS) has many powerful features. SSRS has a well-designed data access engine, a great set of layout tools, and an excellent expression system for creating complex formulas. While the expression system is quite powerful it is not suitable for all applications. This is where SSRS shines. SSRS gives developers the ability to add custom code to their report layouts. This article demonstrates adding custom code to SQL Server Reporting Services reports.
Creating the Sample Report
This article uses data from the SQL Server sample database Northwind. To create new reports, you'll perform the following tasks.
- Create a new SSRS report by selecting File | New Project from the Visual Studio menu.
- Select Business Intelligence Projects | Report Server Project from the provided dialog box. Name the report "CodeReportingServices."
- In the Solution Explorer, right-click the Shared Data Sources folder and select Add New Data Source from the pop-up menu. This will activate the Shared Data Source dialog box. Name the data source CodeNorthwindDataSource.
- Click the Edit button to activate the Connection Properties dialog box.
- Enter the name of the server in the Server Name field and provide the login credentials to the server (if necessary).
- Select the Northwind database from the list of databases and click OK.
- On the New Data Source dialog box, click OK to save the new data source.
Now create a report layout.
- In the Solution Explorer, right-click the Reports Folder and select Add New Item from the pop-up menu.
- Select Report from the Add New Item dialog box. Name this report CodeNorthwindCustomerReport.rdl.
- From the Report Layout toolbar, select the Data tab.
- Select <New Dataset
> from the Dataset drop-down list.
- In the provided dialog box, name the dataset NorthwindCustomers and specify CodeNorthwindDataSource in the "Data source" drop-down list.
- In the query string field type SELECT * FROM Customers.
- Click the exclamation mark to execute the query. This will return a list of customers.
- Switch to the Layout tab of the report.
- Switch to the Report Items toolbox.
- Drag a Table object from the Report Items toolbox onto the report.
- Switch back to the DataSets toolbox and drag the CompanyName and ContactName columns onto the Table object's detail band.
- The design should look like the one in Figure 1.
- You can also preview the report by selecting the Preview tab in the Report Designer. Figure 2 demonstrates what the report looks like in Preview mode.
Adding Custom Code to a Report
|Figure 1: Customer report in Design mode.||
|Figure 2: Customer report in Preview mode.||
Now that you have created a simple report you can add custom code. SSRS provides two mechanisms for adding code to your report: You can embed Visual Basic .NET code directly in your reports or you can add externally created and compiled assemblies.