Getting started with reports

Rick Dobson

Reporting Services is an enterprise-level system integrated with Visual Studio. NET 2003 and SQL Server 2000. It facilitates data-based report creation, publishing, management, and user access. It is a free additional component of SQL Server 2000 provided by Microsoft. Its upgraded version may become a built-in component of SQL Server 2005. Rick Dobson outlines reporting services and explains how to use it to create reports. The online series of reporting services has been completed. It provides a complete overview of this product. This article focuses on the main roles played by Visual Studio. NET when using reporting services to create reports, thus supplementing the built-in documentation.

Although reporting services requires SQL Server 2000, it is not limited to only processing SQL server data. You can create reports based on ole db, ODBC, and ORACLE data sources, or use data in SQL Server databases. As a Visual Studio. NET professional, you may encounter reporting services problems because its report designer is implemented through Visual Studio. NET. Reporting Services supports the XML syntax used to define reports. Therefore, you can use third-party products to create reports. However, the default report designer is Visual Studio. NET.

Reporting Services Overview

In the past few years, most of my experience has been related to Visual Basic. NET and SQL server. Before that, I used access, SQL Server, and VBA for database development. Although access is usually used to generate a report, SQL Server Reporting Services provides a completely different feeling. Access enables Department developers and end users to generate reports with little or no involvement of the IT department. Reporting Services deserves the attention of SQL Server and Windows system administrators. The initial version of Reporting Services manages report access and creation through role-based security. Role permissions are exclusively applied to Windows users and groups.

Reporting Services are extremely rich in functions. It supports multiple report formats and objects. You can use a standard table or a matrix object of a cross tabulation report. The author can also include charts and subreports in his reports. You can selectively enable the trace-Down Function for values in a report and change the report to dynamic by using parameter values. You can even trace from one report to another. There is also a free format for displaying data as a form. You can publish reports in multiple formats, including HTML, Excel, text separated by commas, PDF, Tiff, Office Web Components, and XML.

One important reason why Visual Studio. NET developers are concerned about reporting services is that Visual Studio. NET implements the report designer. After reporting services is installed, you will notice a new project type when you select to start a new project with Visual Studio. NET. The Business Intelligence projects type contains two templates. The Report Project Wizard template generates a set of screens for automatic report creation. Use the Report Project template to generate a blank report in which you can manually set the format. With Visual Studio. NET, you can connect to the data source, develop a query, set the report format based on the query, and deploy the completed report to the Report Server.

Microsoft has created a Reporting Services site (www.microsoft.com/ SQL /reporting/default.asp) that contains many resources to help you quickly master this powerful new feature. You can install reporting services on any computer with SQL Server 2000 installed. You also need to install Visual Studio. NET 2003 on all computers used for report creation. Reporting Services is implemented as a web service. users and administrators can connect to the website http: // hostsitename/reports of this web service. This service automatically discloses the report Manager UI that is advantageous to tasks such as investigation, access, and report management. The function scope exposed through this UI depends on the permissions associated with the Windows User Role connected to the Report Server through the report manager.

Back to Top

Start a Report Project

You can create a report by using the Report Wizard, or manually write a report by starting a blank report. In either case, you must first use Visual Studio. NET to create a new project. Select Business Intelligence projects and click one of the two report templates.Figure 1The Report Wizard is selected for a project named hcvsreporting.

Whether you are using a wizard or manually creating a report, you must specify the data source, query, set the report layout format, preview the report, and edit the report until it meets your requirements, finally, the report is deployed on the Report Server. You can use any method to create a report to achieve these objectives, but the report wizard is especially convenient for beginners.

Back to Top

Create a report listing customers

After the welcome screen is displayed, the Report Wizard allows you to specify a report data source connection. Click the edit button to open the data link attribute dialog box at the top of the wizard screen to select a data source. You can use the "Data Link Properties" dialog box to initially specify a report data source, or edit a data source before exiting the wizard. This wizard allows the report author to type or copy the connection information of a database.Figure 2A "Data Link property" dialog box is displayed, connecting to the northwind database on the local SQL server that uses integrated security.

The next wizard screen allows you to specify a data source-based query to fill the report. You can use Microsoft's common graph query designer or type only one SQL string.Figure 3A query of four columns returned from the northmers table of the northwind database is displayed.

Other wizard screens allow you to set the report layout format. For example, the customer list report in this section uses a table to display customer data in consecutive rows. You can use the built-in report band or add your own band to locate the report data. In the customer list Report, all column values are displayed in the built-in detail band. This rule specifies that all column values are displayed on each row of the table. The penultimate Wizard Page allows you to specify a Report Server and a folder on the server to host the report of a project. Subsequent project reports are stored in the same folder of the server. The final Wizard Page allows you to override the default report name and summarize your selections. Before you click "finish" to create a report, you can return to the previous screen from this final page and edit your selection.

After you click "finish" in the Report Wizard, the focus is back to Visual Studio. NET.Figure 4The "layout" tab of the customerlist report is displayed. Note that two additional tabs provide you with an optional report view. By selecting the preview tab, you can display a report in a way that looks similar to that displayed in a browser. The "data" tab displays the graphical view of the report query. You can edit the query on this tab to select other information about the report.

Figure 4 shows the solution Resource Manager and "properties" window on the right of the report Layout View. You can see that the customerlist. RDL file is selected in Solution Explorer. This file contains the XML of the report. Right-click customerlist. RDL and choose ViewCode"To display the XML of the definition report. The "properties" window shows how to set the selected report object with the selected properties. A report object is a top-level object that contains other objects (such as body, table, and textbox instances used to display labels and column values. You can click a single object on the layout tab to select them, or you can select them from the drop-down list at the top of the Properties window. This function allows the author to modify the report format settings by editing the attribute settings of the selected object.

After fine-tuning a report to display it in your preferred way, you can deploy the report. At first, you can deploy the entire project to the Report Server, which is specified when you first select build | deploy solution to specify the report. This creates a folder for the reports associated with the project on the Report Server. The folder has the same name as the project. You can use report manager to view the contents of the report folder. To open the report manager on the computer on which the Report Server runs, select "all" from the "Start" button of windows.Program| Microsoft SQL Server | Reporting Services | report manager ".Figure 5Displays the customerlist report in the hcvsreporting folder of the home folder on the cab233a Report Server. I installed reporting services on my cab233a computer in my office. The path of the home folder and hcvsreporting folder is highlighted at the top of the report manager, and the report is displayed in the window at the bottom of the report manager.

As the name implies, the report Manager allows you to manage reports on the Report Server. For example, you can select the Excel format for the report in the "select format" drop-down box above the report, and then export the report as a. xls file.

Back to Top

Create Group report

The report designer provides many functions to simplify the report format settings. This section is based on the previous section. It focuses on how to create a report that groups rows in the query. In addition, the discussion on report preparation explains how to apply a special format to a column value, that is, a currency format.

After creating a new project or opening an existing Visual Studio. NET project, you can still call the Report Wizard from Solution Explorer. In Solution Explorer, right-click a report and select "New Report ". This will open the welcome screen of the Report Wizard. The second example report in this article uses this method to start a new report based on the connection to the northwind database. Query reports (as shown in the following code) to obtain the customers and orders tables and order subtotals views.

 
Select MERs. customerid, customers. companyName, [order subtotals]. orderid, [order subtotals]. subtotalfrom customersinner join orderson MERs. customerid = orders. customeridinner join [order subtotals] on orders. orderid = [order subtotals]. orderid
 

This query returns the values of the customerid and companyName columns in the MERs table, as well as the orderid and subtotal column values in the order subtotals view. Although the query returns the result set of the customerid, companyName, and subtotal columns for each unique pair of orderid, the two columns in the order subtotals view are nested in the customers table. The example report in this section highlights the nested relationship by grouping orderid and subtotal values in the customerid and companyName columns of a customer.

The design table page of the report wizard enables the report author to specify the group relationship between the column values in the report in the query.Figure 6The customerid and companyName columns are selected as the columns for grouping one or more orderid and subtotal column values.

Unfortunately, columns in the grouping box are nested by hierarchy. Therefore, although the two columns are in the same group level, companyName is nested in mermerid. You can manually resolve this inappropriate nesting on the "layout" tab of the report. The trick is to move the companyName field to the same group level as the customerid field, and then delete the group level generated by the wizard for companyName.

Because the value of the subtotal column is a currency value, you can apply the currency format to the column value to clearly translate the report value. You can do this by right-clicking the subtotal field in the layout tab. In the "text box properties" dialog box, select "currency" from the "standard" list box ". Click OK ".Figure 7Displays the appearance of the report on the preview tab.

After designing a report, you usually need to deploy it. If you only have one report to be deployed to a folder on the Report Server that already exists in the same project, you can right-click the report in Solution Explorer. Select "deploy" to distribute the report to the Report Server. Select build | deploy solution to deploy multiple reports (possibly other objects) to the Report Server.

Back to Top

Create and use a shared data source

Both reports use their own custom data sources. If a project contains a report set, and each report is connected to a different database, it is particularly suitable to use a custom data source. However, if multiple reports in a project are connected to the same database, it is more efficient to create a shared data source and reuse the shared data source in multiple reports. You can create a shared data source from the Visual Studio. Net project and specify it to a new report created by the Report Wizard or a report created manually. Shared data sources can be deployed on the Report Server like reports. The report Manager Administrator can edit a data source from a custom data source as a shared data source, and vice versa.

By right-clicking "share data source" in Solution Explorer and selecting "Add new data source", you can easily add a shared data source to the project. This opens a "Data Link Properties" dialog box, which is the same as the one opened in the Report Wizard. You can select from the dialog box and click OK to add a shared data source to the project. You can use the context-sensitive "RENAME" menu command or the "properties" Window of the data source to edit the name of the data source. Right-click the data source and select "deploy" to copy the data source from the project to the Report Server. Alternatively, select build | deploy solution to distribute objects from the project to its Report Server. You can assign a deployed shared data source to a report from the report manager. In the example project in this article, I created and deployed a shared data source named northwind. The shared data source specifies a connection to the Integrated Security northwind database.

To assign the northwind shared data source to a report, open the report manager by clicking start in Windows by selecting "all programs | Microsoft SQL Server | Reporting Services | report manager ". In the report manager, click the project folder and then click the report name (such as groupedreport) to receive new data source assignments. This displays the report. Next, select the "properties" tab of the report, and then select the "Data Source" item in the left-side title. This displays a page for specifying a new data source to a report.

Figure 8The "Data Source attributes" page is displayed after the data source is shared by northwind. Recall the example project named hcvsreporting in this article. The report Manager assigns the same name to the folder to save the objects deployed from the project. The "Data Source properties" window displays the selected single-choice button for the shared data source. The name of the shared data source starts with a slash, followed by the folder name, the second slash, and then the project name of the shared data source. Click "application" at the bottom of the page to replace the custom data source created for the report with the shared data source created later.

Back to Top

Manually create a report using a table

To add a new report to a project without using the Report Wizard, select project | add new item and highlight the report icon in the template pane, specify a report name, such as manualreportwithtable. Before setting the report format, you must specify a data source and query the data source. Use name of Reporting ServicesDatasetTo reference the query, but the termDatasetThe ADO. Net DataSet object is not specified. On the contrary, the terms in reporting servicesDatasetSpecifies an SQL statement that returns a row set from the data source.

When installing reporting services, you can choose to install the SQL Server adventureworks2000 database. To show you this example database, the manual report example uses it. You can use the preceding example to create a shared data source for the adventureworks2000 database. After adding a new blank report to a project, you must first assign a dataset to it. This task actually involves two steps. First, you must specify a data source, such as the shared data source of the adventureworks2000 database. Second, you must specify an SQL statement that returns a set of rows with the values of the columns you want to use in the report.

Figure 9The "dataset" dialog box is displayed for specifying the data source and the SQL string specified for the manualreportwithtable report. This query returns the customerid and store name, as well as the full name and phone number of the store contact. In addition, the first letter of the store name is returned as the grouping variable of the row, which is sorted by the store name.

After you specify data for a report, you can set the report format. The layout tab exposes a toolbox that contains items for saving report data, such as the table control. This table is a partition object with a set of built-in table titles, details, and table footer partitions. You can add a group band for the selected fields. The "field" window contains a column of field names. You can use the View | fields command to display it. You can drag the field name from the "field" window to fill in the table cells in a zone. By right-clicking a column or a band border, you can add additional columns or group bands.Figure 10The manualreportwithtable report is displayed. Its table control and some fields are dragged to the built-in band. In addition, you can also see the grouping band of the firstletter field. The entire process of setting the report format is to cross the layout of Windows Forms and access reports.

Back to Top

Summary

Reporting Services provides a new feature for SQL Server 2000. This new SQL Server feature mainly relies on Visual Studio. NET to create reports. Therefore, Visual Studio. NET developers of reporting services can train customers to create their own special reports and create advanced report generation and management functions for their customers. In any case, reporting services increases the value of Visual Studio. Net and the value of developers who know how to use it for organizations with report requirements.