Introduction to SQL Server Report Builder
By now you probably know that there are a lot of fancy new high-end features coming in Microsoft SQL Server 2005. But in its quest to conquer the data center, Microsoft hasn't forgotten the end user. In fact, there are more new features for desktop users and business analysts than ever before. One good example of these features is Report Builder. For the first time, SQL Server is shipping with an end-user reporting tool right in the box. After a little setup by the DBA, your end users can create their own attractive and informative browser-based SQL Server reports (assuming you've installed the Reporting Services component of SQL Server). There was nothing comparable in previous versions of SQL Server, and the new capability looks likely to be a big hit.
Setting up a Data Model
Report Builder doesn't give end users carte blanche to explore a SQL Server database - most DBAs would be horrified at such a thought. Instead, it depends on the notion of a data model, a preselected group of tables and relationships that the DBA has identified as suitable for ad hoc reporting. The first step in preparing for Report Builder, then, is to set up a Data Model. To do so, you launch SQL Server Business Intelligence Development Studio. This is the all-purpose IDE for BI programming tasks in SQL Server 2005, derived from the Visual Studio 2005 codebase. The first step is to create a new Report Model Project; I named mine AWSales.
The new project will display three empty nodes in Solution Explorer: Data Sources, Data Source Views, and Report Models. To populate the project, I followed these steps:
- Right-click on Data Sources and select Add New Data Source. Use the Add New Data Source Wizard to add a connection to the local copy of the AdventureWorks database.
- Right-click on Data Source Views and select Add New Data Source View. Use the Data Source View Wizard to add a view based on the Adventure Works data source.
- In the Data Source View Wizard, select the Production.Products and Sales.SalesOrderDetail tables, then click the Add Related Tables button to bring in the Sales.SalesOrderHeader and Sales.SpecialOfferProduct tables.
- Right-click the Report Models node and select Add New Report Model. In the Report Model Wizard, select the Adventure Works data source view and click Next. The next step is to select the rules that will be used to generate the report model, as shown in Figure 1. I used the default rules. It's these rules that create the entities that will be available to end users for reporting.
- Click Next, choose to update the database statistics, and click Next again to finish the Wizard and build the report model.
At this point, the report model is complete, but it's not yet available to end users. The final step is to select Deploy from the Build menu to push the report model to the Reporting Services server. This publishes the report model so that it will be available to end users in Report Builder.
Launching Report Builder
Report Builder itself is a ClickOnce WinForms application. That means that end users install it from the browser, but once installed it's not a browser-based application. To get started with Report Builder, browse to your Reporting Services home page - typically, this will have a URL something like http://MySqlServer/Reports (or http://localhost/Reports if you're running the browser on the same box with SQL Server 2005 itself). Click the Report Builder link in the home page menu bar to install and launch Report Builder.
Report Builder will load with the New Report dialog box visible, showing all the available report models. Select the Adventure Works model and click OK. Figure 2 shows the result: Report Builder ready to build a report.