The Visual Studio team has taken a first step toward integrating the BI (Business Intelligence) Report Wizard—a baby step.
Now that Visual Studio 2008 has shipped, you finally can get a firm grip on what's there (and what's still missing). One of my favorite features of Visual Studio 2005 is the ReportViewer control that leverages the considerable work done by Microsoft to develop and refine the Report Definition Language (RDL) first seen in SQL Server Reporting Services. For Visual Studio 2008, this control has been improved to provide compressed PDF output (which can make report deployment faster) and expanded local print functionality. The design-time experience also has been tweaked. There are two new project templates. One creates a blank report that's already bound to the Report Viewer control and the Report Wizard as I discuss in more detail in this article. You'll also find the Expression Editor has been tuned to provide common sample expressions.
The Report Wizard
When you start Visual Studio 2008, you'll notice a pleasant surprise—a Reporting project type and a Reports Application template (in addition to the Crystal Reports Application template)—as shown in Figure 1. Using this project template, you would expect to get a quick start on a new report that leverages the ReportViewer control. Unfortunately, as we'll see later, this wizard leaves something to be desired.
The Business Intelligence Project type does not appear in the Visual Studio 2008 menus. The plan is to re-introduce these when SQL Server 2008 ships later next year. Until then, the only way to interactively create Reporting Services reports is to use the Business Intelligence tools in Visual Studio 2005.
Creating a New Reports Application
Choosing the Reports Application template launches the Report Wizard that should be familiar to those who have run the SQL Server Reporting Services Report Wizard. Generally intuitive, it's designed to mimic the steps developers should take when building a locally-hosted RDLc-based report.
Figure 1: Creating a Report Project using the RDL template.
That is, the wizard walks you through the following steps:
- Select (or create) a Data Source to retrieve data for the report.
- Choose the report type—such as Tabular or Matrix.
- Choose the columns to be included in the report, those used to determine the page and group breaks and the detail columns.
- Define the report layout and formatting.
The end result is a new RDLC file that defines the report as well as a strongly typed TableAdapter that contains the DataSets used to populate the report controls.
Running the Wizard
The first step that creates a new Data Source has not changed from Visual Studio 2005—the wizard simply creates a standard strongly typed Dataset over an entire table and presents the developer with the following (new) dialog as shown in Figure 2. To continue, your only choice is to select one of the DataSets built by the Data Source configuration wizard. No, you aren't given an opportunity to launch the Query Builder dialog—ever.
Figure 2: Choosing a DataSet table.
I'm not at all thrilled with this approach. First, it's rare that a report would be built against an entire table. More realistically, it would be built against a focused rowset returned by the product of a JOIN or at least a parameterized SELECT statement. As it is, there is no opportunity to build a JOIN query or even provide a filtering parameter to focus the rowset on a subset of the table rows—not too clever. A better approach might be to write a special stored procedure that returns the correctly JOINed query product for your report—unfortunately, this approach is crippled as well as I explain later.
The next few dialogs are drawn directly from the 2005 Business Intelligence (BI) addin for Reporting Services. They give you an opportunity to choose the columns to include in the report, and those that determine where to break the pages and groups. Subsequent dialogs help define whether the report should be "stepped" or in "block" mode, whether or not it should include subtotals and drilldown, and the basic color layout—again, these are drawn right out of the SQL Server 2005 Reporting Services Report Wizard.
What's different in this case is that, when the Wizard is done, the finished form already has been customized to include the data-bound ReportViewer control as well as the code in Form_Load to execute the SELECT query and launch the report as shown in Listing 1.
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
'BiblioDataSet.Customers' table. You can move,
'or remove it, as needed.
Listing 1: Code automatically added to the Form_Load event handler.
As I discuss in my lectures and in the long chapter in my book on the Report Viewer control, this is not an ideal place for this code, but I understand that the Wizard does not have a lot of other choices. One of the first chores you should take on is to create a button on the form to execute these two lines of code—one that runs the query, the next that launches the report processor.
Running the Report
Okay, at this point you actually can run the project and see the new report. It might take quite some time to execute because it has to return all of the rows of the target table to the client and construct the report in memory. Think about the steps you need to take to make this a more useful report:
- Focus the rowset used to generate the report. This entails creating a DataTable (strongly typed or untyped) that accepts one or more parameters (or at least a hard-coded WHERE clause) to focus the rowset on the desired data. Ideally, the user should have some control over the data exposed by the report.
- This also means you'll have to code your own UI code to capture and validate the parameters and pass these query parameters to the LocalReport class before rendering the report.
- You'll also want to reformat the columns so that the column widths, numbers, and dates are properly formatted and the groups and page breaks occur where they should. As it is, the wizard does not do much of this housekeeping for you.
Working with Stored Procedures
The problem is that, if you specify a parameter-driven stored procedure to return the rowset, the Wizard does not add the call to the TableAdapter Fill method to the Form_Load event handler. To wire this up, you'll need quite a few more steps and a mechanism to capture the query parameter values at runtime.
It's a shame that the report wizard does not take this fairly basic approach because without it, the Report Wizard makes pretty good demoware, but not much else.
About the Author
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He's worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He's written seven editions of the Hitchhiker's Guide to Visual Basic and SQL Server, and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Bill is a top-rated speaker and frequents conferences all over the world, including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He's also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He's available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.