Building a Multi-table Report with SQL 2005 Reporting Services

Introduction

For an enterprise to succeed accurate and timely business decisions are important, whether they be for asset management, competition assessment, campaign scheduling or any other type of activity. As a consequence business reporting software constitutes an important part of the business software suite. There are a large number of vendors catering to a variety of reporting needs that may range from MS Excel and include such well known names as Business Objects, Cognos, MicroStrategy, Actuate and a host of others.

Beginning with VS 2003, Microsoft started adding its own reporting software, better known as SQL Server Reporting Services (SSRS) into its latest version of Visual Studio 2005. SSRS addresses some of the challenges in enterprise reporting, namely real-time access to data, data security, integrated data access, extensibility for reporting, deployment options that range from hard-copy, report servers to web services, and so on.

This tutorial shows you how you can generate a multi-table report using the SSRS which accesses data on your SQL 2005 Server. .NET 2.0 makes this task extremely easy with data access using its .NET data providers, drag and drop features, superior server controls, an in-situ query builder tool, and tight integration with the web server for deployment.

The data behind the report uses two of the tables in the Northwind database on the SQL 2005 Server. The business intelligence project through its Reporting Services template accesses the data and prepares it in the design environment of Visual Studio, which is later deployed on the web server. The tutorial uses the Visual Studio 2005 IIS 5.1 server on a Windows XP Professional Media Center OS.

Create an RS Project

The New Project window shown below can be accessed from the File---> New Project... drop-down from the main menu. You will find the Business Intelligence Projects at the top of the project Types. When you choose this as the project type, you can choose from a variety of Visual Studio installed templates, of which there are two items related to SSRS.

When you choose the Report Server Project Wizard and choose a name for the project, RSMulti in this tutorial, you will put the wizard in motion. The wizard shows up with its customary window as shown in the next picture. If you read the welcome screen you will see that the Report Wizard provides all that is necessary to create the report from data access to report formatting.

When you click the Next button, the Report Wizard - Select the Data Source window shows up. This is an important step where you need to provide the inforamtion for the data that is going to be incorporated into the report. You have the possibility of choosing an existing data source or creating a new one. In this tutorial a new data source will be created.

Connecting to data

The name provided for the New data Source is DS2005. Click on the Edit... button. The Connection properies window shows up as shown in the next picture. The screen comes up with the default data source, the Microsoft SQL Server (SQLClient). This is a new data source in SQL 2005 Server.

For the Server Name:, insert Hodentek\Mysorian. For the SQL 2005 Server on this machine, the User Name=sa; and Password=xxxx are the proper ones for SQL Server authentication. Place a check mark in the Save my password checkbox as shown. Choose the Northwind database from the Select or enter a database name drop-down. The drop-down will show all the databases assuming the information you entered is correct. You may also test the connection by clicking on the Test Connection...button.

Close the Microsoft Visual Studio message window by clicking on the OK button and close the Connection properies window as well.

This will get you back to the --ReportWizard-Select DataSource window.

Click on the Next Button.

Creating a query

The Report Wizard--Design the Query window pops up as shown in the next picture. Herein you will be building the query used in the report.

Click on the Query Builder... button. The Query Builder screen opens up as shown in the next picture. If you are used to any of Microsoft's database software such as MS SQL Server or Visual InterDev, this should be familiar to you. However, in order to bring the tables to the design pane, you need to click on the icon at the very left of this page at the top. When you hover over this icon with the mouse the tool-tip should show 'Generic Query Designer.' If this is not properly set, the context sensitive drop-down menu that shows up when you right click the design pane will not show the menu to add tables.

Now when you right click in an empty area in the design pane, you can choose to add the tables from the database you chose in the earlier screen as shown.

When you click on Add Table... in the above drop-down, you will be presented with the window shown in the next picture, where you add the tables to the design pane. Click the Customers and Orders tables. Close the Add Table window. You will find the two tables in the Query Builder design pane as shown.

The two tables show up as joined by some relationship that is defined by their primary/foreign key relationships. If you want to examine what it is, click on the line joining the two tables to show the join Properties as shown below. The properties indicate that CustomerID is the common column for the two tables and that the join is of the type Inner Join.

Now choose the columns that you need to include in your report in the check boxes alongside the columns in the two tables. The default SQL statement in the SQL pane gets modified simultaneously with your choice. Here a number of columns have been chosen. The query may be modified to suit your needs as the processes of selecting and deselecting are reversible.

Once you are satisfied that you have made the right choices, the right filtering, and the right ordering, you may test the query by executing it. This is again accomplished by bringing up the context sensitive help and picking the ExecuteSQL item as shown.

As mentioned earlier you may refine the query by making changes to the SQL Statement, or in the designer. The query chosen for this report is as shown in the next paragraph.

SELECT Customers.CompanyName, Customers.City, Customers.PostalCode, 
Customers.Phone, Orders.OrderDate, Orders.RequiredDate, 
Orders.ShipVia, Orders.ShipCity
FROM   Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
WHERE     (Orders.ShipCountry = N'Germany')

Now click on the OK button in the Query Builder. This will bring you back to an earlier screen as shown in the next picture showing the query string you developed in the earlier screen.

Select the type of report

Clicking on the Next button will bring up the window Report Wizard--Select Report Type. There are only two choices; the default is tabular. This is accepted for this tutorial. The Report Services window appears to show all records embedded in a table when this option is chosen, as we shall see later.

Designing the table: choosing page and grouping

Choose Tabular and click on the Next button. The Report Wizard--Design the Table window shows up as in the next picture. From among the Available fields in the list on the left, you can use the Page>, Group>, and Details> buttons to transfer the desired fields to the right hand Displayed fields' lists. As shown here the page heading will show the ShipCity and the records for each will be grouped by City.

Choose Table Layout

Click on the Next button in the previous screen. This opens the Report Wizard--Choose the Table Layout window. There are two choices for the layout; if you want drill-down capability you place a check mark on the checkbox as shown.

Choose Table Style

Click on the Next button to open the Report Wizard--Choose the Table Style window as shown. There are a couple of canned styles from which you may choose. The Ocean style is chosen; a preview of what it would look like can be seen in the window.

After choosing the style, click on the Next button. This opens the Report Wizard--Completing the Wizard window. This shows the summary for the report.

When you finally click on the Finish button, the report, Report1.rdl, will open in the Layout view as shown in the next picture. You can see that the ShipCity is now in the Page section of the report. The City is shown as the first level of grouping and the rest are in the Details section.

This is not the traditional banded structure present in earlier reporting software. In this view you can choose any object, right click the object to find its properties and change them if you need to. In addition to Layout, you can also access the  Data and Preview tabs.

Simultaneously with the above window, the window shown next, Document Outline, also shows up. This document shows all the controls that you can access on the report.

You may click a control in the Document Outline window to access the corresponding control in the Layout as shown in the next picture for the chosen textbox2.

Now you can access the properties of textbox2 by right clicking on textbox2 in the Layout tab to reveal its properties as shown in the next window. This way you may access any control in the Layout and change its properties.

Also, by hovering over objects with your mouse you can get details from the tool-tip that pops up as shown. This is pretty detailed and gives no room for confusion. Also note the title Report1 has been changed to Multi Table Report.

You can look up the data your report is hooked up with by clicking on the Data tab. This will open up the Query Builder with all its bells and whistles. The toolbar at the top gives you complete access to your data. If you wish to make any changes to the data you can make them here and have them reflected in your report.

When you click on the Preview tab, the program shows a progress bar (rather a circular animation) and after some time the report will be displayed. You can navigate the records and do a number of things by choosing the appropriate menu in the Preview tab.

Deploying the report on the Report Server

When you deploy the report on the Report Server, you will be giving web access to your reports. Since the data is coming from a SQL Server which requires SQL Server authentication, the report is available only to authenticated users. In order to deploy the report, right click on Report1.rdl and from the drop-down click on deploy. This brings up the Microsoft Report Designer message. This message says that the target server (Report Server on which this report must be placed) is not specified.

Click on the OK button to close out of this window. Highlight the Project in the solution explorer and right click to open the project's properties window as shown. Type in the URL as shown in the text box for the TargetServerURL.

The next picture shows the web server on which you may find this server. The report may be targeted to another server if you wish. For the RS, you may find two virtual directories. You may upload a report, or you may place the project on the Report Server.

By accessing the Home page of Reports$Mysorian you may upload the file using the Upload File button. The Report1 !New has been uploaded using this button.

If you click on Report1 !New it will open a screen where you need to provide the authentication information.

After the authentication you can click on the View Report button to open the report as shown, hosted on the Reports folder.

However if you successfully deploy the project, the project files, which may consist of other reports, will be hosted on the Report Server.

Summary

Details of creating a multi-table report using the SSRS have been described. The report accesses the Northwind database on the SQL 2005 server which requires SQL Server authentication. The built-in query designer is extremely user friendly. However, the query designer may not work the same way for non-Microsoft data sources. The formatting of the report is also facilitated by the extensive properties available for the controls in addition to the built-in styles. Microsoft's SQL Server Reporting Services makes rapid enterprise report creation and deployment a whole lot easier by the tight integration of its products.