The Report Designer is used to design reports based on a relational database. The Report Designer provides the functionality to retrieve data from various data sources, design the actual report and, finally, deploy it. The report designer also allows you to preview the report before you deploy to the report server and provide subsequent access to various users. In this section you will be designing a report on Sales of products from the Adventure Works DW database. To design this report, perform the following steps:
Launch the BIDS and create a new Project.
In the Business Intelligence Projects Project Type, select Report Project and provide a new project name, as shown in Figure 17-1.
You will see two folders called Shared Data Sources and Reports in the Solution Explorer window. The Shared Data Sources folder is used to share data sources between multiple reports. Right-click the Reports folder and select Add New Report, as shown in Figure 17-2, to launch the Report Wizard.
If you get the "Welcome" page, dismiss it. The first real page of the Report Wizard allows you to specify the data source. The default data source type is Microsoft SQL Server, as shown in Figure 17-3. Click Edit to specify the data source connection detail.
In this example you will be creating a report based on the Adventure Works DW relational database. Specify the connection details in the Connection Properties dialog as shown in Figure 17-4. Click OK once you have tested the connection to the data source using the Test Connection button.
Click Next to see the Report Wizard's "Design the Query" page; here you will form the query to retrieve data from the relational data source using a query builder. Click Query Builder to launch the Query Builder page.
The default query builder page is a generic query builder that can be used against any relational data source. Click the leftmost icon on the query builder page to switch over to a graphical designer user interface. This query builder has four panes as shown in Figure 17-5.
Initially the Table organizer pane is empty. Right-click within this pane and select Add Table. You will see the list of tables available within the AdventureWorksDW database. Select the FactInternetSales, DimTime, DimProduct, and DimProductSubcategory tables and click Add. The query builder will retrieve the relationships between tables from the database and show them graphically in the Table Designer pane (see Figure 17-6). You can select the required columns from the table designer pane by clicking the check boxes adjacent to the columns. The designer in the Query pane will create appropriate SQL queries. You can edit the selections in the Column chooser pane or the query directly in the query pane. The query used for retrieving the sales information in Figure 17-5 is shown below. Enter the SQL query in the query designer.
SELECT DimTime.CalendarYear, SUM (FactInternetSales.SalesAmount) AS TotalSales, DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName FROM FactInternetSales INNER JOIN DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey INNER JOIN DimTime ON FactInternetSales.OrderDateKey = DimTime.TimeKey INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey GROUP BY DimTime.CalendarYear, DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName
If you already have a SQL query from which you want to create your report, you can type the query in the query pane. The query designer will validate the query. If you click the exclamation (!) icon in the Query Builder page you can see the results of the query in the results pane near the bottom of Figure 17-5. Click OK after you have completed the query and click Next in the Report Wizard.
Next, you need to select the type of report to create; the two forms of report available are the tabular report and the matrix report. In the tabular report, the report contains the values in the row column format similar to the relational tables. The columns have a header corresponding to the column and each row contains the values. In the matrix report there are headers for rows and columns and each cell in the report corresponds to a specific row and column. For example, you can have Time on rows and Cities on columns and the cells will indicate the sales amount of a product or a store for a given time and a city. Select the tabular report type and click Next.
On the Design the Table page, you can choose the results from the data source to be shown in the report as well as how you want to show them. You can move the fields to one of the Page, Group, or Details panes. If you choose a specific field on the page, then for each value of that field a new page will be created that shows the fields in groups and details. A typical example would be to show the sales of products by each year or by each store. In this example you will be creating a report that creates a new page for the sales of products for each year. Select the CalendarYear field and click the Page button. Rows can be grouped based on a specific field. For example, sales of various sizes of televisions in a store can be grouped under a category called TVs. In this example you will be grouping the sales of products based on the subcategory name. Grouping helps you to organize reports for enhanced readability. Select the EnglishProductSubCategoryName and click the Group button. Typically there is a one-to-many relationship between a field in the group and the fields in the details. The fields in the group are shown exactly once in the report. Select the fields for the detail level reporting. Select the TotalSales and EngineProductName fields and click the Details button. Your Design the Table page should look like Figure 17-7. Click Next.
On the Choose the Table Layout page you can choose the layout of the report, specify the visual layout, and include subtotals for groups. The Enable Drilldown option, which allows you to drill down into the details of the report, can be enabled here too. Stepped and block report styles are quite similar; they only differ due to the values being housed in a block. If you click the options you can judge the visual impact of the final report. Select the desired options in the table layout page as shown in Figure 17-8 and click Next.
The next page of the Report Wizard provides you the option to choose predefined report styles or templates. When you select the specific option you can see a preview of the style within the pane on the right side. The slate style is shown in Figure 17-9. Select the template of your choice and click Next.
In the final page of the Report Wizard you can specify the name of your report. Enter the name AdventureWorksDWSalesRelationalReport and click Finish. You have now successfully created your first report using Reporting Services 2005.
The Report Wizard creates the RDL for the report you designed, and you will now be in the Report Designer as shown in Figure 17-10. The Report Editor has three tabs: Data, Layout, and Preview. The Data view is used for editing data sources or the query so that you can modify your report accordingly. The Layout view is the main view where you design your report. The Preview pane helps you to preview the report within the report designer with your current credentials. The list of report items available to build your report is within the Toolbox window. If you do not have the Toolbox window showing, just select the menu item "View" and click on "Toolbox" under that.
Now that you have created the report, you no doubt want to see a preview. Click the Preview tab to view the report you have created. Figure 17-11 shows the preview of the report you have created. This report spans multiple pages, one page for each Calendar Year. You have controls to move between various pages. Within each page you can see the product subcategories along with the sales information. Because the report is grouped and you selected the drilldown option, the default view of the report does not show all the details. You can click the + sign associated with a product subcategory to see the details of the total sales of that product category.