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.