Integrating .NET Code and SQL Server Reporting Services

SQL 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.

1. Create a new SSRS report by selecting File | New Project from the Visual Studio menu.
2. Select Business Intelligence Projects | Report Server Project from the provided dialog box. Name the report “CodeReportingServices”.
3. 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.
4. Click the Edit button to activate the Connection Properties dialog box.
5. Enter the name of the server in the Server Name field and provide the login credentials to the server (if necessary).
6. Select the Northwind database from the list of databases and click OK.
7. On the New Data Source dialog box, click OK to save the new data source.

Now create a report layout.

1. In the Solution Explorer, right-click the Reports Folder and select Add New Item from the pop-up menu.
2. Select Report from the Add New Item dialog box. Name this report “CodeNorthwindCustomerReport.rdl”.
3. From the Report Layout toolbar, select the Data tab.
4. Select <New Dataset…> from the Dataset drop-down list.
5. In the provided dialog box, name the dataset NorthwindCustomers and specify CodeNorthwindDataSource in the Data source drop-down list.
6. In the query string field type SELECT * FROM Customers.
7. Click the exclamation mark to execute the query. This will return a list of customers.
8. Now switch to the Layout tab of the report.
9. Switch to the Report Items toolbox.
10. Drag a Table object from the Report Items toolbox onto the report.
11. Switch back to the DataSets toolbox and drag the CompanyName and ContactName columns onto the Table object’s detail band.
12. The design should look like the one in Figure 1.
13. 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.

By: Rod Paddock

Rod Paddock is the editor of CoDe Magazine. Rod has been a software developer for more than 10 years and has worked with tools like Visual Studio .NET SQL Server, Visual Basic, Visual FoxPro, Delphi and numerous others.

Rod is president of Dash Point Software, Inc. Dash Point is an award winning software development firm that specializes in developing applications for small to large businesses. Dash Point has delivered applications for numerous corporations like: Six Flags, First Premier Bank, Intel, Microsoft and the US Coast Guard.

Rod is also VP of Development for SQL Server tools maker, Red Matrix Technologies. (www.redmatrix.com).