Phase 2: Deploy the Report Model to a Report Server
Now that we've selected a data source and created a data source view and a report model, it's time to set some deployment options and deploy the data source and report model files to a report server.To set deployment options, right-click the Project entity in the Solution Explorer and set TargetServerURL to the URL of your Report Server (e.g., http://localhost/ReportServer). If you've used SQL Server 2000 Reporting Services before, you'll notice a new deployment option, TargetDataSourceFolder, that makes it easier to centralize and reuse data sources across different projects.
Phase 3: Author a Report
Now we're going to switch hats and play the part of an end user. In your browser, type the location of the machine on which Reporting Services is running (e.g., http://localhost/Reports) to bring up the Report Manager. Next, start the Report Builder client by clicking the new Report Builder button in the Report Manager. Report Builder leverages the new Visual Studio 2005 ClickOnce deployment model, which means that Report Builder will automatically install itself on the end user's machine. (Remember, Report Builder requires .NET Framework 2.0 to be installed on the client machine.)
Select a data source and design the report layout. You need to select a data source and a report layout (i.e., table, matrix, chart) before you generate a report—Report Builder doesn't support free-form reports. For the first sample report, I selected the Example1 data source and a matrix report layout to display sales data across time.Then I clicked OK.The Explorer pane in Figure 4 lists the model entities in the Entities list box. (If you don't see the Explorer pane, select View, Explorer from the Report Builder toolbar.) Select the Sales Territory entity in the Entities list box. Notice the Fields list box displays fields that are specific to Sales Territory.
Select the Group field and drag it to the row groups section of the report layout window, as Figure 4 shows. Alternately, you can double-click the Group field to get the same result. Notice that after you put the Group field in the report layout window, Report Builder filters the Entities list to show only entities related to Sales Territory. This filtering feature makes it easier for an end user to navigate through a report model, and the filtering also prevents a user from joining unrelated entities together. Place the Name field to the right of the Group field in the row groups section in the report layout window.
Next, in the Entities list box under the SalesTerritory entity select the Sales Order Header role, as Figure 5 shows. In the Fields list box, expand the Ship Date field, and drag the Ship Year attribute to the columns groups section, which is on the right side of the report layout window. Finally, we'll add numeric data to the totals section in the report layout window. In the Fields list box, drag the Total Sub Total field into the totals section in the report layout window, as Figure 5 shows.You can format the numeric data by selecting all 6 numeric fields at one time, right-clicking, and selecting Format. Enter a report title. The report layout should look similar to Figure 5.
Run a report. Click Run Report in the Report Builder toolbar to run the report. When you look at the output, you'll notice that Report Builder automatically adds some interesting details: It totals and calculates row and column subtotals and totals, adds row-level sorting to the Group and Territory fields, and dynamically generates hyperlinks for each Sub Total value. Move your cursor over a Sub Total, click on the hyperlink, and a new report (named Sales Order Headers) displays.This report doesn't exist; Report Builder creates it on the fly by inferring the relationships between report entities. Similarly, you can select a Sales Order Number to display a new report, then click #Sales Order Details, and so on.This feature provides end users with an "infinite drill-through" facility to explore the report data.
Save a report. To save the report to the report server, select File, then Save from the Report Builder menu. After the report has been saved, you can view, manage, and secure the report as you would if you used Visual Studio 2005 Report Designer to build the report.
Create a Report by Using the Table Report Layout
Now let's use the table report layout to generate a second report, and this time we'll select settings that will return a list of Sales Persons by Territory for a given Territory Group. I've chosen this report to highlight a few areas we need to address in our initial Report Model. Select File, New from the Report Builder toolbar. Select the Example1 data source and a table (columnar) report layout. Locate and select the Sales Person entity in the Entities list box and drag the # Commission Pct field into the column fields section in the report layout window. (If you don't see this field, expand the Total Commission Pct group.) As before, Report Builder filters the Entities list to show only the entities related to Sales Person. Drag the Total Sales Quota and Total Sales YTD fields to the right of Commission Pct in the report layout window.
Next, in the Entities list box, select Sales Person under the Sales Person entity—this role is a pointer to the Employee entity, but the Report Model Wizard named the role Sales Person. Under the Sales Person role, you'll see another list of roles. Select the Contact role and drag the Last Name and First Name entity fields to the left of the Total Commission Pct field in the report layout window. In the report layout window, right-click each gray column tab and deselect the Show Group Sub Totals option to remove the subtotal field associated with the First Name and Commission Pct fields.
Finally, we'll add a drop-down box so you can filter the report by Sales Territory. Click the Filter icon in the Report Builder toolbar (or select Report, Filter in the Report Builder menu); the Filter Data window appears. In the Entities list box, select the Territory role (under the Sales Person entity) and drag the Name field into the Filter pane. Select Northwest from the drop-down box, right-click Territory in the filter pane, and select the Prompt menu item. You'll see a green question mark next to Territory which indicates you can configure this filter when you view this report. Now your report should look similar to Figure 6.
What could we have done in our model to make this report easier to author? For starters, it would have saved time to include an Employee's Name field in the Sales Person entity. A Full Name field would have been nice, as well, to make it easier for us to display the person's complete name without worrying about extra Sub Totals. Although Report Builder lets an end user format numeric values, it would be nice if Report Builder formatted these values for us.
I'm sure you can think of other improvements, but we have enough to get started with. To continue learning how to organize and customize entities, combine attributes, and change format properties, see the Web-exclusive sidebar "Refining a Report Model," http://www.sqlmag.com, InstantDoc ID 50048.
Report Builder is a welcome addition to Reporting Services. Although I think it's unlikely that IT Professionals will ever get out of the report-writing business entirely, I think Report Builder empowers users who are familiar with data at a business level, but aren't comfortable using Visual Studio 2005 to build a report.