Summer is a busy time here in the SQL Server marketing team. In June, we successfully negotiated the minefield that was Microsoft Tech ED, Microsoft's premier technical conference. Then, many of us headed directly to Amsterdam to cover Tech ED Europe. Now, we're all back in Redmond, preparing to head out again to the Microsoft Professional Developer Conference (PDC) in September. Working through all of these shows gives our marketing team hands-on experience with a job requirement that is also a common pain point for many of our customers: producing reports.
After each of our shows, our team must produce "trip reports," which summarize conference data. This year at Tech ED, the SQL Server team ran 66 sessions, 14 hands-on labs, and 40 Q&A sessions, and sent approximately 60 members of the SQL Server product team to talk to and hang out with attendees. From the data we collected in all these venues, management wants to know things like how many people attended the SQL Server sessions, the average session score, the name of the top presenter, and that presenter's score. All of this information is stored in a central Tech ED database system that, unfortunately, we are granted only limited access to. So, how do we pull reports with all this information in it? And how do we get all the data into a readable format for our management? Well, once the Tech ED system is switched over to SQL Server 2005, it'll be easy. We'll just use SQL Server Report Builder.
Report Builder is a new product in SQL Server 2005 Enterprise, Standard, and Workgroup Editions that addresses a concern expressed by many of our Reporting Services customers. Although everybody loved Reporting Services, almost everyone wanted an end-user tool for creating reports that didn't require the user to work in Visual Studio. As Figure 1 shows, Report Builder offers a nice, Microsoft Office–like, end-user GUI that lets users create many types of custom report.
Even though users don't have to start Visual Studio, the product still requires a Visual Studio task: Someone needs to define the metadata that end users will work with to create their reports. To define the metadata, you use a new Visual Studio Project type, the Report Model Project, which lets you make a subset of your data available to users of the Report Builder client. Giving end users limited data to work with simplifies the users' job of creating reports. Instead of forcing users to navigate your entire relational or OLAP structure, you give them a Report Model that maps to the way they think about the data.
Creating a report model requires three steps. First, you have to define the data sources that users will work with. The Data Source Wizard lets you to create data sources for either databases or objects in your environment. Once you've defined a data source, you need to create a data source view by using the Data Source View Wizard. Creating a Data Source View is similar to creating a view on the server in that it lets you to the data that SQL Server returns and implement advanced features such as caching metadata, adding relationships, creating calculations, and setting logical keys. The final step is to create the semantic Report Model that end users work with. The Report Model Wizard lets you set rules to control how metadata will be generated from the data source. These rules let you control aspects such as the creation of aggregates on the data, how the data types are formatted, and what language you want the model in. Once you've defined the rules for the model creation, the wizard generates the semantic model. Then, you can view the details of what fields your users will see in Report Builder so that you can ensure the fields are correct.
Before a user can work with the Report Model you've created, you must deploy the model to the server. From within the Visual Studio 2005 interface, you choose to deploy the solution from the Build menu. The model deploys to the Reporting Server where it appears as a Data Source that users can select in the Report Builder tool.
Users launch the Report Builder application from the Reporting Services Report Manger portal. Report Builder is a Smart Client .NET application that uses the new ClickOnce technology to deploy itself to the client machine. Users select one of the predefined report models that you've deployed to the server, then work with a design canvas that lets them create reports based on the available data defined in the report model. Report Builder doesn't save any information to the client machine but works directly with the Reporting Server. When a user chooses to save a report, it resides on the Reporting Server just like any report that you build by using the Report Designer in Visual Studio: it's just Report Definition Language (RDL). The report is also immediately available to other users of the Reporting Service portal as just another report, and users can perform common Reporting Services actions such as subscribing to the report or exporting it to Excel. In addition, a developer can modify this basic user-created report by using the more advanced features of the Report Designer.
As you can see, the inclusion of Report Builder adds an interesting new twist to the reporting world. No longer do you have to try and understand the business needs of your management to create reports for them. Instead, you can set them up with limited access to the data, let them play at creating reports to their hearts' content, then customize the results to make them appropriate for your entire organization. Maybe it even means that you get to go home early some nights?