Introducing Microsoft Reporting Services, Part 2, Page 3
Authoring the report
Let's develop our first report using the Report Designer. To do so, we need to create a new Visual Studio .NET Business Intelligence (BI) project.
Task: Create a Business Intelligence Project
To create a project, complete the following steps (see figure 1.10):
- Open Visual Studio .NET and choose File -> New -> Project.
- From Project Types, select Business Intelligence Projects.
- From Templates, select Report Project.
- In the Location field, enter AWReporter, specify a location, and click OK.
- Once the project is created, right-click on the AWReporter project node in the Solution Explorer window and select Properties. The Property Pages dialog box appears, as shown in figure 1.11.
- Verify that the TargetFolder setting is set to AWReporter. This specifies the folder name in the report catalog where all reports defined in the project will be deployed.
- In the TargetServerURL field, enter the Report Server URL. If RS is installed locally on your machine and you have accepted the defaults during setup, the URL of the Report Server should be http://localhost/ReportServer. Click OK to close the Property Pages dialog box.
Figure 1.10 Use Visual Studio .NET to create a new BI project.
Figure 1.11 Use the report property page to set up the project properties.
Task: Create the Report Data Source
Next, we create a shared data source pointing to the AdventureWorks2000 sample database. Don't worry if the concept of a shared data source is not immediately clear. When we get to chapter 3 it will all begin to make sense.
Right-click on the Shared Data Sources node in the Solution Explorer and choose Add New Data Source. The familiar Data Links Properties appears, as shown in figure 1.12.
Switch to the Provider tab and verify that the Microsoft OLE DB Provider for SQL Server is selected (we will be connecting to a SQL Server database). Back to the Connection tab, specify:
- The name of the SQL Server that you use to install RS. In my case, the database is installed locally, which is why the data source name is "."
- A valid username and password combination for an SQL Server account that has permissions to query the tables in the AdventureWorks2000 database. Select the Allow Saving Password check box.
- Select the AdventureWorks2000 database from the "Select the database on the server" drop-down list. Test the connection by clicking the Test Connection button. If all is well, click OK.
Figure 1.12 Use the Data Link Properties dialog box to establish to set up a data source pointing to the AdventureWorks2000 database.
By default, RS names the data source with the same name as the database. Since we are going to use this data source for most of the sample reports in this book, let's make the name more descriptive.
Double-click on the AdventureWorks2000.rds file. The Shared Data Source dialog box appears, as shown in figure 1.13.
Change the Name property of the data source to AW2000 Shared DS and click OK. Optionally, in the Solution Explorer rename the data source file to AW2000 Shared DS.rds.
Now it's time to author the report. We'll use the handy Report Wizard to save some time.
Task: Set the Report Dataset
- Right-click on the Reports node in the Solution Explorer and choose Add New Report.
- On the Report Wizard welcome screen, click Next.
- On the Select the Data Source screen, make sure that the Shared Data Source radio option is selected and that AW2000 Shared DS appears in the Shared Data Source drop-down list. Click Next.
- In the Design the Query screen, click the Edit button. The familiar query designer window appears.
- Enter the following SQL statement in the query pane:
Figure 1.13 Setting up the shared data source to AdventureWorks2000 database.
SELECT ST.Name AS Territory, PC.ProductCategoryID, PC.Name AS ProductCategory, SUM(SOD.UnitPrice * SOD.OrderQty) AS SalesFROM SalesOrderDetail SODINNER JOIN Product P ON SOD.ProductID = P.ProductIDINNER JOIN SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN SalesTerritory ST ON SOH.TerritoryID = ST.TerritoryIDINNER JOIN ProductSubCategory PSC ON P.ProductSubCategoryID = PSC.ProductSubCategoryIDINNER JOIN ProductCategory PC ON PSC.ProductCategoryID = PC.ProductCategoryIDWHERE DATEPART(YY, SOH.OrderDate) = DATEPART(yy, GETDATE())GROUP BY ST.Name, PC.Name, PC.ProductCategoryIDORDER BY ST.Name, PC.Name
This query retrieves the product sales orders grouped by territory and product category. The AW database groups products in subcategories, which are then rolled up to product categories. For the purposes of this report, we summarize the sales data by product categories since this represents the most consolidated level in the product hierarchy, which is exactly what upper management is interested in seeing. The sales amount is retrieved from the SalesOrderDetail table. In addition, the query filters the orders created for the current year. In chapter 3, we'll make the report parameter driven by allowing the user to pass an arbitrary date. At this point, click Next.
Task: Lay Out the Report
To lay out the report, perform the following steps:
- On the Select the Report Type screen, leave the report type set to Tabular. Click Next.
- On the Design the Table screen, select all fields except ProductCategoryID and click Details so the fields appear in the report details section, as shown in figure 1.14. Click Next.
- On the Choose the Table Style screen, click Corporate, the click Next.
- Finally, on the Completing the Report Wizard screen, enter Sales by Territory as the name of the report. Click Finish, and we're done!
Visual Studio displays the Report Designer with the Layout tab selected, as shown in figure 1.15.
The integration with Visual Studio.NET Report Designer allows you to easily preview and test your reports without leaving the Visual Studio .NET IDE.
Figure 1.14 In the Design the Table step, you choose which fields will appearon the report and how data will be grouped.
Figure 1.15 Use the Report Designer Layout tab to lay out your report.