Build SQL Server Reports Quickly
Use the SQL Server Report Designer's wizards for a streamlined solution to setting up, designing, polishing, and publishing reports.
- By Stacia Misner
- 09/01/2004
Technology Toolbox: SQL Server 2000
Editor's Note: This article is excerpted from Chapter 3 of Stacia Misner's upcoming book, Microsoft SQL Server 2000 Reporting Services—Step by Step [Microsoft Press, ISBN: 0735621063]. .
SQL Server Reporting Services includes the SQL Server Report Designer, whose wizards give you a streamlined solution for setting up, designing, polishing, and publishing reports. Authoring a report starts with defining a SQL Server Reporting Services data source, which packages information about the location of the data your report uses. It also defines the credentials users must have before they're allowed to access that data. You must know which server hosts the data and which database or file stores it, and you need to have the right credentials and permissions to retrieve that data. You must define at least one data source for each report you author.
Next, you create a data set for the report, including at least one query, using the appropriate language and syntax needed to retrieve data—Transact-SQL (T-SQL), if you're accessing a Microsoft SQL Server database. A data set also includes a pointer to the data source and other information used when the query executes. The Report Project Wizard lets you define only one data set, but you can access multiple data sets in one report if you forego the wizard and use T-SQL (see Table 1).
Finish constructing your report by creating a report layout, which is the design template SQL Server Reporting Services uses to arrange and format the data. The report layout includes the structure, or data region (such as a table or matrix), where data is placed when the report is processed. You can define style properties for each section of a data region, including font, color, and format, and you can set these properties for report items as well, including the report title in a textbox, or the report background. You can control the look and feel of your report precisely.
The Report Project Wizard and the Report Wizard walk you through each step. They help you create Microsoft Visual Studio containers to hold your new report, project, and solution. Name these containers and give them a storage location on your computer's hard drive or on a network file share.
Fire up the Report Project Wizard, then Visual Studio. Go to the Visual Studio File menu, point to New, and click on Project. The New Project dialog box appears. View the available templates by clicking on the Business Intelligence Projects folder. Click on the Report Project Wizard, then on the More button at the bottom of the dialog box if it's visible. Name the project Adventure Works for the purposes of this exercise. The text in the New Solution Name box of the New Project dialog box changes to match the project name as you type along. Change the solution name later if you wish.
Give the project this location: c:\rs2000sbs\Workspace. Click on OK to bring up the Report Wizard's Welcome page (which you can disable). The Report Wizard differs from the Report Project Wizard, which lets you create a solution, a project, and a report in one step, then launches the Report Wizard. You can use the Report Wizard at any time to add a report to an existing project using a wizard interface. Click on Next.
Connect to a Data Source
Now specify connection information to the server and database hosting the data. You can also supply credentials information for Reporting Services to use for authentication when querying the database.
Create a data source that defines a connection to SQL Server's rs2000sbsDW database, using Microsoft Windows authentication. Name the data source rs2000sbsDW. Click on Microsoft SQL Server in the connection type listbox (the other choices are OLE DB, Oracle, and ODBC). Type a connection string manually, or click on the Edit button to use the Data Link Properties dialog box, which generates the connection string automatically.
By default, only the current report can use the data source you create here. This lets you manage its usage separately from other reports. On the other hand, you can share your data source with other reports by selecting the checkbox at the bottom of the dialog box. Doing so simplifies data source management.
Click on Edit, which brings up the Data Link Properties dialog box. Your previous choice of Microsoft SQL Server as the connection type means the wizard selects Microsoft OLE DB Provider for SQL Server as the default data link. Type localhost or the name of the SQL Server instance in the "Select or enter a server name" box. Click on "Use Windows NT Integrated Security." Pick rs2000sbsDW in the "Select the database on the server" listbox. Click on Test Connection to make sure you can connect to the rs2000sbsDW database, then close the confirmation dialog box. Click on OK to close the Data Link Properties dialog box. The connection string generated for your SQL Server data source should look like this:
data source=localhost;initial
catalog=rs2000sbsDW
You can enter a connection string for a data source manually as long as it uses the syntax of the database to which Reporting Services connects.
Now click on Credentials to bring up the Data Source Credentials dialog box. Here's where you can opt to override the authentication method you specified in the Data Link Properties dialog box. Otherwise, you can skip this step. Authentication methods include Windows Authentication, a single user's credentials, a prompt at run time for the user's credentials, or no credentials at all.
Next, the Report Wizard asks you to design the query to be displayed in the report. The query must conform to the relational database syntax you defined in the data source—otherwise, the wizard will balk.
Your query for this exercise summarizes the Adventure Works sales for each employee by year, sales territory group, and sales territory country. Start Microsoft Notepad and open the Sales Summary.txt file in the C:\rs2000sbs\chap03 folder. Copy the query from Listing 1 and paste it into the "Query string" box on the Report Wizard's "Design the Query" page. Alternatively, you could click on the Edit button to open the Query Builder and create a SELECT statement using a graphical interface. This works like the Query Builder in SQL Server Enterprise Manager.
Your query is one of several items stored in a data set, which is a container for a pointer to the data source and the query you design. In general, you can type a query directly into the "Query string" box, use the Edit button to open the Query Builder, or paste in a query that has been tested first in Query Analyzer or saved in a file.
Click on the Next button. The wizard validates the query against the data source and displays an error message if it runs into a problem, such as an invalid column name. You must fix the problem before you can continue to the next stage: designing your report.
Design a Report
The design process starts with selecting a report type that defines how the data is structured in the report. Then you arrange the data within the selected structure and finish the design by applying a style template. The wizard doesn't give you much latitude about layout and style, but you can adjust those later before actually publishing the report.
You can choose between a tabular or a matrix report type; choose a tabular type for this exercise. The wizard now enables the Finish button. At this point, you've created a basic report, ready for publishing. You can click on the Finish button and make modifications later using the Report Designer, but in this case, you'll continue designing your report with the wizard.
The report type defines the structure, or data region, of the data returned by your query. The Report Wizard lets you present this information as either a table or a matrix, though you have more options outside the wizard. The main difference between these two types of data regions is the number of columns. A table has a fixed number of columns; a matrix has a variable number determined by the query results.
Now arrange the data within the report type you selected. This process determines how data is grouped and the order in which it's displayed. Think of grouped data as the vertical sections of a report (although you can display groups next to each other), and the data order as the sequence in which the data is presented in the same row, vertically for groups and horizontally for columns.
Your query produces five fields. Arrange them to build a report displaying the ActualSales amount for each Employee as details, grouped by SalesTerritoryGroup and SalesTerritoryCountry, with a page break for each CalendarYear. Click on CalendarYear, then click on the Page button to place the CalendarYear field in the Page section of the "Displayed fields" list (see Figure 1).
Each time you place a field in a display section, the corresponding section in the sample table is highlighted to show you where the field will appear in your report. Each column of data returned by the query is linked, or mapped, to a report field displayed in the "Available fields" list until that field is assigned to a section of the data region. The fields appear in the "Displayed fields" list when assigned to a data region's section. The assigned section determines whether the report displays detail rows, aggregated rows, or both types of rows.
You can assign fields to the Page, Group, or Details section of the report because you're using a tabular report type. For example, a field assigned to the Page section isn't included in the table in the report, but instead goes in a textbox positioned at the top-left corner of the report. Each distinct value for a page field creates a page break in the report. The wizard uses fields added to the Group section of the report to break the table into separate sections. These can include subtotals by section.
Table Your Field Values
Table rows are built from field values assigned to the report's Details section, with one table row for each row returned by the defined query. You can select the "include subtotals" option to sum up the Details section's numeric fields into the subtotals, and decide later whether you want to hide report details and display only summary information.
Field assignment differs slightly for the matrix report type. The wizard still includes the Page and Details sections, but sections for columns and rows replace the Group section. You need to assign at least one field to each of these sections to build a matrix (or crosstab).
Now click on SalesTerritoryGroup, then click on the Group button to place the field in the Group section. Repeat for SalesTerritoryCountry. You could use the drag-and-drop feature instead, moving fields from the list of available fields to the appropriate sections.
The order in which you add fields to each section determines the sequence in which data displays in the report, though you can alter the sequence subsequently. The fields in the Group section display in order from top to bottom or from left to right, depending on the style template you select in a later step in the wizard. Fields in the Details section display in columns in order from left to right. Drag ActualSales to the Details section (the bottom section of the Displayed Fields list), then drag Employee to the same section. Click on Employee, then click on the Up button to move Employee above ActualSales. You can also use drag-and-drop to rearrange fields within a data region.
Now you make your last design decision for your report: applying a style template. This defines the report's look and feel. Start by defining a block layout for the table, which includes group subtotals, and selecting the Bold style template.
Select a report style and click on Block. The sample layout changes to give you a preview of the block layout. Here you're choosing a layout style for the tabular report that controls the placement of detail rows relative to aggregated rows on the report. You can also choose to include subtotals or enable drilldown. The difference between the layout options becomes clearer when you can actually view your report. At that time, you'll see examples of the other layout styles for comparison.
This page of the Report Wizard asks you to select either a stepped or block report layout style. The stepped layout arranges each distinct group value on its own row and in its own column. This is the only layout that also provides a drilldown option (to display hidden detail data). The block layout is more compact—you place data in each column, starting a new row only for additional detail rows within the same group or for a new group value.
Select the Include Subtotals checkbox. Click on the Next button. Click on each table style to preview the style in the "Choose the table style" page. Assigning a style template to a tabular or matrix report sets the overall color theme and font usage for the report. Finally, set the style for your report by clicking on Bold.
Preview, Revise, Preview Again
You've defined where to find the data, what data to include in the report, and how the data will look. Now specify deployment information: a URL on the Report Server to host the report, the folder into which the report will be placed on the server, and a name for your report. You also get to review a summary of the selections you made throughout the wizard and see a preview of your report.
This step of the Report Wizard sets the project properties; it doesn't actually deploy, or publish, the report to the Report Server. The default folder has the same name as your project, and is created when you deploy the report if the folder doesn't exist already. The wizard doesn't validate the server name, so deployment will fail ultimately if you enter an incorrect server name. However, you can update Project Properties in the Solution Explorer if this occurs.
Click on the Next button, then type Sales Summary in the "Report name" box. You might overwrite an existing report deployed on the Report Server if you use the same name and deploy the report to the same folder. You won't get a warning message first, so be careful about assigning names and folder locations to reports.
Review your selections by scrolling through the information in the "Report summary" box. Preview your report by selecting the Preview Report checkbox. The Report Designer displays your report in layout mode if you don't do this, so you can edit it further, but you can switch to preview mode by clicking on the Report Designer's Preview tab.
Check column size during your preview. The columns all default to the same size and probably won't be wide enough for data. Also, you might need to adjust formatting for numeric values. You can fix these problems using the layout mode, then review the fixes using the preview mode.
This completes your work with this wizard. Later, you can use Report Manager to update the report's description and execution properties, explore the report online, and export it as a Microsoft Excel file.
You can preview your report from the Report Wizard's last page or from the Report Designer's Preview tab. Either way, your query executes and the query results are stored in a data set and assigned to fields. The report is then rendered according to the assignment of the fields to the data regions you specified as well as to the layout and style you selected. The report format and the report data are merged to produce the preview (see Figure 2). You can interact with the report as if it were published to the server, allowing you to test the results before making it available on the Report Server.
The preview for this exercise should display the CalendarYear field in the top-left corner. Just below it you can see the column names in the table header with the details displayed in rows by groups. The first group is SalesTerritoryGroup; the second is SalesTerritoryCountry. These fields are defined as groups, so their values display only in the first row of details within that group. For example, you can see actual sales amounts for employees Garrett Vargas and José Saraiva for Canada in North America. The row beneath these details is the group subtotal for Canada.
You Can't Go Back
You can't return to the wizard to make layout changes once you close it. Instead, you must create a new report. Actually, you might find this easier to do than making changes to the layout directly in the Report Designer. For example, you might use the wizard to create a stepped report with subtotals (on the "Choose the table layout" page) using the same query. This layout puts North America in its own row, including its subtotal on the same row. Then Canada appears by itself on the next row, followed by the detail rows. This report style is longer than the block layout.
Alternatively, you could use the wizard to create a report with a stepped layout and drilldown selected (see Figure 3). Drilldown lets users click on the plus sign to expand the report and click on the minus sign to collapse it. The drilldown report default is to show it completely collapsed when it is opened.
Scroll to the bottom of the first page, where the group subtotal for the United States is shown. Beneath this group subtotal you can see the group subtotal for SalesTerritoryGroup, including both Canada and the United States. Click on the Next Page button on the Preview toolbar to view the page for 2002. You assigned CalendarYear to the Page data region of the report, so each page contains data for a separate year. You can use the page buttons on the Preview toolbar to navigate between pages, or you can type in the page number you want to view.
Scroll to the bottom of the second page to see the layout when there are multiple values for SalesTerritoryGroup, then scroll back to the top of the page. Scroll horizontally to see the Actual Sales column if you can't see the full width of the report. Unfortunately, the text wraps in the SalesTerritoryCountry, Employee, and ActualSales columns, and the format of the ActualSales values could be improved.
Now switch from preview to layout mode to fix these problems. Layout mode lets you adjust every property of every element in the report, giving you complete control over everything you can see. You can switch back and forth between layout and preview mode easily to test the results of your changes progressively.
For example, you need to fix column sizes and data formatting in the report layout. Click on the Layout tab. Rulers appear both above and to the left of the report layout. Use these as a visual guide when making changes to the report, such as when resizing report items or positioning new ones. Click on any cell in the table to display column and row handles. The column handles are the shaded cells appearing above the table; the row handles are the shaded cells with icons, shown to the left of the table. Use these handles to modify the table properties.
Edit Your Table Properties
Position your cursor between the second and third column handles, then click and drag to widen the second column, Sales Territory Country, to approximately 1.5 inches. You can drag the column only when the cursor is positioned properly and changes to a double-headed arrow. Making the column bigger eliminates the text-wrapping problem, but also requires you to have some idea of the maximum length of the data that could appear there.
Position the cursor between the third and fourth column handles, then click and drag to widen the Employee column to about 1.75 inches. Right-click on the fourth column handle, above Actual Sales, to select the entire column, then click on Properties. Visual Studio displays the Properties window for the selected column (TableColumn4). Scroll through the Properties window to find the Format property, then type C0 in the Format property field to format the field as currency with no decimal places.
Use .NET numeric and date-string formatting to control display of the data. Click on the Preview tab to see the modified report. Now you've solved the text-wrapping problem and improved the Actual Sales column formatting. Your first report is ready for publishing to the Report Server, where users can use it.
Start the actual publishing process in Visual Studio's Solution Explorer window. Right-click on the Adventure Works solution (not the project) at the top of the tree, then click on Properties. The Solution "Adventure Works" Property Pages dialog box is displayed. Click on the Configuration Properties folder in the left pane. The project context is displayed in the right pane. Verify that the Deploy checkbox is selected. Click on the OK button, and click on Deploy Solution on the Build menu.
The Output window displays the progress of deployment. Deployment of the solution is complete when you see messages in the Output window announcing that the build and deploy operations succeeded.
You don't have to deploy an entire solution. You can also deploy a single report or multiple reports within a project or solution. You publish a report by using one of your deployment options to transfer it from Visual Studio to the Report Server.
Also, you can still change reports after they're published, using the Web application called Report Manager supplied by SQL Server Reporting Services. But first things first: Now you can use the Report Project Wizard to whip out the report your users wanted yesterday.