WHERE PC.Missing = 1 OR IC.Missing = 1 in VB.NET

Printing QR Code in VB.NET 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

PROJECT ACCESS Load the 21 (Before) Code project, either through the New Project templates or by accessing the project directly from the installation directory. To see the code in its final form, load 21 (After) Code instead.

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.

Since we already jointly created an RDLC report earlier in the chapter, I went ahead and added the five built-in reports for you: ReportCheckedOut.rdlc This file implements report #1, the items checked out report. It uses the ReportSchemaPatronItems class schema, and includes three columns in the main data list: patron name/bar code, item name/bar code/details, and due date. For the item name field, I wanted to present additional information when available. The item name, copy number, and media type are required values, but item bar code is optional. Here s the format I desired:

Item Name (#CopyNumber, MediaType, Barcode)

To get this result, I had to concatenate the various source fields together, and use a conditional function (IIf) to optionally include the bar code and its comma:

=Fields!Title.Value & " (#" & CStr(Fields!CopyNumber.Value) & ", " & Fields!MediaName.Value & IIf(IsNothing(Fields!ItemBarcode.Value), "", ", " & Fields!ItemBarcode.Value) & ")"

As mentioned earlier, the due date field has an expression in its Color property that turns the text red when the item is overdue. ReportOverdue.rdlc This report shows a list of all overdue items in the system. Since everything will be overdue, I set the due date field to always use red for its font color. Other than that and the title, the report is basically identical to the checked-out items report. ReportMissing.rdlc This report shows a list of all items marked as missing. Even though the schema includes a due date field, I don t use it in this report. The rest of the report is basically identical to the checked-out items report. ReportPatronFines.rdlc This report lists all patrons that still owe fines, and the amount of the fine due. It uses the ReportSchemaPatronFines class schema. The field that displays the fine has a C in its Format property. This formatting code forces the decimal value to display as currency using the culture settings on the local system. This Format property uses the same codes recognized by the String.Format method. ReportStatistics.rdlc Report #5 displays record counts from some of the tables in the Library database. This is the only report that uses the ReportSchemaStatistics class schema. The report itself just displays two strings per record: a name and a value. It depends on the calling code to format those fields properly.