Since initial launch of SQL Server 2000 Reporting Services in January 2004, I've had the opportunity to give more than 100 demonstrations of the product. One piece of feedback often received from customers was Reporting Services needed an end-user reporting tool. Microsoft listened to the request and created Report Builder, a new, ad hoc reporting tool in SQL Server 2005 Reporting Services.You use Visual Studio 2005's Business Intelligence Development Studio—a set of project types within Visual Studio 2005—to create and customize a report model (the underlying metadata layer that makes end-user reporting possible). Once a model is set up your end users use Report Builder to author their own reports. Report Builder is designed to let users who don't have a lot of database experience create reports without having to understand how to build a query to extract data.
I've divided the process of creating a report into three phases: creating a report model in Visual Studio 2005 (which includes setting up the data source, the data source view, and the report model), deploying the report model to a report server, and authoring a report. You'll need these tools: Visual Studio 2005, SQL Server 2005 Reporting Services, and .NET Framework 2.0.
Phase 1: Create a Report Model in Visual Studio 2005
Similar to other SQL Server 2005 components, you start by using Visual Studio 2005 to create a new project for your report model. Choose Business Intelligence Projects as the project type and use Report Model Project as the template.
Create a data source. Once you create a new project, the next step is to define one or more data sources in the Report Model Project. Report Builder currently supports SQL Server version 7.0 or higher as a data source. Analysis Services 2005 is also a valid data source (though you won't see this as an option in a Report Model Project—for more information see the sidebar "Creating Report Models"). To create a data source, right-click the Data Source folder in the Solution Explorer window to start the Data Source Wizard. For this example, use the AdventureWorks sample relational database that ships with SQL Server 2005.
Create a Data Source View. A Data Source View (DSV), as the name implies, is a way to select a subset of tables and views from the data source.To create a data source view, right-click the Data Source Views folder in the Solution Explorer window and select the Add New Data SourceView menu item to start the DSV Wizard. The wizard lets you select tables and views from a target database. After completing the wizard, you can add calculated columns to an existing table or view, or create entirely new views (called Named Queries to avoid confusion with actual views that already exist in the database). I'll select all of the tables and not make additional customizations at this time.
Create a report model. A report model is a metadata description of a data source and the relationships between its entities. This is where the fun begins. To create a report model, right-click on the Report Models folder in the Solution Explorer window and select the Add New Report Model menu item to start the Report Model Wizard. When the Select Data SourceView page appears, select the appropriate data source view. When the Select report model generation rules form appears, leave the default settings that Figure 1 shows. Although the wizard provides a description for each rule, here are a few key points:
- Entities and Attributes. Tables, views, and named queries are entities in a report model (and in the Report Builder client). For example, the first two rule options relate to how the wizard will create an entity based on the underlying Data Source View. Table columns are attributes.
- Aggregates. By default, the Report Model Wizard will automatically create sum, minimum, average, and maximum aggregations for numeric attributes; the wizard can also create date aggregates and counts that represent the number of unique instances within an entity.These aggregations provide additional information that's useful from a reporting and analytical standpoint (e.g., the number of orders you had last month).
- Roles. A role in a report model refers to relationships between entities—a report model role shouldn't be confused with a security role. When an end-user creates a report, Report Builder uses these roles to automatically filter entities, which makes it easier for the end user to navigate and select related items in a report model.
After you choose rules, the Report Model Wizard prompts you to collect Model Statistics. The wizard uses Model Statistics to generate and set default properties in a report model. The data-model generation process stores these statistics in the data source view. For example, the Report Model Designer counts the unique instances of each entity and uses these counts to determine when to display a drop-down list of values in a parameter list versus forcing the user to search for a value.
Finally, on the Completing the Wizard page, enter a report model name and click Run to complete the wizard. The wizard builds the model, which can take several minutes depending upon the size of the report model.The wizard makes two passes at the source database before it displays the output of the report model. During the first pass, the wizard processes the rules responsible for creating entities, attributes, date variations, aggregations, and roles. The second pass pertains to advanced rule processing, which I don't cover in this article.
On the Report Model output window that Figure 2 shows, scroll down to find Status in the left pane and you'll see the warning message that Figure 3 shows. This message brings up an important point: If a primary key doesn't exist for a table, you should set a primary key for underlying tables so that each row of data is uniquely identified when you run a report in Report Builder.You can set a logical primary key by using the Data Source View Designer; highlight one or more columns and select the Data Source View-Set Logical Primary Key menu item.
Figure 2 shows the Report Model output window; entities are in the left pane and attributes and roles are in the right pane. Behind the scenes, report models are defined by using an XML language called Semantic Model Definition Language (SMDL), so the file extension for report model files is .smdl.
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."
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.
