Reporting in Visual Studio .NET

Crystal Reports is a third-party product licensed by Microsoft and provided as part of Visual Studio. A separate version (Version 9 as of this writing) is available from Crystal Decisions, and as you might imagine, it has features not included in the Visual Studio version. But the version that's included with Visual Studio has pretty amazing capabilities, and you may never need more than what comes right out of the box.

Building Your First Report with Visual Studio .NET

Crystal Reports provides two mechanisms for building reports. The Report control is the simpler technique. You drop it on a form, set a few values, and you're done. For more complex reports, the Crystal Report object gives you an almost unlimited range of possibilities.

Let's start with a simple report from the Customers table of the Northwind database. Start by creating a Windows application called SimpleReport1. In the code to accompany this book, it's in the Chapter10Code directory. Right-click on the project name in the Solution Explorer, choose Add, Add New Item from the context menu, and then select Crystal Report from the available choices. Call the report SimpleReport1.rpt and click on Open .

From the list of available templates, you'll begin to see the range of possible reports that you can build. The defaults, Use the Report Expert (wizard) and Standard, are fine for now. Click on OK to continue.

For the data source, select OLE DB(ADO), and then pick Microsoft OLE DB Provider for SQL Server. Next , fill in the Connection Information dialog with the server name (local), the user ID (sa), the password (blank), and the Database name (Northwind). (If you need to use a different UserID and password, use them here.) You don't need to change anything in the Advanced Information dialog that follows in most cases. Expand the tree view to show the table names and select Customers.

By this time, the Standard Report Expert dialog will look like Figure 10.13.

Figure 10.13. The Standard Report Expert dialog before you select a table.

Click Insert to select the Customers table, and you'll advance to the next dialog, which lets you pick the fields to be included in the report. Double-click on the CompanyName , ContactName , City , Country , and Phone fields to select them, as shown in Figure 10.14.

Figure 10.14. Selecting fields for your report.

Grouping is a desirable feature in many reports. It's very easy and shows off a few cool Crystal Reports features. Select Country as the Group By field as shown in Figure 10.15 and click Next.

Figure 10.15. Selecting a Group By field.

In the subsequent Total (Summary Information) dialog, select Country from the Summarized Fields list, and pick Count from the Summary Type combo box. Don't select any option from the next dialog, which asks whether you want to sort by the subtotals you just selected. As tempting as it is, skip it for now.

Finally, you'll see that a graph has been created based on your selection of subtotals including counts. It's free, although you can suppress it if you want. Finally, when the dialog appears to name the report, enter "Customers by country" as the report title and click Finish.

The designer will display your report at this point, as seen in Figure 10.16.

Figure 10.16. The Crystal Reports Designer.

The mechanism used to display this report is the CrystalReportViewer , and it needs to be hosted in a form. So, select a CrystalReportViewer from the Windows Forms toolbox (use Ctrl+Shift+X if it's not already visible) and drop it on the default form that was added to the project. Name it Viewer1, set its Dock property to Fill , and its ReportSource property to the name of the report you just created (browse to the name, or just type it in). Press F5 to run the app. Couldn't be simpler. The resulting report is partially shown in Figure 10.17.

Figure 10.17. Previewing the report.

If you use Ctrl+Alt+L to open the Solution Explorer and click on the View All Files icon, you'll see that below the SimpleReport1.rpt entry there is a SimpleReport1.vb class file. The CrystalReportViewer allows you to refer to this file, which is called a strongly typed report (because it's a class definition) in a way that's useful for building a generic report preview screen.

PickAReport.vb is such a form. It consists of a form, a CrystalReportViewer component docked at the bottom of the screen and leaving a half-inch or so at the top, and a combo box at the top of the form. In the Items collection, I've added two entries: "<Pick a report>" and "SimpleReport1". The second entry could also be "Customers by country with graph." The code for the combo box's SelectedIndexChanged event is coded with a reference to the class name, as shown in Listing 10.9.

Listing 10.9. Using a ComboBox to Select the Report to View

 Private Sub ComboBox1_SelectedIndexChanged( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles ComboBox1.SelectedIndexChanged     Select Case ComboBox1.Text         Case "<Pick a report>"             CrystalReportViewer1.ReportSource = Nothing         Case "SimpleReport1"             CrystalReportViewer1.ReportSource = New SimpleReport1     End Select End Sub 

Adding Login Information

If you want to point to a different database, or use a different userID and password combination, include the code in Listing 10.10 before assigning the ReportSource .

Listing 10.10. Logging on to the Database in Code

 Dim Tab As CrystalDecisions.CrystalReports.Engine.Table Dim LogonInfo As CrystalDecisions.Shared.TableLogOnInfo For Each Tab In SimpleReport1.Database.Tables     LogonInfo = Tab.LogOnInfo     With LogonInfo.ConnectionInfo         .ServerName = "localhost"         .UserID = "sa"         .Password = ""         .DatabaseName = "Northwind"     End With     Tab.ApplyLogOnInfo(LogonInfo) Next Tab 

Putting the User in Control

If you want to offer users options at this point, all you have to do is drag a ReportDocument from the Components toolbox onto the form and assign the strongly typed report class to it. Figure 10.18 shows the dialog that appears when you put the ReportDocument on your form.

Figure 10.18. Adding a ReportDocument to the form.

The default name is ReportDocument1 . Change the name to rcSimple , and then comment out the previous assignment of the strongly typed report to the ReportSource and add an assignment of the report component instead, as shown in Listing 10.11.

Listing 10.11. Changing the Report Selection Code to Permit Dynamic Report Selection

 Private Sub ComboBox1_SelectedIndexChanged( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles ComboBox1.SelectedIndexChanged     Select Case ComboBox1.Text         Case "<Pick a report>"             CrystalReportViewer1.ReportSource = Nothing         Case "SimpleReport1"             ' CrystalReportViewer1.ReportSource = New SimpleReport1             CrystalReportViewer1.ReportSource = rcSimple     End Select End Sub 

This runs exactly as it did before. However, the ReportViewer now references an object that can be instanced from anywhere ”a class in the application's assembly, an external assembly, or an XML Web Service.

Report Management and Use with the Server Explorer

Use Ctrl+Alt+S to open the Visual Studio Server Explorer. The Server Explorer can be used to create connections, create databases, add tables, and generally do many of the things that we've always been able to do in FoxPro, but which required the Enterprise Manager in SQL Server. MSDE didn't have a user interface, which made using MSDE relatively difficult (a nice way of saying "not worth the trouble"). The Server Explorer fixes that and provides many other features as well.

One of them is something you might have noticed if you've opened the Server Explorer before. If you expand the Servers node, the first entry is Crystal Services. And one of the nodes directly below Crystal Services is Server Files. Server Files shows the contents of a directory in your Visual Studio directory tree; on my computer it's this:

 Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Reports 

Any directories under the Reports directory will be shown and their contents displayed in the expandable tree under the directory name. My computer has the two directories (Feature Examples and General Business) that were installed with Visual Studio. Add a My Reports directory and copy SimpleReport1.rpt there, and you'll see it as shown in Figure 10.19. You may have to click the Refresh icon at the top of the Server Explorer window.

Figure 10.19. Displaying your reports in the Server Explorer.

Create a new form called ReportFromServerExplorer and make it the current project Startup form. Use Ctrl+Alt+S to open up the Server Explorer and expand the tree until you see your SimpleReport1.rpt under the My Reports node. Drag it onto the new form. It is given the default name ServerFileReport1 . Next, add a CrystalReportViewer control onto the form, change the Dock property to Fill (the big box in the center of the Dock property dialog). Finally, open the ReportSource drop-down in the CrystalReportViewer object and select this:

 ServerFileReport1 [CrystalDecisions.Shared.ServerFileReport] 

The reason this is interesting becomes apparent if you add a server. Right-click on the server node and select Add, and a dialog appears asking for the computer name or URL. That means that you can add reports to your application that are located on other servers. That considerably expands what you can offer your users. We'll see more examples shortly.

Using a Dataset as a Data Source

Now that you know how easy it is to populate a dataset, you'll be pleased to know that you can design your report to get its data from an ADO.NET dataset instead of from a database using an OLE DB provider.

To create a strongly typed dataset based on the Customers table in Northwind, add a new empty dataset to the project using the Add New Item dialog. Name it dsCusts.xsd . This puts you into the Dataset Designer, which looks just exactly like an empty Web page or Web service page. Actually, it's more like MODIFY STRUCTURE .

Open the dataset's designer by double-clicking on the dataset's name in the Solution Explorer. Then, drag the Customers table from the Server Explorer (opened with Ctrl+Alt+S) onto the design surface of the dataset, and save it using File, Save All (Ctrl+Shift+S).

If you click on the Show All icon at the top of the Solution Explorer window, you'll see that under the dsCusts.xsd file, there are two other files named dsCusts.vb and dsCusts.xsx . These three files constitute a typed dataset.

First you'll need to create a report using this dataset. Select Add New Item, Crystal Report from the Solution Explorer right-click context menu. In the resulting Standard Report Expert dialog, on the Data page, click on the Database Files node of Available data sources. You'll be given a chance to browse to your new dataset, dsCusts.xsd . Click on it and continue with the steps to design the same report layout we designed earlier.

Next, open the new report and right-click on its design surface to get a context menu, then select Database, and then from the submenu select Set Location. In the Set Location dialog, expand the Project Data node, the ADO.NET DataSets node, the node for your dataset, and select the Customers table (see Figure 10.20).

Figure 10.20. Adding the Customers dataset.

Your report will now look to your strongly typed dataset for its metadata and will run when bound to an instance of it. Click the Close button to proceed.

Now that we have a report to run against dsCusts , drag the Customers table from the Server Explorer onto the designer surface of the form. This will add SQLConnection and SQLDataAdapter objects to the form that points to the Northwind database and its Customers table, respectively. Rename these objects scnNorthwind and sdaCustomers , modify the ConnectionString property of scnNorthwind to include a valid user ID and password, and then right-click sdaCustomers and select Generate Dataset. In the Choose a Dataset section, select the Existing radio button, and the class name of your strongly typed dataset from the combo box (see Figure 10.21).

Figure 10.21. Pointing to the dataset.

Click the OK button and add the following code to the Load event:

 sdaCustomers.Fill(DsCusts1, "Customers") datasetReport1.setdatasource(DsCusts1) CrystalReportViewer1.ReportSource = datasetReport1 

How It Works

This code first populates dsCusts1 , an instance of the dsCusts strongly typed DataSet class. Next, the code sets the DataSource property of DatasetReport1 , which is an instance of the strongly typed report class SimpleReport1 , to dsCusts1 . Finally, it points the ReportSource of CrystalReportViewer1 to DatasetReport1 .

If you change the Startup page of the project to DatasetReport and press F5 to run it, you'll see that the report looks the same as before. However, this time the data doesn't come straight from a database, but rather from a DataSet object. The DataSet was populated by a SELECT statement from the Customers table. The SELECT statement and the connection string could have referenced any server and any tablename, as long as the dataset has the same structure. The dataset is like a FoxPro cursor; it's just a container for the data. The Fill method of the DataAdapter just pours the data into it.

Report Filtering with Parameters

Crystal Reports knows how parameters work and will automatically provide a parameter input form that users can fill in. It's primitive, but it's free. You can also write your own dialog screens and specify the parameters yourself. The description process is going to be very visual, so please forgive all the pictures.

Creating a Parameterized Report

To build a parameterized report, create a new Windows Form project called ReportWithParameters. Right-click on the project and select Add New Item; click on Crystal Reports, name the new report CustomersByCountry, and click OK.

You'll immediately see the Standard Report Expert dialog, which starts by asking you where the data is. Select the OLE DB(ADO) node, as shown in Figure 10.22, and click on the plus sign to the left of it as if to expand it.

Figure 10.22. The Data Selection page of the Standard Report Expert.

Clicking on the plus sign when you haven't yet selected a data source produces the OLE DB Provider Selection Expert (wizard), as shown in Figure 10.23. You'll want to select Microsoft OLE DB Provider for SQL Server, as shown in Figure 10.23.

Figure 10.23. Selecting the driver to use for the database connection.

Click Next and you'll be sent to the next page to fill in the server name, the userID, the password, and the name of the database, as shown in Figure 10.24. Click Finish to complete the process.

Figure 10.24. Specifying the database connection information.

When you've done this, you're ready to select one or more tables for your report. Expand the Tables node and double-click on Customers to select it (see Figure 10.25).

Figure 10.25. Selecting the Customers table.

On the Fields page, select CompanyName and Country (see Figure 10.26).

Figure 10.26. Selecting fields from the Customers table.

Based on your selections, Crystal then builds the report for you (see Figure 10.27). It's actually a pretty attractive report. You can select from several alternative designs, and the report expert will make the required changes. So far, so good. You can also add or change labels, such as the report heading in the Page Header band .

Figure 10.27. Viewing and modifying the generated report.

Adding a Parameter

The next step, though, is somewhat unexpected. You might imagine that you would go to the Select tab and enter parameters. The Select tab is used to enter hard-coded selection criteria for building a SELECT statement without any parameters. If you want the user to supply the parameter, there's another way.

Press Ctrl+Alt+T to open the Field Explorer, and right-click on the Parameter Fields node as shown in Figure 10.28, where I've right-clicked on the node to activate the context menu.

Figure 10.28. The Field Explorer.

TIP

You can also click on the Toggle Field View icon, the first one in the Crystal Reports toolbar, to show or hide the Field Explorer.

Select New, and you'll get the dialog shown in Figure 10.29. Enter mCountry for the parameter name, Country to include as the prompt, and String as the type. There are a few more options, but you probably won't use this except for prototyping, so don't get too excited.

Figure 10.29. Specifying the new parameter.

Using the Parameter

The mere fact that you have a parameter doesn't do anything. You have to use it in a selection formula. To do this, right-click anywhere on the form that's not a field or a label, and select Report, Edit Selection Formula, Records from the context menu, as shown in Figure 10.30.

Figure 10.30. Adding a selection formula.

Crystal Reports is criticized for having a quirky formula language of its own. In the Edit Selection Formula dialog, you pick a field from the Field Tree window by double-clicking on it. The formula construction process is visible at the bottom of the screen. Double-click on the Operator Tree in the rightmost window and pick the "=" operator, which is added to the selection formula. Finally, go back to the Field Tree and pick the parameter field mCountry by double-clicking on it. As expected, it will be added to the end of the formula. (I've displayed the Function Tree as well, although we don't need it for this example.) The result, (Customers.Country) = (?mCountry) , appears at the top of the Edit Selection Formula screen.

Close the dialog, add a CrystalReportViewer to the form, dock it with the Fill option, and designate the new report's name in the CrystalReportViewer's ReportSource property. Press F5 to run the form. Lo and behold, it prompts you for a country, and displays a report containing only that country's records.

XML Report Web Services

Reports can be published as Web Services and then used as the ReportSource for a CrystalReportViewer control. This allows you to offer report processing on the server. That doesn't buy you anything if you're printing a complete listing of a file; but if you're building a summary report, it allows you to use the entire database on the server, but send only the summary data over the Internet. Bandwidth savings can be huge.

To make an XML Report Web Service from SimpleReport1.rpt , create a new ASP.NET Web Service project named SimpleReportWebService1. Delete the Service1.asmx file that is added to the project by default, and add SimpleReport1.rpt to the project.

Next, right-click SimpleReport1.rpt in the Solution Explorer, and choose Publish as Web Service from the context menu.

This adds a Web Service file, SimpleReportService.asmx , to the project.

Now build the project. Open a browser and type the following URL:

 http://localhost/SimpleReportWebService1/SimpleReport1Service.asmx 

You'll get a Web Services testbed like we saw in earlier chapters. Exposed methods include the following:

DrillGraph

Refresh

FindText

GetPage

GetTotaller

TestReport

GetGroupLevelData

FindGroup

GetReportInfo

Export

You can't test them in the Web page because they're meant to be used from within a Windows form to manipulate and return elements of the report. The ReportViewer control knows what to do with them, and that's what matters.

To use this Web service in a form, back in the Windows Forms application, in the Load event, set the CrystalReportViewer control's ReportSource to the URL of the Report Web Service, as shown in Listing 10.12.

Listing 10.12. Pointing the ReportSource to a Web Service

 Private Sub CrystalReportViewer1_Load( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles CrystalReportViewer1.Load With CrystalReportViewer1   .ReportSource = "http://localhost/SimpleReportWebService1/" _                 + "SimpleReport1Service.asmx" End With End Sub 

You can add a Web reference for this .asmx file to the project. Having done this, you can assign a new instance of the Web Service class to the ReportSource :

 CrystalReportViewer1.ReportSource = New localhost. SimpleReport1Service() 

Try them both. It's really that easy.

In order to make this look easy, however, the folks at Crystal Decisions, or perhaps in Redmond, have done a lot of work. Essentially, two classes have been created in the codebehind file for SimpleReport1 .rpt:

 Public Class SimpleReport1 ... Public Class CachedSimpleReport1 

The first one is the primary class for implementing the strongly typed report. It inherits from ReportClass and wraps various members of the ReportDefinition.Sections collection. The second one implements the ICachedReports interface. It creates a cached report. In its CreateReport method it creates an object based on the SimpleReport1 class. On the Web Service side, there are also two classes, SimpleReport1Service , which inherits from ReportServiceBase , which exposes the methods that publish the report as a Web service, and CachedWebSimpleReport1 , the caching counterpart .

When this .asmx file has been generated, all you have to do is copy it, together with the related RPT file, to your Web Services directory.

To use these reports, just provide a configuration file (either a TXT or an XML file) that your application can read to determine the IP address of the reports server. You then construct the ReportSource URL at runtime.

Building ASP.NET Reporting Clients

Although this book is primarily about Windows application development, I've included a short chapter on building thin client applications because they are part of the competitive environment. Some developers out there are offering thin client as the solution for any type of database application.

The good news is that thin client applications are simple. The bad news is that thin client applications are simple. For simple applications, they're not hard; for complex applications, they're not appropriate. That about covers the controversy.

However, the one area that seems to be an excellent fit for using Internet browsers, either on the Internet or on an intranet, is reporting. Reporting is one-way, which is perfect for use with a browser.

Create a new ASP.NET Web Application in Visual Studio .NET, and use the Add Existing Item feature to add SimpleReport1.rpt to the project. Make sure that the report is looking to the Customers table for its data.

Next, rename WebForm.aspx to CustomersByCountry.aspx , and add a Web Forms CrystalReportViewer control to it. Unlike the Windows Forms CrystalReportViewer control, the Web Forms variant doesn't have a ReportSource property that's exposed at design time. You can only set it in code, usually in the Page_Load event:

 CrystalReportViewer.ReportSource = Server.MapPath("SimpleReport1.rpt") 

Run the application and you'll see that it works as before. The output sent to the browser is, of course HTML; but search, navigation, zoom ”all of the features except printing ”are supported through the use of trips back to the server.

Exporting to a PDF

If you want users to be able to print a copy of the report, browser output is usually not a very good option. A better one is to export your report to Adobe Acrobat (PDF) format. You simply export the report to a PDF file and redirect the browser to that same file. Listing 10.13 shows the code to do this.

Listing 10.13. Exporting a Report to a PDF

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As _   System.EventArgs) Handles MyBase.Load     Dim destopts As New _       CrystalDecisions.Shared.DiskFileDestinationOptions()     Dim FName As String = GUID.NewGUID().ToString     destopts.DiskFileName = FName     With cbsMain  ' Name of the CrystalReport object         .ExportOptions.ExportDestinationType = _           CrystalDecisions.Shared.ExportDestinationType.DiskFile         .ExportOptions.ExportFormatType = _           CrystalDecisions.Shared.ExportFormatType.PortableDocFormat         .ExportOptions.DestinationOptions = destopts         .Export()     End With     Response.Redirect(FName) End Sub 

Because several users may request the report at the same time, you need to provide unique names for PDF files. I used the NewGUID() method of the GUID class that comes with .NET. (I had to use the ToString cast because technically, a GUID is a type and a string is a different type.)

Other Report Experts

So far, we only looked at the Standard Report Expert. Crystal Reports comes with seven experts:

Standard

Form Letter

Form

Cross-Tab

Subreport

Mail label

Drill Down

Each of these walks you through the process of building a slightly different kind of report. The wizards permit you to build a variety of display output from your data with little or no coding. We've already looked at the standard wizard, so we'll now look at samples of some of the other types of report output produced by the wizards.

Create a Windows Form project called OtherReports. Change Form1.vb to MainForm.vb , size 790 x 510, no MinButton or MaxButton , autocentered on the screen, and with a fixed 3D border. Add a MainMenu control with a File, Exit option, and then add a Reports pad with three menu bars: Form Letter, Cross-Tab, and Drill Down. (These three are pretty interesting and will illustrate how the Report Experts work.) To make the code easier to read, you might want to select Show Names from the menu's context menu and assign meaningful names. Now you're ready to build some new types of reports.

The Form Letter Wizard

Form letters (called mail-merges in the FoxPro world) are a useful addition to many database applications. They're easy to do in FoxPro, and they're easy to do in .NET.

Add a new Windows Form called frmFormLetter. Make it 780x500 and sizeable, no MinButton or MaxButton , autocentered. Add a CrystalReportViewer control and dock it to all sides. Next, right-click on the project and select Add New Item, Crystal Report. Select Form Letter from the Experts dialog, as shown in Figure 10.31. Enter the data connection information for the Northwind database, and then select the Customers table.

Figure 10.31. Running the Form Letter Expert.

We're going to do a form letter to all customers, so all we really need are the fields that are going to be printed on each page. So include the fields shown in Figure 10.32.

Figure 10.32. Selecting the fields to be printed in the form letter.

The rightmost tab of the Expert page is specific to the Form Letter expert. On it, you select the band in which to put the content (select Detail), and then type text and insert field placeholders to be filled in with data from each selected record when the report is run. Figure 10.33 shows the detail band I built for the form letter; save the report.

Figure 10.33. The finished form letter in the Form Letter Expert dialog.

When you close the expert, you'll be looking at the report. The text and placeholders that you created are by default inserted into the report's Detail band (Section 3).

We have one more thing to do. If you consider that the form letter will try to fit multiple objects on a single page if they're small enough to fit, you'll understand why you need to either expand the detail band to 9 inches in height or do something else. Something else is preferable in this case; right-click on the Detail Band and you'll see the Properties sheet shown in Figure 10.34. Check the New Page After option, and you'll get exactly one letter per page.

Figure 10.34. Forcing a new page after the detail band for a record prints.

Finally, go to MainForm and add the following two lines of code for the mnuFormLetter menu code:

 Dim frm As New frmFormLetter frm.Show() 

CAUTION

There's a gotcha here that got me, so I'll try to save you the pain: Don't name your form and your report both FormLetter, even though they have different file extensions. The reason for those endless object prefixes in the Visual Studio .NET world (besides the fact that because a form in Visual Studio .NET just has an extension of .vb or .cs , you can't tell if a file is a form or not without the prefix) is that the filename is usually used as the default class name. Because both the report and the form will be converted to classes, you'll end up with two FormLetter classes.

Figure 10.35 shows a sample page produced by a form to which I've added a CrystalReportViewer component with the ReportSource set to the form letter report created previously. When you run a form letter, the entire batch is printed in a preview format; you can then send it to the printer.

Figure 10.35. The finished form letter in the Form Letter Expert dialog.

Mail merges are a part of many database applications. FoxPro has a pretty good mail-merge feature, but .NET's implementation is at least as easy to use, and maybe even a little easier.

The Cross-Tab Expert

Add a new Windows form called frmCrosstab to the project. Drag a CrystalReportViewer object onto it and dock it to all sides. Next, right-click on the project in the Solution Explorer and select Add, Add New Item, Crystal Report. Select Cross-Tab from the Expert dialog.

The report then asks you to select a table. The Products table has some useful fields to tabulate, so I'll use it. Pick the Supplier ID and Category ID as the row and column tabs. But there's no meaningful value to add; adding quantities of different items is literally adding apples and oranges.

However, if we multiply on-hand quantity times unit price, we have an extended value that can be totaled. You can open the Formula editor, create a "formula" called Extended, select UnitPrice from the list of table fields, type in an asterisk, and then select UnitsInStock from the list of table fields (Figure 10.36). You can then save the formula and use it in the cross-tab.

Figure 10.36. Defining a "formula" (a calculated variable) for use in the report.

In Figure 10.37, I've entered the two control fields at the top and left, and the value to be totaled in the Summarized Fields box.

Figure 10.37. The finished cross-tab in the designer.

The finished product appears in Figure 10.38. For good measure, I included a graph that the expert offered me at no extra charge.

Figure 10.38. The cross-tab report.

The difficulty of building cross-tabs has long been a frustration for database developers. If the Cross-Tab Expert were the only feature in Crystal Reports, it would be an important tool. The fact that it's just one of many types of reports that Crystal can build is a tribute to how far this product has come.

The Drill Down Expert

It's easy to create links in Web pages that pass parameters to other pages in such a way as to give the illusion of "drilling down" into the data. This feature is one that users are accustomed to, and ask for in our applications. Unfortunately, it's not as easy to do in Windows Forms applications as it is in a Web page. But Crystal Reports' Drill Down Expert builds reports that do just that. I've added a new form called frmDrilldown with a CrystalReportViewer control on it, docked to all sides as usual. In Figure 10.39, I've added a Crystal Report to the project, and selected the Drill Down expert.

Figure 10.39. The Drill Down Expert.

I added the usual database connection, then selected the Customers and Orders tables. The expert correctly linked the two tables on CustomerID . I added the Customers table and the Orders table, and the expert correctly linked the two using their CustomerID fields (see Figure 10.40).

Figure 10.40. Joining the two tables on CustomerID .

The report's data selector is going to do a JOIN on the two tables. As you know, a JOIN puts all of the selected fields in both records into a result cursor. The report will group on the fields in the Customers table and present those fields at the first level; when you double-click on any row, all of the records for the group will be shown, but only the columns from the detail record. It's a simple concept, elegantly executed. In Figure 10.41 you see the field selection.

Figure 10.41. Selecting fields for the Drill Down report.

The Drill tab shown in Figure 10.42 is where you specify which records to show and which to hide.

Figure 10.42. The Drill tab: Show the Customer fields, hide the Orders fields.

Finally, set the ReportSource property of the form to Drilldown.rpt and add the following code to the MainForm menu:

 Dim frm as New frmDrilldown Frm.Show() 

In Figure 10.43, I picked one of the 10 custom formatting options, which gave me drop shadows on some of the text displays.

Figure 10.43. Selecting a report style.

The resulting report layout is shown in Figure 10.44. Group and detail records share the same column headings. It looks strange when you first run the report because the column headings for the detail records show even though the detail records don't.

Figure 10.44. The final report layout.

Press F5 to bring up MainForm and select Reports, Drill Down from the menu, and you'll see the high-level customer summary data, as shown in Figure 10.45.

Figure 10.45. The top-level customer data.

Double-click on the heading row, and the detail rows appear, as shown in Figure 10.46.

Figure 10.46. The drill-down detail report.

This is an amazing result for no programming.