Building a Multi-table Report with SQL 2005 Reporting Services - Creating a query
(Page 4 of 6 )
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.
Next: Designing the table: choosing page and grouping >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 13 
