Get an Edge with SQL Server 2005 Reporting Services

The ultimate utility of a database lies in presenting the data in different formats to fulfill the information requirements of various users. Microsoft SQL Server 2005 Reporting Services (SSRS) gives nontechnical end users and programmers a server-based reporting platform that will generate any kind of report from any kind of data source for both desktop and Web applications.

Creating a Report

The first step in generating a report is to launch the Business Intelligence Development Studio — an all-purpose IDE for Business Intelligence programming in SQL Server 2005. Here, we will use the Report Project template and the pubs database to create a new report model project.

After installing the Business Intelligence Development Studio, you should see a new project type window as shown below with the Report Project Wizard and Report Project templates available. Select the Report Project template to create a new project and name it "PubsReport." Click OK to create this project report.

Next, in the "Solution Explorer" window, you should see two folders, Shared Data Sources and Reports, displayed under the PubsReport project.

If there are multiple reports in a project connecting to a single database, it is more efficient to create a shared data source and use it in multiple reports. To set up the shared data source for the PubsReport, Right click the Shared Data Sources folder and select the Add New Data Source option.

In the Data Link Properties window, select the Connection tab. Now select the server name and database. (In our case, it is "local" and "pubs" respectively.) To keep it simple, select "Use Windows Integrated security" and then click on "Test Connection" to test the connectivity before moving ahead.

In the Solution Explorer window, you will now see the "pubs.rds" report data source under the Shared Data Sources folder as shown in the figure below.