Enhancing a Visual Basic 6.0 Application with the ReportViewer Control

  • Article

Sales Support: A Hybrid Visual Basic 6.0 & Visual Basic 2005 Application, Part 3

Scott Swigart

February 2008

Summary: This article, the third in a four-part series, shows how to include modern, simple-to-configure Microsoft Visual Basic 2005 reporting components within an existing Microsoft Visual Basic 6.0 application. (13 printed pages)

Contents

Introduction

The Sales Support Application

Reporting

Application Architecture

Visual Basic .NET Implementation

Interoperability with Visual Basic 6.0

Conclusion

Introduction

Do you want to modernize your Microsoft Visual Basic 6.0 applications? Would you like to take advantage of Web services, desktop search, and more, but you don't have the time or resources to upgrade (or rewrite) your production applications in Microsoft Visual Basic 2005?

In Part 1 of this article series, Enhancing an Application with Windows Desktop Search and Office Outlook Integratione, I showed that it is not necessary to migrate a Visual Basic 6.0 application to Visual Basic 2005 in order to take advantage of .NET functionality. Instead, you can integrate Microsoft Windows Desktop Search into a Visual Basic 6.0 application by using COM objects that have been created with Visual Basic 2005.

In Part 2, Enhancing a Visual Basic 6.0 Application with MapPoint Web Service, I demonstrated how to enhance the same application to display maps for locations in an existing database by calling into Microsoft MapPoint Web Service.

This hybrid approach lets you take advantage of all of the functionality of the .NET Framework without having to rewrite your application.

This article focuses on what is often an afterthought to developers, but is top-of-mind to users: reporting capabilities. Instead of struggling with the more limited reporting capabilities of Visual Basic 6.0, you will see how to include modern, simple-to-configure Visual Basic 2005 reporting components within an existing Visual Basic 6.0 application.

The Sales Support Application

"Sales Support" is a very simple sales-force support application that allows the user to look up accounts, contacts, and forecasted sales. The application is not intended to be a complete sales-force automation solution. Instead, it is designed to be a representative placeholder for an existing line-of-business Visual Basic 6.0 application. Also, it serves as the anchor for a number of logical application enhancements that are best implemented by using Visual Basic 2005.

Installation

The Sales Support installer has been tested on Microsoft Windows XP SP2 and on Windows Vista, and likely would work on older operating systems, too. Because this is a hybrid Visual Basic 6.0 and Visual Basic 2005 application, the Sales Support installer will install the.NET Framework 2.0 as a prerequisite. This application also stores data in a back-end database. For this reason, the installer will install Microsoft SQL Server 2005 Express Edition, which replaces Microsoft SQL Server 2000 Desktop Engine (MSDE 2000).

If you do not have these prerequisites installed, the installer will download them for you automatically. If they are already installed, the installer will detect their presence and skip to the application installation.

Operation

After the application is installed, you can launch it from the Start menu. Look for the Sales Support8_3 entry. The application will initially prompt you to log on. To simplify use, the user name and password fields default to working values, as shown in Figure 1. You can just click the Login button to continue.

Figure 1. The Sales Support Login form

After you have logged on, the main application window appears, as shown in Figure 2. The application provides three different views of the sales information. The Accounts tab provides the first view and shows each account and associated sales information. This view can be sorted differently by clicking on a column heading. The Contacts tab provides the second view and shows more detailed information about each individual contact. The Forecast tab provides the third view and displays future sales-forecast information.

Figure 2. The main application window

Again, the Sales Support application is not designed to be a working sales-force solution. Instead, it serves only as a basis for realistic application enhancements.

Reporting

Most line-of-business applications are "forms-over-data" applications that serve as a way to enter, edit, view, and visualize discrete pieces of information that is stored in back-end databases. Of course, users also want to see this data in the aggregate; for this reason, reports are a key component of most line-of-business applications (although they often are developed as an afterthought in version 1.0 of an application). Reporting has been available for Visual Basic through options such as Crystal Reports, the Data Environment, and a rich ecosystem of third-party reporting tools.

With Visual Studio 2005, Microsoft has released the powerful new ReportViewer control, which allows you to create reports and view them in your application. The ReportViewer control supports interactive reports, printing, layout control, and exporting options.

To add new reports to a Visual Basic 6.0 application, you could continue to use the tools that shipped with the product in 1998. However, the users of our fictional Sales Support application demand the "latest and greatest," so that the ReportViewer control was the best choice. This control can be added easily to a .NET form; also, by using the Interop Forms Toolkit 2.0, this form can be launched from the existing Visual Basic 6.0 application. Figure 3 shows the resulting report for accounts.

Figure 3. Accounts report

You can access this report in the sample application via the new Reports menu item that includes Account, Contacts, and Forecast reports.

Application Architecture

The core of the Sales Support application is a Visual Basic 6.0 application that communicates with a SQL Server 2005 Express database. When the user selects a report from the new Reports menu, the Visual Basic 6.0 application creates an instance of the .NET report form as though it were a COM object. The Visual Basic 6.0 application passes the report data to the .NET report form as an ADO Recordset object. The .NET report form converts the Recordset object to a DataSet object. This is necessary because the ReportViewer control cannot bind to an ADO Recordset object, but it can bind to an ADO.NET DataSet object. The report is then displayed as shown in Figure 3. (For more information, please see "Sharing ADO Recordsets Between Visual Basic 6.0 and Visual Basic 2005," in the MSDN Library.)

Figure 4. Application data flow

Visual Basic .NET Implementation

Extending the Visual Basic 6.0 Sales Support application requires a Visual Basic .NET Framework Class Library project that is called VBNET_Extensions. This project accepts a Recordset object from the Visual Basic 6.0 Sales Support application and converts it to a DataSet object that is suitable for reporting. The DataSet object provides a visual designer that allows you to create table definitions to hold the data. By looking at the designer, you can see that this DataSet object is configured with tables that map to the data that the Sales Support application will provide.

Figure 5. DataSet designer

If you are completely new to the .NET Framework, a DataSet object is a big change from the four main ADO objects—namely, Recordset objects. A DataSet object could be described as a collection of hierarchically related Recordset objects (DataTables), disconnected from the database, but accompanied by associated Command objects to handle data manipulation within the database. What is yet more interesting, a DataSet object can be filled with data from sources other than a database. In this example, the DataSet object is filled with data from ADO Recordset objects.

The Contacts, Account, and Forecast tables hold data that is supplied from the Sales Support Visual Basic 6.0 application. The Visual Basic 6.0 application passes in Recordset objects that contain this information, and the VBNET_Extensions Class Library copies that data from the Recordset object into these DataTable objects. The TopAccounts and TopForecast tables are designed specifically for charts that appear in some of the reports. These charts show the top 10 accounts, in terms of recent sales and forecast sales.

After the DataSet is created, you're ready to create the actual reports. Many reporting options have been available for Visual Basic; however, with Visual Studio 2005, Microsoft has introduced the ReportViewer control, which is an all-in-one reporting tool that simplifies the building of powerful and interactive reports. This is a .NET control, of course, so that it has to be placed on a .NET form that is launched from the existing Visual Basic 6.0 application.

Creating a .NET form that is accessible from Visual Basic 6.0 requires the Interop Forms Toolkit 2.0, which is a free and supported Visual Basic 2005 PowerPack that makes .NET forms appear as COM objects in Visual Basic 6.0 applications. After you install it, a Visual Basic 6.0 InteropForm is added to the Visual Basic .NET Framework Class Library, as shown in Figure 6.

Figure 6. Adding a Visual Basic 6.0 InteropForm

The InteropForm is just like any other .NET form, except that it contains a few extra items that make it COM-callable. After the form has been added to the project, place a ReportViewer control from the Data section of the toolkit onto the form. The ReportViewer control has a small smart tag in the top-right corner that is used to dock the ReportViewer control in the form. Docking a control will cause the control to fill the available space, and resize automatically as the form is resized—a challenging task in Visual Basic 6.0. The smart tag is used also to create a new report definition. You can see the smart tag accessed in Figure 7.

Figure 7. Creating a new report from the smart tag

With the report designer open, the toolkit updates to contain a number of items that are specific to reports. For this application, tables and charts are the two main items that are used. Drag a table from the toolkit onto the report-design surface. As soon as the table is in place, drag individual columns from the Data View window onto the table to bind table cells to the DataSet columns, as shown in Figure 8. You can also drag columns onto the chart to specify from where the data values and category names should come.

Figure 8. Building the report

To make the report more readable, set the BackgroundColor property of the table cells to the expression =iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White"), which will alternate the background color of each line of the table. Right-click the chart, and then select Properties to display the custom property pages for the chart, as shown in Figure 9. These property pages allow you to customize the chart—for example, by setting the chart title, or by formatting the x-axis labels as currency.

Figure 9. Chart-property pages

The final step is to connect the ReportViewer control to the SalesSupportDataSet.

To interact with the Sales Support Visual Basic 6.0 application, the application requires a way to pass a Recordset object into the AccountReportForm. This step is accomplished by exposing a property on the report form, as shown in Listing 1.

    Private mRecordset As Object
    <InteropFormProperty()> _
    Public Property Recordset() As Object
        Get
            Return mRecordset
        End Get
        Set(ByVal value As Object)
            If (value IsNot mRecordset) Then
                mRecordset = value
                SalesSupportDataSet.Account.Clear()
                Dim da As New OleDbDataAdapter()
                da.Fill(SalesSupportDataSet.Account, mRecordset)
                GenerateTopAccounts()
                AccountReportViewer.RefreshReport()
            End If
        End Set
    End Property

Listing 1. Recordset property on the Visual Basic .NET form

The property uses a .NET Framework OleDbDataAdapter object to copy the contents of the Recordset object into the DataSet's Account DataTable object, which was created earlier in the DataSet designer. The magic of taking data from Visual Basic 6.0 and ADO and transferring it to an ADO.NET DataSet comes in one line of code:

da.Fill(SalesSupportDataSet.Account, mRecordset)

The DataAdapter does all of the work for you.

Some of the reports also include charts, which show "top-10" information. You use a function to generate the data for these reports. For the Accounts report, you accomplish this by using the GenerateTopAccounts function that is shown in Listing 2.

    Private Sub GenerateTopAccounts()
        Dim bs As New BindingSource(SalesSupportDataSet, _
            SalesSupportDataSet.Account.TableName)

        bs.Sort = SalesSupportDataSet.Account. _
            sales_last_3_monthsColumn.ColumnName & _
            " asc"

        SalesSupportDataSet.TopAccounts.Clear()
        For i As Integer = bs.Count - 11 To bs.Count - 1
            Dim accountRow As SalesSupportDataSet.AccountRow
            accountRow = bs.Item(i).Row
            SalesSupportDataSet.TopAccounts. _
                AddTopAccountsRow(accountRow.CompanyName, _
                accountRow.sales_last_3_months)
        Next
    End Sub

Listing 2. GenerateTopAccounts function

The GenerateTopAccounts function uses a .NET Framework BindingSource object to sort the accounts by sales. The first 10 rows are then copied from the BindingSource object into the TopAccounts DataTable. Because the chart already was bound to this table, the chart just populates itself and shows the top accounts.

Interoperability with Visual Basic 6.0

As noted earlier, the Interop Forms Toolkit 2.0 allows you to build forms in Visual Basic .NET that can be called from Visual Basic 6.0. The Interop Forms Toolkit 2.0 installs a Visual Basic 6.0 InteropForm item that was used to build the report forms. After the reports are created, select Tools | Generate Interop Wrapper Classes, as shown in Figure 10, to generate COM wrappers that are needed for Visual Basic 6.0 to interact with the .NET form.

Figure 10. Generating COM wrapper classes

After the wrapper classes are generated, compile the solution by using the Build | Build Solution menu command.

With the Sales Support application open in Visual Basic 6.0, add a reference to the VBNET_Extensions COM object. Visual Basic 6.0 code can create an instance of this object and call into it, as though it were any other COM object. Listing 3 shows the Visual Basic 6.0 code that passes the account data into the Visual Basic .NET report form.

Private Sub mnuAccounts_Click()
    Dim f As New VBNET_Extensions_AccountReportForm

    If rsAccount Is Nothing Then
        LoadAccountData
    End If

    f.Recordset = rsAccount
    f.Show
End Sub

Listing 3. Passing a Recordset object into a Visual Basic .NET report form

You can see that only a tiny amount of code was required in Visual Basic 6.0 to trigger the new Visual Basic .NET functionality.

Conclusion

Visual Basic .NET is a great choice for many new application scenarios. Microsoft continues to pour its efforts into enhancing the .NET Framework and related technologies. At the same time, many organizations are maintaining significant Visual Basic 6.0 applications. As this article has shown, there is no reason that you cannot create hybrid applications by using the best of both technologies.

About the author

Scott Swigart spends his time consulting, authoring, and speaking about converging and emerging technologies. With development experience going back over 15 years, and by staying in constant contact with future software-development technologies, Scott is able to help organizations get the most out of today's technology while they prepare to leverage the technology of tomorrow. Scott is also the author of several .NET books, a certified Microsoft trainer (MCT) and developer (MCSD), and a Microsoft MVP. Feel free to contact the Scott at scott@swigartconsulting.com, or check out his latest musings at blog.swigartconsulting.com.