As we learned in the previous chapter, the .NET Framework includes a full object-based printing system that uses GDI+ commands to draw text and graphics on each printed page. Since you can put anything you want on each page, you could develop your own custom reports using this method. The responsibility for positioning each label and calculated field on the page, and determining when to move to a new page, will rest entirely on your shoulders. Still, the GDI+ commands are straightforward, and developing some basic reports using this method would not be overwhelming.

If you want to take this route for your reports, I refer you back to Chapter 20 and the basic printing concepts presented there.

Besides being a significant timewaster, the Internet (and its HTML-based page description language) is a great medium for data-report communication. The table-formatting tags in HTML (such as <td>) let you organize tabular output without much effort. Sure, it’s a chore stringing all of those baby-size text strings together to build the page, but there are ways around that, too.

Back in Chapter 13, I discussed XSLT (XSL Transformations), a way to take XML-based data and reshape it into any form you want—including great works of art by Michelangelo, or nicely crafted HTML. However you obtain the HTML, you have a choice of display methods as well. The most direct method involves storing the generated HTML in a disk file, and starting the user’s default browser to display it using a command such as:

Process.Start("c:	empMyReport.htm")

If you want the report to have a more “integrated” look in your application, you can display the HTML content in a web browser control. We did this in the project code for Chapter 17, when we displayed the details of a library item as HTML.

Windows Presentation Foundation (WPF) is used primarily to make your user interface dance with color and action. But a portion of that technology exists to generate XML-based static documents known as XPS (XML Paper Specification). Just as you can generate reports using HTML, you can generate them using the XPS standard.

Chapter 18 includes a brief discussion of WPF and XPS. If the generation of XPS-based reports is of interest to you, check out the documentation included with Visual Studio.

Visual Studio includes a set of classes in the Microsoft.Reporting namespace that are specifically designed to report data. The key class in this namespace is the ReportViewer class (identified as MicrosoftReportViewer in its control alter-ego). Actually, it is two controls: one for Windows Forms and one for Web Forms. These controls are based, in part, on the technology found in Microsoft SQL Server Reporting Services, although you can use the controls without SQL Server.

The Library Project will use the WinForms.ReportViewer control for its built-in reports. We’ll spend most of this chapter discussing the control and its use in Windows Forms applications. I won’t be discussing the Web Forms version of the control here, although its use closely parallels that of the Windows Forms version.

You’ll see how simple it is to add the MicrosoftReportViewer control to an existing project. But Visual Studio also includes a new project template that focuses on the MicrosoftReportViewer control. Creating a new “Reports Application” project uses a project wizard to help you set up a custom report. The final result can be your complete report application, or you can use it as the basis for further customization.

If you have at least the Professional Edition of Visual Studio 2008, you received a complimentary copy of Crystal Reports. The included version is a functional subset of the official Crystal Reports 2008 release. If you are new to Visual Basic, you have missed out on the previous versions of Crystal Reports that have been included with the language since its earliest releases. Because of this long-time relationship with Visual Basic, Crystal Reports has become one of the most widely used reporting packages on the market.

Crystal Reports is a third-party product, currently owned by a company called Business Objects. The product has changed ownership hands several times since it was first paired with Visual Basic, but Business Objects seems to be taking care of it for now. I won’t be discussing Crystal Reports any further in this book.

Visual Basic has been the primary macro language of the Microsoft Office suite of applications since the untimely death of WordBasic. But I’m talking about pre-.NET Visual Basic, which is so unmanaged. Fortunately, you can also use the managed world of Visual Basic 2008 to interact with Microsoft Office applications. How you interact with Office depends on whether the Office document or the Visual Basic application is the primary focus for the user.

If your goal is to enhance a “line of business” application by using various Office applications as the portal to that application—for example, showing the latest sales figures from within Microsoft Outlook—consider building an Office Business Application (OBA). OBAs represent a new way of designing integrated programs using Visual Studio, Microsoft Office, Microsoft SharePoint Services, and other related systems. From the Visual Studio side of the world, your development work happens through Visual Studio Tools for Office (abbreviated as VSTO, or “visto”), included with the Professional and Team System editions of Visual Studio.

If your goal is to create your own “task bar” add-ins within Office applications, use the new Office Add-In project templates included with Visual Studio. These easy-to-develop extensions let you customize the user experience by customizing the Office feature set. Add-ins are also considered part of VSTO, and are available only with Visual Studio 2008 Professional and Team System editions.

If the user will access Office features only indirectly through your Visual Basic application (e.g., if you want your program to initiate a Microsoft Word mail merge), use the Microsoft Office Primary Interop Assemblies (PIA) supplied by Microsoft. These libraries provide access to Office application-specific features through the Microsoft.Office namespace. Like VSTO, these libraries link up your .NET code to Microsoft Office, but with the focus on your code instead of on the Office document.

Add a data source to the project that refers to the Activity database table. We already did this back in Chapter 10, in the "Creating a Data Source" section. Select the Data → Add New Data Source menu command, and use the Data Source Configuration Wizard to locate your Library database. When you reach the list of database objects, check the box next to the Activity table, and click the Finish button. You should now have a data source named LibraryDataSet. Figure 21-1 shows the elements added to the Solution Explorer and the Data Sources panel by this action.

Figure 21-1. The LibraryDataSet as a data source, and as an XML “.xsd” file

Use the Project → Add New Item menu command to add a new “Report” item. Figure 21-2 shows the item report in the Add New Item dialog. Make sure you choose Report and not Crystal Report from the list.

Figure 21-2. Adding a new report to the project

Click the Add button to insert the report into the project. A new Report1.rdlc file appears in your project, and its designer opens automatically. “RDLC” is short for “Report Definition Language – Client,” and files of this type contain XML content that describes the layout of a locally designed report. Figure 21-3 shows the designer for the added Report1.rdlc file, plus the controls in the toolbar that you can add to the report surface. I will refer to reports created through this designer as “RDLC reports” throughout the rest of this chapter.

Figure 21-3. The report designer and related toolbar

If you’ve written reports in Microsoft Access or in some other common reporting tool, you are probably familiar with “banded” reports. These reports have separate “bands” or stripes that represent a portion of the printed page. Bands include page headers and footers, report headers and footers, the record detail section, and group headers and footers used to visually and logically group the detail entries. As the report runs, an imaginary horizontal page-wide line runs from the top to the bottom of the page. As the line hits each band, the report processes the fields in that band until there are no more records to process.

RDLC reports are a little bit different from those banded reports. There are only three bands: page header, page footer, and everything else (a band called “Body”). Instead of adding bands for records and groups, you add fields to data regions. These special controls process the records linked to the report according to the shape of the data region. There are four data region controls in the toolbox:

Records from data sets are always tied to a data region. If your report includes data from multiple distinct data sources, each data source will link to exactly one report region, and all regions appear in the Body band. We’ll use a List data region for this sample report. Go ahead and add the List control to the Body band on the report surface. You can now add other items either to the band surface itself, or to the List control surface. Items added to the List control are reprocessed for each record in the incoming data source. These items can be either controls from the toolbox, or database fields displayed in the Data Sources panel. Using the Activity table in the Data Sources panel, drag the FullName field to the List control surface. Figure 21-4 shows the display just after performing this drag operation.

Figure 21-4. A List control with a field from the data set

When we dragged the field from the data source to the List control, Visual Studio established a link between them. The list1 control’s DataSetName field now refers to LibraryDataSet_Activity, the name of the data source. It also added a TextBox control to the list’s surface, and added an expression (=Fields!FullName.Value) that displays the contents of that field from the database for each processed record.

I’m going to resize the List control, the text box, and the Body band so that the FullName text box field is pretty much all there is in the report (see Figure 21-5).

Figure 21-5. A resized version of the report

The report is ready to use. As we designed the report surface, Visual Studio was busy generating XML and storing it in the Report1.rdlc file.

The RDLC file is only an XML definition of a report; it doesn’t have any ability to display itself. To view the report, we must add a report control to a form or web page that knows how to properly merge the XML design content with the data from the specified data source. Return to Form1, and add a MicrosoftReportViewer control to its surface from the toolbox (it’s in the Reporting section of the toolbox on my system).

The added control includes a small “smart tags” button in its upper-right corner. Clicking this button displays the ReportViewer Tasks fly-out window, which appears in Figure 21-6.

Figure 21-6. The MicrosoftReportViewer control on the form surface

The MicrosoftReportViewer control presents a form-based experience for displaying reports. Most of the control is a blank area where the report appears. It also includes a toolbar used to navigate through the pages of the report. The user can also initiate an export or a printout of the report through these controls. If you don’t need the toolbar or one of its controls, use the various Show... properties of the MicrosoftReportViewer control to hide the unneeded elements.

The report viewer is generic and report-independent. If you have several RDLC files in your project, you can display any of them (one at a time) through the same report viewer. We have only one report in our project, so let’s connect it (SimpleReport.Report1.rdlc) to the viewer by using the Choose Report task from the report viewer’s smart tag button. Also, click on the “Dock in parent container” task in the fly-out window to expand the report to the form’s size.

The RDLC report, the data from the data source, and the MicrosoftReportViewer control are all joined in one glorious report display by the magic of data binding. When you linked the report to the viewer control, three more controls appeared on the form: LibraryDataSet, ActivityBindingSource, and ActivityTableAdapter. LibraryDataSet is a reference to the actual data source we added earlier. The other two controls wrap up that data in a form that can be bound to the report viewer. Although you can’t see it in the designer, the hidden form code connects up these controls and the XML report to the viewer.

ReportDataSource1.Name = "LibraryDataSet_Activity"
ReportDataSource1.Value = Me.ActivityBindingSource
Me.ReportViewer1.LocalReport.DataSources.Add( _
   ReportDataSource1)
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = _
   "SimpleReport.Report1.rdlc"

Yeah, I don’t really get it, either. But that’s OK. Visual Studio connected it all up for us.

Press F5 and see the results of your efforts. In Figure 21-7, I adjusted the view by clicking on the Page Layout toolbar button, and setting the zoom level to Page Width.

Figure 21-7. Reporting the essential contents of the Activity table

Well, that report is fine as far as Activity table reports go, but we could spruce it up a bit more.

I think the report needs a meaningful title at the top of each page, plus a page number in the bottom-right corner. Let’s return to the RDLC report designer and add them. Once there, right-click on the background of the report (not the body, which has the grid marks on it), as shown in Figure 21-8.

Figure 21-8. Adding page headers and footers

From this menu, select Page Header, then bring up the menu again and select Page Footer. Each new band appears on the report surface.

Whether it’s static, unchanging text or text that’s dynamically generated from a data source, the TextBox control is the control of choice for showing text content. Add a TextBox control from the toolbox to both the header and footer sections. Click inside the header’s text box, and type the following:

="The Activity Table Report"

You can use the Properties panel to adjust the look of this control, including its display font.

In the footer text box, add this text:

="Page " & Globals!PageNumber

The Globals pseudo-object includes a few members that you can use in the report. How did I know to use Globals!PageNumber? I built the expression visually using the Expression Editor. To access it, right-click on the TextBox control and select Expression from the shortcut menu. The editor, shown in Figure 21-9, lets you build up an expression using lists of functions and field names. The actual functions just happen to be—hooray—Visual Basic functions.

Figure 21-9. The Expression Editor

Grouping of data is common in printed reports. To add grouping to our report, we need to embed our existing List control (the detail record) within another List control (the group), and set various properties on the group List control to determine the method of data grouping.

Let’s try it. Add another List control (called list2) to the report body, and give it twice the height as the existing List control (called list1). Then, drag list1 (the detail record) into list2 (the new group), placing it toward the bottom. Your report should look like Figure 21-10.

Figure 21-10. A grouping list added to the report

To configure the group, right-click on it and select Properties from the shortcut menu. The List Properties form appears. On its General tab, click the “Edit details group” button, which sets the grouping. On the Grouping and Sorting Properties form that appears, enter the following text into the first row of the “Group on” field:

=Left(Fields!FullName.Value, 1)

This expression tells the list2 control to group its detail results by the first character of the first name field.

On this same form, add the following text to the “Document map label” field:

="Letter: " & Left(Fields!FullName.Value, 1)

The document map enables a clickable hyperlink list into the different groups of the report. When we run the report a little later, we’ll see this map just to the left of the report display surface.

The records in the Activity table are ordered for the convenience of the programmer (me). But the report user probably wants to see them sorted in some reasonable fashion. Click on the Sorting tab, and add the following text to the “Sort on” field, in the Expression column:

=Fields!FullName.Value

As expected, this will sort the data by the FullName field. Click the OK buttons all the way out, and return to the report surface.

We still need to add something that will make each group stand out. Add a TextBox control to the list2 grouping control. Put it in the upper-left corner of that parent control, and type the following text into it (or into its Value property):

=Left(Fields!FullName.Value, 1)

I also set its BackgroundColor property to “Black,” its Color property to “White,” and its Font property to “Normal, Arial, 12pt, Bold” just for looks.

Running the report gives the results in Figure 21-11. Notice the document map along the left edge of the window, and the grouped single-letter titles before each grouped section.

Figure 21-11. The full report, with grouping and sorting enabled

Probably the coolest feature of RDLC reports is that many of the properties for items placed on the report surface can include conditional expressions. This means that you can conditionally alter, say, the visual properties of a TextBox control based on the value of a field in the current record.

In the "Project" section of this chapter, we’ll write a report that uses due dates for items currently checked out. If the item is past due, I want to show the due date in red. Normally, a TextBox control’s Color property (which controls font color) is “Black.” To have that field respond to overdue items, I will replace “Black” with the following expression:

=IIf(Fields!DueDate.Value < Today, "Red", "Black")

Although it is very common to generate reports from databases, you can actually use data from virtually any source. When using the MicrosoftReportViewer control, any data source that implements the IEnumerable interface is good enough. That includes all collections, arrays, and LINQ query results. The report isn’t that picky, as long as the data is formatted as it expects. For the report we just made, we can ditch the actual data and supply our own fake data. This intercepting and substituting data is like something out of a spy thriller. But we must follow a few rules to make it work:

Those rules aren’t so bad. So, here’s what we need to do: create a fake data source, intercept the report just before it tries to get the data from the Library database, and insert our own data instead.

For a fake data source, we’ll need a class that includes at least the FullName field.

Public Class FakeActivityRecord
   Private StoredID As Long
   Private StoredFullName As String

   Public Sub New(ByVal whatID As Long, _
         ByVal whatFullName As String)
      StoredID = whatID
      StoredFullName = whatFullName
   End Sub

   Public Property ID(  ) As Long
      Get
         Return StoredID
      End Get
      Set(ByVal value As Long)
         StoredID = value
      End Set
   End Property

   Public Property FullName(  ) As String
      Get
         Return StoredFullName
      End Get
      Set(ByVal value As String)
         StoredFullName = value
      End Set
   End Property
End Class

The exposed fields must be properties, and not just public fields; the report viewer doesn’t recognize standard member fields.

If you look at the source code for Form1, you’ll find that the following code was added to the Form_Load event handler when we linked the report viewer with the RDLC report:

Me.ActivityTableAdapter.Fill(Me.LibraryDataSet.Activity)
Me.ReportViewer1.RefreshReport(  )

It’s that first line that loads the data from the Library database’s Activity table and links it to the report. We need to replace those two wizard-generated lines with code that cuts off the real data at the pass.

' ----- Create a fake table of fake records.
Dim fakeSource As New Collections.Generic.List( _
   Of FakeActivityRecord)

' ----- Add each of the fake records.
fakeSource.Add(New FakeActivityRecord(1, "Do some work"))
fakeSource.Add(New FakeActivityRecord(2, "Take a nap"))
fakeSource.Add(New FakeActivityRecord(3, "Write a program"))
' ----- The report was already bound to the true
'       data source. Delete it.
Me.ReportViewer1.LocalReport.DataSources.Clear(  )

' ----- Build a new data source. Remember, it must have
'       the same name.
Dim fakeReportSource As New _
   Microsoft.Reporting.WinForms.ReportDataSource
fakeReportSource.Name = "LibraryDataSet_Activity"
fakeReportSource.Value = fakeSource

' ----- Connect the data source to the report, and we're done.
Me.ReportViewer1.LocalReport.DataSources.Add(fakeReportSource)
Me.ReportViewer1.RefreshReport(  )

Figure 21-12 shows the report with the fake data on display.

Figure 21-12. This fake data will not self-destruct in five seconds

Substituting data at the last second is fine and all, but what if you want to design a report that doesn’t depend on a database at all? You can do that, too, by supplying a fully custom data source. RDLC reports require some sort of data source schema at design time; you just can’t supply fully custom data on the fly when running the report. But you can supply a custom schema based on a class in your application.

For the class, we’ll stick with the FakeActivityRecord we created in the previous section. Then we will design a data source from this class. Select the Data → Add New Data Source menu command. When the Data Source Configuration Wizard has appeared in the past, you have always selected Database as the source for the data. This time, select Object, as shown in Figure 21-13.

Figure 21-13. Creating a data source based on a custom object

When you click the Next button, a hierarchy of all the classes in your application appears. Expand the classes, then locate and select the FakeActivityRecord class. Click the Finish button. FakeActivityRecord now appears as a data source in the Data Sources panel.

Now you can drag and drop this data source’s FullName field onto a new RDLC report design surface. Add a new report to your project, and follow the same steps we used previously to design the first report. This time, use the FakeActivityRecord data source instead of the LibraryDataSet source.

To test this new report, I removed the original Form1 from the project and added a brand-new Form1. I also added a MicrosoftReportViewer control to its surface and docked it, but I did not link it to the RDLC report. This keeps things a lot cleaner as there are no binding source controls and whatnot to worry about. Then I added this code to the form’s Load event handler:

' ----- Link to the RDLC report design.
Me.ReportViewer1.LocalReport.ReportEmbeddedResource = _
   "SimpleReport.Report2.rdlc"

' ----- Create a fake table of fake records.
Dim fakeSource As New Collections.Generic.List( _
   Of FakeActivityRecord)

' ----- Add each of the fake records.
fakeSource.Add(New FakeActivityRecord(1, "Breakfast"))
fakeSource.Add(New FakeActivityRecord(2, "Lunch"))
fakeSource.Add(New FakeActivityRecord(3, "Dinner"))

' ----- Build a new data source. Remember, it must have
'       the same name.
Dim fakeReportSource As New _
   Microsoft.Reporting.WinForms.ReportDataSource
fakeReportSource.Name = "SimpleReport_FakeActivityRecord"
fakeReportSource.Value = fakeSource
' ----- Connect the data source to the report, and we're done.
Me.ReportViewer1.LocalReport.DataSources.Add(fakeReportSource)
Me.ReportViewer1.RefreshReport(  )

It’s pretty similar to the previous custom code, although the data source name is now SimpleReport_FakeActivityRecord, the name this new report expects (which I found out by running the report and reading the error message).

I’ve saved a copy of both custom reports in the installation directory for the book’s source code samples. Look in the subdirectory named SimpleReport.

The first report, “items checked out,” lists the patron name and item title for every item currently checked out by the patron. It involves the Patron table (to get the patron name), the PatronCopy table (the checkout event), the ItemCopy table (the actual item checked out), and the NamedItem table (where the item title appears). We’ll also include the CodeMediaType table, which tells us whether the item is a book, a CD, or some other media type.

Microsoft SQL Server Management Studio Express includes a visual Query Designer that we can use to design the query. Figure 21-14 shows the five needed tables as linked together by the designer.

Figure 21-14. The five tables in the checked-out items query

Whether you use the Query Designer or build the SQL statement by hand, you eventually come up with something similar to the following, which we’ll use within the Library application:

/* ----- Report #1: Items checked out report. */
SELECT PA.LastName + ', ' + PA.FirstName AS PatronName,
   PA.Barcode AS PatronBarcode,
   PC.DueDate, IC.CopyNumber, IC.Barcode AS ItemBarcode,
   NI.Title, CMT.FullName AS MediaName
FROM Patron AS PA
   INNER JOIN PatronCopy AS PC ON PA.ID = PC.Patron
   INNER JOIN ItemCopy AS IC ON PC.ItemCopy = IC.ID
   INNER JOIN NamedItem AS NI ON IC.ItemID = NI.ID
   INNER JOIN CodeMediaType AS CMT ON NI.MediaType = CMT.ID
WHERE PC.Returned = 0
   AND PC.Missing = 0
   AND IC.Missing = 0
ORDER BY NI.Title, IC.CopyNumber, PA.LastName, PA.FirstName

This query links up all of the tables, and then requests every record that has not been returned (PC.Returned = 0). It ignores any item marked as missing (PC.Missing = 0 AND IC.Missing = 0). This query will eventually drive the report. But for now, keep in mind that RDLC reports don’t actually need a real SQL statement or database table for the report schema. We can also build a compatible schema by hand using a class. This turns out to be a lot cleaner since we won’t have a lot of dataset-related files strewn throughout the project source code. (The LibraryDataSet data source we created in the sample report earlier in this chapter added four source files and nearly 50 KB of source code to the project, not counting the RDLC report! The class-based data source didn’t add any code other than the class definition itself, and a little bit of XML in the RDLC file.)

As for the data source schema, we can extrapolate it from the SELECT clause of the SQL query. If we were to design a class with a matching schema, it would look like this (without the property detail code):

Class Report1Schema
   Public Property PatronName As String
   Public Property PatronBarcode As String
   Public Property DueDate As Date
   Public Property CopyNumber As Integer
   Public Property ItemBarcode As String
   Public Property Title As String
   Public Property MediaName As String
End Class

The next two reports are for “overdue items” and “missing items.” For me, the schema for report #1 is exactly what I want to see in these other two reports, so let’s just use the same SQL statement. All we need to do is change the WHERE clause. For the overdue items report, use this WHERE clause:

WHERE PC.Returned = 0
   AND PC.Missing = 0
   AND IC.Missing = 0
   AND PC.DueDate < GETDATE(  )

The missing items report will use this WHERE clause:

WHERE PC.Missing = 1
   OR IC.Missing = 1

The fourth report displays the amount of fines still owed by patrons, so it will require a different schema. Here’s its SQL statement, which uses some aggregate grouping features:

/* ----- Report #4: Fines owed by patron. */
SELECT PA.LastName + ', ' + PA.FirstName AS PatronName,
   PA.Barcode AS PatronBarcode,
   SUM(PC.Fine - PC.Paid) AS FinesDue
FROM Patron AS PA
   INNER JOIN PatronCopy AS PC ON PA.ID = PC.Patron
GROUP BY PA.LastName + ', ' + PA.FirstName, PA.Barcode
HAVING SUM(PC.Fine - PC.Paid) > 0
ORDER BY PatronName

Here’s the schema that goes with report #4:

Class Report4Schema
   Public Property PatronName As String
   Public Property PatronBarcode As String
   Public Property FinesDue As Decimal
End Class

For the final report, we’ll just use a schema with two string values: a statistic name, and its related value. Here’s its schema:

Class Report5Schema
   Public Property EntryName As String
   Public Property EntryValue As String
End Class

Well, that’s enough preparation. Let’s start coding.

The ReportSchemas.vb file, already added to the project, includes the three schemas used for the five built-in reports. Just to remind us of the members, here are the public property definitions included in each class, minus the Get and Set accessors, and minus the private class members:

Public Class ReportSchemaPatronItems
   ' ----- Used for the following reports:
   '         Report #1: Items checked out report
   '         Report #2: Items overdue report
   '         Report #3: Items missing report
   Public Property PatronName(  ) As String
   Public Property PatronBarcode(  ) As String
   Public Property DueDate(  ) As Date
   Public Property CopyNumber(  ) As Integer
   Public Property ItemBarcode(  ) As String
   Public Property Title(  ) As String
   Public Property MediaName(  ) As String
End Class

Public Class ReportSchemaPatronFines
   ' ----- Used for the following reports:
   '         Report #4: Fines owed by patron
   Public Property PatronName(  ) As String
   Public Property PatronBarcode(  ) As String
   Public Property FinesDue(  ) As Decimal
End Class
Public Class ReportSchemaStatistics
   ' ----- Used for the following reports:
   '         Report #5: Library database statistics report
   Public Property EntryName(  ) As String
   Public Property EntryValue(  ) As String
End Class

Once the schema classes are in the project, you will need to build the project before those classes can be used in RDLC reports as data sources. In the Library Project, build the project now with the Build → Build Library menu command. All three schemas should then appear as sources in the Data Sources panel (see Figure 21-15). If the Data Sources panel is closed, open it using the Data → Show Data Sources menu command.

Figure 21-15. The three data source schemas

Since we already jointly created an RDLC report earlier in the chapter, I went ahead and added the five built-in reports for you:

It’s time to add a MicrosoftReportViewer control. Since a single MicrosoftReportViewer control can display any type of RDLC report, we’ll just add a single form to handle all five built-in reports.

Add a new form named ReportBuiltinViewer.vb to the project. Set its Text property to Library Report and its WindowState property to Maximized. Also, load the project’s icon (Book.ico) into the Icon property. You’ll find a copy of this file in the project installation directory. If you want, you can size the form to some reasonable starting point for a report (I used “680, 400”), but each report will start out maximized when used.

Add a MicrosoftReportViewer control named ReportContent to the form, and set its Dock property to Fill. Set both the ShowBackButton and ShowDocumentMapButton properties to False.

The code we will add to this form is a variation of code we wrote earlier in this chapter. The code that starts each report will pass to this form the name of the report RDLC file, the name of the data schema used, and the actual data. Since these reports will be modeless (you can keep them open while still using other parts of the Library program), we can’t let the calling code wait around for the user to close the report before we discard the report data. We’ll let the report dispose of the data itself. To do this, we need to keep a reference to that data. Add the following statement to the ReportBuiltinViewer form class.

Private StoreDataTable As Object

Remember, reports can use a variety of data source formats, including true database connections, arrays, and collections. Reports #1 through #4 will use a System.Data.DataTable instance, and report #5 will pass a generic List collection.

The best time to dispose of the data is when the report is closing. Add the following event handler to the form, which confirms that the data supports the disposal process before calling the Dispose method.

Private Sub ReportBuiltinViewer_FormClosing( _
      ByVal sender As Object, ByVal e As _
      System.Windows.Forms.FormClosingEventArgs) _
      Handles Me.FormClosing
   ' ----- Get rid of the data.
   If (TypeOf StoreDataTable Is IDisposable) Then
      CType(StoreDataTable, IDisposable).Dispose(  )
   End If
End Sub

The code that opens this display form will pass in the essential report values through a public method named StartReport. Add its code now.

Public Sub StartReport(ByVal whichReport As String, _
      ByVal whichDataSchema As String, _
      ByVal whichData As Object)
   ' ----- Run one of the built-in reports. whichReport is
   '       the name of the RDLC report file, in the format
   '       "Library.xxx.rdlc." whichDataSchema provides the
   '       name of the schema to use, in the format
   '       "Library_xxx." whichDataSet is the actual data
   '       to link to the report, which must match the schema.
   Dim customDataSource As New _
      Microsoft.Reporting.WinForms.ReportDataSource

   ' ----- Connect the viewer, the report, and the data.
   ReportContent.LocalReport.ReportEmbeddedResource = _
      whichReport
   customDataSource.Name = whichDataSchema
   customDataSource.Value = whichData
   ReportContent.LocalReport.DataSources.Add( _
      customDataSource)

   ' ----- Display the report.
   StoreDataTable = whichData
   Me.Show(  )
End Sub

This code tells the viewer which report to use as an embedded resource, and then attaches the data as a custom data source. “Local” in these property names indicates a local (client) report instead of a “server” report that runs within SQL Server.

When we were playing with the reports before, we saw that the default display mode was the “fill-the-entire-screen-with-page-content” mode. Personally, I like to see those fake page boundaries. The MicrosoftReportViewer control doesn’t include a property that lets us change this default view (why not?), but we can still adjust the initial display style through methods on the control. When we added the report viewer to the form, Visual Studio also added the following statement to the form’s Load event handler:

ReportContent.RefreshReport(  )

Add the following code just before that statement.

' ----- Generate and display the report.
ReportContent.SetDisplayMode( _
   Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)
ReportContent.ZoomMode = _
   Microsoft.Reporting.WinForms.ZoomMode.Percent
ReportContent.ZoomPercent = 100

I forget how long ago we added the ReportSelect.vb form that drives reporting, but it is already there in the project. In case you forgot what it looked like (I did), Figure 21-16 gives us a refresher.

Figure 21-16. The report selection form

We previously added support for our five built-in reports in this form’s code. In a tribute to the never-ending reality of forgetting to finish all of the code, we need to add some code that we overlooked earlier. If you use an XML report configuration file to populate the report list, and you provide a description for each report in the XML, each entry displays that description in the lower half of the report selection form. But if you don’t use a configuration file, and just depend on the form to add the five built-in reports by default (which it does), the form won’t display associated descriptions, because we forgot to add them. Add a function to the ReportSelect class that returns a short description for each of the five reports.

Private Function GetBuiltinReportDescription( _
      ByVal whichReport As ReportItemEnum) As String
   ' ----- Return a predefined description for the
   '       built-in reports.
   Select Case whichReport
      Case ReportItemEnum.BuiltInCheckedOut
         Return "Displays all items currently checked " & _
            "out, sorted by name."
      Case ReportItemEnum.BuiltInOverdue
         Return "Displays all overdue items, sorted by name."
      Case ReportItemEnum.BuiltInMissing
         Return "Displays all missing items, sorted by name."
      Case ReportItemEnum.BuiltInFinesOwed
         Return "Displays all unpaid fines owed by " & _
            "patrons, sorted by patron name."
      Case ReportItemEnum.BuiltInStatistics
         Return "Displays some record counts from the " & _
            "Library database."
      Case Else
         Return "There is no description for this report."
   End Select
End Function

We’ll call this code from two places. The first is in the LoadReportGroup method. This code loads in the XML report configuration file. If that file includes one of the built-in reports, but doesn’t supply a description with it, we’ll supply the description ourselves. About halfway through that code, you’ll find these lines:

' ----- So, what type of entry is it?
If (scanNode.Attributes("type").Value = "built-in") Then

About five lines below this is the following statement:

reportEntry.ItemType = CType(CInt( _
   reportEntry.ReportPath), ReportItemEnum)

Add the following code just after that statement.

If (reportEntry.Description = "") Then _
   reportEntry.Description = _
   GetBuiltinReportDescription(reportEntry.ItemType)

The second need for the built-in descriptions appears in the RefreshReportList method. This method makes the call to LoadReportGroup to retrieve the XML configuration. But if after that the report list is still empty, RefreshReportList adds in the five default reports, which each require a description. Near the end of the method, within a For...Next loop, you’ll find this closing statement:

' ----- Add the report entry to the list.
AllReports.Items.Add(reportEntry)

Add the following code just before that statement.

reportEntry.Description = GetBuiltinReportDescription( _
   reportEntry.ItemType)

OK, that’s it for the fix-up code. Now back to writing the actual reports. The code to start each of the five reports already exists in the ReportSelect form’s ActRun_Click event handler. Most of that code includes a Select Case statement that acts as a switchboard for the selected report. Here’s the part that calls the five built-in reports:

Case ReportItemEnum.BuiltInCheckedOut
   ' ----- Items Checked Out
   ' TODO: Write BasicReportCheckedOut(  )
Case ReportItemEnum.BuiltInOverdue
   ' ----- Items Overdue
   ' TODO: Write BasicReportOverdue(  )
Case ReportItemEnum.BuiltInMissing
   ' ----- Items Missing
   ' TODO: Write BasicReportMissing(  )
Case ReportItemEnum.BuiltInFinesOwed
   ' ----- Fines Owed by Patrons
   ' TODO: Write BasicReportFines(  )
Case ReportItemEnum.BuiltInStatistics
   ' ----- Library Database Statistics
   ' TODO: Write BasicReportStatistics(  )

Clearly, this code isn’t accomplishing much. Change each of the TODO lines, removing the TODO: Write portion of the statement. So, in the line that says:

' TODO: Write BasicReportCheckedOut(  )

change the code to:

BasicReportCheckedOut(  )

Do that for each of the five TODO lines.

Exposing these five method calls means that we have to write those methods, darn it. These methods will retrieve the data for the report, and send that data to the report viewer, along with the name of the RDLC file. They’re actually quite short and simple, considering the beautiful reports you will get out of them. Let’s start by adding the BasicReportCheckedOut method to the ReportSelect class.

Private Sub BasicReportCheckedOut(  )
   ' ----- Run built-in report #1: Items checked out report.
   Dim sqlText As String
   Dim reportData As Data.DataTable
   Dim reportForm As ReportBuiltinViewer

   On Error GoTo ErrorHandler

   ' ----- Retrieve the data as a dataset.
   sqlText = "SELECT PA.LastName + ', ' + " & _
      "PA.FirstName AS PatronName, " & _
      "PA.Barcode AS PatronBarcode, " & _
      "PC.DueDate, IC.CopyNumber, " & _
      "IC.Barcode AS ItemBarcode, " & _
      "NI.Title, CMT.FullName AS MediaName " & _
      "FROM Patron AS PA " & _
      "INNER JOIN PatronCopy AS PC ON PA.ID = PC.Patron " & _
      "INNER JOIN ItemCopy AS IC ON PC.ItemCopy = IC.ID " & _
      "INNER JOIN NamedItem AS NI ON IC.ItemID = NI.ID " & _
      "INNER JOIN CodeMediaType AS CMT ON " & _
      "NI.MediaType = CMT.ID " & _
      "WHERE PC.Returned = 0 " & _
      "AND PC.Missing = 0 " & _
      "AND IC.Missing = 0 " & _
      "ORDER BY NI.Title, IC.CopyNumber, " & _
      "PA.LastName, PA.FirstName"
   reportData = CreateDataTable(sqlText)

   ' ----- Check for no data.
   If (reportData.Rows.Count = 0) Then
      reportData.Dispose(  )
      MsgBox("No items are checked out.", MsgBoxStyle.OkOnly _
         Or MsgBoxStyle.Exclamation, ProgramTitle)
      Return
   End If

   ' ----- Send the data to the report.
   reportForm = New ReportBuiltinViewer
   reportForm.StartReport("Library.ReportCheckedOut.rdlc", _
      "Library_ReportSchemaPatronItems", reportData)
   Return

ErrorHandler:
   GeneralError("ReportSelect.BasicReportCheckedOut", _
      Err.GetException(  ))
   Return
End Sub

The code retrieves the report-specific records from the database, and makes sure that at least one record was included. (We could have added the SQL statement to the Library database as either a stored procedure or a view, and called that instead. For the purposes of this tutorial, it was simpler to store the statement directly in code.) It then calls the report viewer, passing the name of the RDLC file, the schema name (in the format ProjectName_ClassName), and the data table.

Next, add the BasicReportOverdue and BasicReportMissing methods. I won’t show the code here since, except for the name of the RDLC file and the WHERE clause in the SQL statement, they are identical to BasicReportCheckedOut.

Add in the BasicReportFines method, which handles built-in report #4.

It’s also quite similar to the BasicReportCheckedOut method, but it uses the SQL statement we designed earlier for patron fine retrieval. It also uses a different schema and report name.

reportForm.StartReport("Library.ReportPatronFines.rdlc", _
   "Library_ReportSchemaPatronFines", reportData)

The last method to add to ReportSelect.vb is BasicReportStatistics, which handles built-in report #5. It’s a little different from the other four because it gathers data from six different tables, one at a time. In each case, it retrieves a count of the number of records in a database table. The results are then stored in a generic collection (System.Collections.Generic.List), where each list entry is an instance of ReportSchemaStatistics, the class we used for the fifth report’s data schema. What a coincidence!

Here’s the code for BasicReportStatistics for you to add now to the ReportSelect form class.

Private Sub BasicReportStatistics(  )
   ' ----- Run built-in report #5: Library database
   '       statistics report.
   Dim sqlText As String
   Dim reportData As Collections.Generic.List( _
      Of ReportSchemaStatistics)
   Dim oneEntry As ReportSchemaStatistics
   Dim reportForm As ReportBuiltinViewer
   Dim resultValue As Integer
   Dim counter As Integer
   Const tableSets As String = "Author,Publisher," & _
      "Subject,NamedItem,ItemCopy,Patron"
   Const tableTitles As String = "Authors,Publishers," & _
      "Subject Headings,Items,Item Copies,Patrons"

   On Error GoTo ErrorHandler

   ' ----- Build the report data. It's all counts from
   '       different tables.
   reportData = New Collections.Generic.List( _
      Of ReportSchemaStatistics)
   For counter = 1 To CountSubStr(tableSets, ",") + 1
      ' ----- Process one table.
      sqlText = "SELECT COUNT(*) FROM " & _
         GetSubStr(tableSets, ",", counter)
      resultValue = DBGetInteger(ExecuteSQLReturn(sqlText))

      ' ----- Add it to the report data.
      oneEntry = New ReportSchemaStatistics
      oneEntry.EntryName = _
         GetSubStr(tableTitles, ",", counter)
      oneEntry.EntryValue = CStr(resultValue)
      reportData.Add(oneEntry)
   Next counter

   ' ----- Send the data to the report.
   reportForm = New ReportBuiltinViewer
   reportForm.StartReport("Library.ReportStatistics.rdlc", _
      "Library_ReportSchemaStatistics", reportData)
   Return

ErrorHandler:
   GeneralError("ReportSelect.BasicReportStatistics", _
      Err.GetException(  ))
   Return
End Sub

Since we really need to get the same information (COUNT(*)) for each of the six tables involved, I just implemented the code as a loop, and built the SQL statement for each one as I passed through the loop. A friendly table name and the record count are then stored in the generic list, which is eventually sent to the report.

You can now run the application and use the five built-in reports. You must log in as a librarian or administrator, and then access the Print Reports panel on the main form.

Believe it or not, we’re almost finished with the application. The only big thing left to do is to process past-due patron items to see whether fines are required. We’ll add this code in the next chapter, and also take a look at licensing.