Control Break Concepts

Control Break Concepts

Control break processing is a technique used to process a sorted sequential set of data (such as a text file, resultset of a database query, etc.) that contains groups of records with common values in one or more fields which are designated as the control fields. An example of a control field would be the sale date in a file of sales records for a particular month. Suppose you had eight sales on the first day of the month, and ten sales on the second day of the month. The set of eight records for day one and the set of ten records for day two each form a control group. So a control group is a set of records that all have the same value in the control field. As your program process the sales records, it notices that the date has changed between the eighth and ninth record. This change in the value of the control field is called a control break. When the control break is detected, the program will typically perform an action for the previous control group, such as print a subtotal for that group.

Control break processing typically comes into play when writing report programs "manually" (i.e., without the benefit of a report designer such as Crystal Reports, the ReportViewer, the MS-Access Report Designer, or others). Such report designers handle all of this control break stuff behind the scenes while you visually prepare the format of the report. Writing report programs manually (i.e., handling all details of the printing and subtotalling logic yourself within the program) is typically done in mainframe environments using a language like COBOL. So why bother with report program logic and control break logic in VB? Well, there are times when a plain-text printed report will get the job done and you can avoid the processing overhead of using a report designer. And even if you never have to write a report program "manually" (using native VB code), knowledge of control break processing can come in handy for other programming tasks, such as populating a TreeView control or generating an XML file.

In this article we will examine control break logic in the context of processing a sequential file of sales order data for the Northwind Traders company. (Note: This data was extracted from the Northwind sample database that Microsoft provides with VB and modified for the purpose of the examples presented here.) We will also examine some old-school report program logic techniques.

The Northwind sales file is a comma-delimited file consisting of the following fields:

· Year-Month (YYYYMM format). For example, if the sale occurred in October 2004, the value would be "200410".

· Order Date (The date on which the sale was made, M/D/YYYY format).

· Order Number (5-digit order identifier)

· Customer ID (5-character customer code identifier)

· Product Description (textual description of the product sold, 32 characters max)

· Unit Price (numeric unit price of the product sold)

· Quantity (integer representing the quantity of the product sold)

· Discount Percentage (Numeric decimal value representing the percent discount given to the customer on the item, if any. Could be 0, or 0.1 for 10%, 0.25 for 25%, etc.)

When the sample programs process this file, fields from each input record will be printed along with three calculated values:

· Extended Price (the base amount of a sale line item, calculated as Unit Price * Quantity)

· Discount Amount (the amount "off", calculated as Extended Price * Discount Percentage)

· Sale Total (the total amount for a sale line item, calculated as Extended Price – Discount Amount)

The content of the input file (NWSALES.TXT) is shown below:

200410,10/3/2004,10295,VINET,Gnocchi di nonna Alice,30.4,4,0

200410,10/4/2004,10296,LILAS,Pavlova,13.9,30,0

200410,10/4/2004,10296,LILAS,Gudbrandsdalsost,28.8,15,0

200410,10/4/2004,10296,LILAS,Queso Cabrales,16.8,12,0

200410,10/5/2004,10297,BLONP,Mozzarella di Giovanni,27.8,20,0

200410,10/5/2004,10297,BLONP,Chartreuse verte,14.4,60,0

200410,10/6/2004,10298,HUNGO,Chang,15.2,40,0

200410,10/6/2004,10298,HUNGO,Inlagd Sill,15.2,40,0.25

200410,10/6/2004,10298,HUNGO,Raclette Courdavault,44,30,0.25

200410,10/6/2004,10298,HUNGO,Tarte au sucre,39.4,15,0

200410,10/7/2004,10299,RICAR,Teatime Chocolate Biscuits,7.3,15,0

200410,10/7/2004,10299,RICAR,Outback Lager,12,20,0

200410,10/10/2004,10300,MAGAA,Louisiana Hot Spiced Okra,13.6,30,0

200410,10/10/2004,10300,MAGAA,Scottish Longbreads,10,20,0

200410,10/10/2004,10301,WANDK,Boston Crab Meat,14.7,10,0

200410,10/10/2004,10301,WANDK,Gnocchi di nonna Alice,30.4,20,0

200410,10/11/2004,10302,SUPRD,Alice Mutton,31.2,40,0

200410,10/11/2004,10302,SUPRD,Rössle Sauerkraut,36.4,28,0

200410,10/11/2004,10302,SUPRD,Ipoh Coffee,36.8,12,0

200410,10/12/2004,10303,GODOS,Boston Crab Meat,14.7,40,0.1

200410,10/12/2004,10303,GODOS,Louisiana Fiery Hot Pepper Sauce,16.8,30,0.1

200410,10/12/2004,10303,GODOS,Scottish Longbreads,10,15,0.1

200410,10/13/2004,10304,TORTU,Maxilaku,16,30,0

200410,10/13/2004,10304,TORTU,Raclette Courdavault,44,10,0

200410,10/13/2004,10304,TORTU,Fløtemysost,17.2,2,0

200410,10/14/2004,10305,OLDWO,Carnarvon Tigers,50,25,0.1

200410,10/14/2004,10305,OLDWO,Thüringer Rostbratwurst,99,25,0.1

200410,10/14/2004,10305,OLDWO,Chartreuse verte,14.4,30,0.1

200410,10/17/2004,10306,ROMEY,Nord-Ost Matjeshering,20.7,10,0

200410,10/17/2004,10306,ROMEY,Perth Pasties,26.2,10,0

200410,10/17/2004,10306,ROMEY,Tourtière,5.9,5,0

200410,10/18/2004,10307,LONEP,Scottish Longbreads,10,3,0

200410,10/18/2004,10307,LONEP,Tarte au sucre,39.4,10,0

200410,10/19/2004,10308,ANATR,Outback Lager,12,5,0

200410,10/19/2004,10308,ANATR,Gudbrandsdalsost,28.8,1,0

200410,10/20/2004,10309,HUNGO,Chef Anton's Cajun Seasoning,17.6,20,0

200410,10/20/2004,10309,HUNGO,Grandma's Boysenberry Spread,20,30,0

200410,10/20/2004,10309,HUNGO,Singaporean Hokkien Fried Mee,11.2,2,0

200410,10/20/2004,10309,HUNGO,Ipoh Coffee,36.8,20,0

200410,10/20/2004,10309,HUNGO,Fløtemysost,17.2,3,0

200410,10/21/2004,10310,THEBI,Pavlova,13.9,10,0

200410,10/21/2004,10310,THEBI,Tarte au sucre,39.4,5,0

200410,10/21/2004,10311,DUMON,Gudbrandsdalsost,28.8,7,0

200410,10/21/2004,10311,DUMON,Singaporean Hokkien Fried Mee,11.2,6,0

200410,10/24/2004,10312,WANDK,Rhönbräu Klosterbier,6.2,10,0

200410,10/24/2004,10312,WANDK,Perth Pasties,26.2,20,0

200410,10/24/2004,10312,WANDK,Rössle Sauerkraut,36.4,4,0

200410,10/24/2004,10312,WANDK,Ipoh Coffee,36.8,24,0

200410,10/25/2004,10313,QUICK,Inlagd Sill,15.2,12,0

200410,10/26/2004,10314,RATTC,Mascarpone Fabioli,25.6,40,0.1

200410,10/26/2004,10314,RATTC,Escargots de Bourgogne,10.6,30,0.1

200410,10/26/2004,10314,RATTC,Tarte au sucre,39.4,25,0.1

200410,10/27/2004,10315,ISLAT,Outback Lager,12,30,0

200410,10/27/2004,10315,ISLAT,Sasquatch Ale,11.2,14,0

200410,10/28/2004,10316,RATTC,Tarte au sucre,39.4,70,0

200410,10/28/2004,10316,RATTC,Jack's New England Clam Chowder,7.7,10,0

200410,10/31/2004,10317,LONEP,Chai,14.4,20,0

200411,11/1/2004,10318,ISLAT,Jack's New England Clam Chowder,7.7,20,0

200411,11/1/2004,10318,ISLAT,Lakkalikööri,14.4,6,0

200411,11/2/2004,10319,TORTU,Alice Mutton,31.2,8,0

200411,11/2/2004,10319,TORTU,Rössle Sauerkraut,36.4,14,0

200411,11/2/2004,10319,TORTU,Lakkalikööri,14.4,30,0

200411,11/3/2004,10320,WARTH,Fløtemysost,17.2,30,0

200411,11/3/2004,10321,ISLAT,Steeleye Stout,14.4,10,0

200411,11/4/2004,10322,PERIC,Filo Mix,5.6,20,0

200411,11/7/2004,10323,KOENE,NuNuCa Nuß-Nougat-Creme,11.2,4,0

200411,11/7/2004,10323,KOENE,Chartreuse verte,14.4,4,0

200411,11/7/2004,10323,KOENE,Genen Shouyu,12.4,5,0

200411,11/8/2004,10324,SAVEA,Pavlova,13.9,21,0.15

200411,11/8/2004,10324,SAVEA,Steeleye Stout,14.4,70,0.15

200411,11/8/2004,10324,SAVEA,Spegesild,9.6,30,0

200411,11/8/2004,10324,SAVEA,Raclette Courdavault,44,40,0.15

200411,11/8/2004,10324,SAVEA,Vegie-spread,35.1,80,0.15

200411,11/9/2004,10325,KOENE,Konbu,4.8,12,0

200411,11/9/2004,10325,KOENE,Mozzarella di Giovanni,27.8,40,0

200411,11/9/2004,10325,KOENE,Tofu,18.6,9,0

200411,11/9/2004,10325,KOENE,Grandma's Boysenberry Spread,20,6,0

200411,11/9/2004,10325,KOENE,Gorgonzola Telino,10,4,0

200411,11/10/2004,10326,BOLID,Chef Anton's Cajun Seasoning,17.6,24,0

200411,11/10/2004,10326,BOLID,Ravioli Angelo,15.6,16,0

200411,11/10/2004,10326,BOLID,Rhönbräu Klosterbier,6.2,50,0

200411,11/11/2004,10327,FOLKO,Escargots de Bourgogne,10.6,30,0.2

200411,11/11/2004,10327,FOLKO,Chang,15.2,25,0.2

200411,11/11/2004,10327,FOLKO,Queso Cabrales,16.8,50,0.2

200411,11/11/2004,10327,FOLKO,Nord-Ost Matjeshering,20.7,35,0.2

200411,11/14/2004,10328,FURIB,Scottish Longbreads,10,10,0

200411,11/14/2004,10328,FURIB,Raclette Courdavault,44,9,0

200411,11/14/2004,10328,FURIB,Louisiana Fiery Hot Pepper Sauce,16.8,40,0

200411,11/15/2004,10329,SPLIR,Teatime Chocolate Biscuits,7.3,10,0.05

200411,11/15/2004,10329,SPLIR,Nord-Ost Matjeshering,20.7,8,0.05

200411,11/15/2004,10329,SPLIR,Côte de Blaye,210.8,20,0.05

200411,11/15/2004,10329,SPLIR,Gnocchi di nonna Alice,30.4,12,0.05

200411,11/16/2004,10330,LILAS,Gumbär Gummibärchen,24.9,50,0.15

200411,11/16/2004,10330,LILAS,Mozzarella di Giovanni,27.8,25,0.15

200411,11/16/2004,10331,BONAP,Tourtière,5.9,15,0

200411,11/17/2004,10332,MEREP,Zaanse koeken,7.6,16,0.2

200411,11/17/2004,10332,MEREP,Singaporean Hokkien Fried Mee,11.2,10,0.2

200411,11/17/2004,10332,MEREP,Carnarvon Tigers,50,40,0.2

200411,11/18/2004,10333,WARTH,Tofu,18.6,10,0

200411,11/18/2004,10333,WARTH,Sir Rodney's Scones,8,10,0.1

200411,11/18/2004,10333,WARTH,Fløtemysost,17.2,40,0.1

200411,11/21/2004,10334,VICTE,Filo Mix,5.6,8,0

200411,11/21/2004,10334,VICTE,Scottish Longbreads,10,10,0

200411,11/22/2004,10335,HUNGO,Mascarpone Fabioli,25.6,6,0.2

200411,11/22/2004,10335,HUNGO,Manjimup Dried Apples,42.4,48,0.2

200411,11/22/2004,10335,HUNGO,Gorgonzola Telino,10,25,0.2

200411,11/22/2004,10335,HUNGO,Chang,15.2,7,0.2

200411,11/23/2004,10336,PRINI,Chef Anton's Cajun Seasoning,17.6,18,0.1

200411,11/24/2004,10337,FRANK,Tunnbröd,7.2,40,0

200411,11/24/2004,10337,FRANK,Gumbär Gummibärchen,24.9,24,0

200411,11/24/2004,10337,FRANK,Inlagd Sill,15.2,20,0

200411,11/24/2004,10337,FRANK,Gravad lax,20.8,28,0

200411,11/24/2004,10337,FRANK,Mozzarella di Giovanni,27.8,25,0

200411,11/25/2004,10338,OLDWO,Nord-Ost Matjeshering,20.7,15,0

200411,11/25/2004,10338,OLDWO,Alice Mutton,31.2,20,0

200411,11/28/2004,10339,MEREP,Tarte au sucre,39.4,28,0

200411,11/28/2004,10339,MEREP,Alice Mutton,31.2,70,0.05

200411,11/28/2004,10339,MEREP,Chef Anton's Cajun Seasoning,17.6,10,0

200411,11/29/2004,10340,BONAP,Carnarvon Tigers,50,20,0.05

200411,11/29/2004,10340,BONAP,Jack's New England Clam Chowder,7.7,12,0.05

200411,11/29/2004,10340,BONAP,Ipoh Coffee,36.8,40,0.05

200411,11/29/2004,10341,SIMOB,Geitost,2,8,0

200411,11/29/2004,10341,SIMOB,Raclette Courdavault,44,9,0.15

200411,11/30/2004,10342,FRANK,Inlagd Sill,15.2,40,0.2

200411,11/30/2004,10342,FRANK,Pâté chinois,19.2,40,0.2

200411,11/30/2004,10342,FRANK,Gorgonzola Telino,10,56,0.2

200411,11/30/2004,10342,FRANK,Chang,15.2,24,0.2

200412,12/1/2004,10343,LEHMS,Scottish Longbreads,10,4,0.05

200412,12/1/2004,10343,LEHMS,Lakkalikööri,14.4,15,0

200412,12/1/2004,10343,LEHMS,Wimmers gute Semmelknödel,26.6,50,0

200412,12/2/2004,10344,WHITC,Chef Anton's Cajun Seasoning,17.6,35,0

200412,12/2/2004,10344,WHITC,Northwoods Cranberry Sauce,32,70,0.25

200412,12/5/2004,10345,QUICK,Northwoods Cranberry Sauce,32,70,0

200412,12/5/2004,10345,QUICK,Teatime Chocolate Biscuits,7.3,80,0

200412,12/5/2004,10345,QUICK,Singaporean Hokkien Fried Mee,11.2,9,0

200412,12/6/2004,10346,RATTC,Alice Mutton,31.2,36,0.1

200412,12/6/2004,10346,RATTC,Gnocchi di nonna Alice,30.4,20,0

200412,12/7/2004,10347,FAMIA,Chartreuse verte,14.4,50,0.15

200412,12/7/2004,10347,FAMIA,Boston Crab Meat,14.7,4,0

200412,12/7/2004,10347,FAMIA,Rhönbräu Klosterbier,6.2,6,0.15

200412,12/7/2004,10347,FAMIA,NuNuCa Nuß-Nougat-Creme,11.2,10,0

200412,12/8/2004,10348,WANDK,Chai,14.4,15,0.15

200412,12/8/2004,10348,WANDK,Tunnbröd,7.2,25,0

200412,12/9/2004,10349,SPLIR,Tourtière,5.9,24,0

200412,12/12/2004,10350,LAMAI,Gudbrandsdalsost,28.8,18,0.1

200412,12/12/2004,10350,LAMAI,Valkoinen suklaa,13,15,0.1

200412,12/12/2004,10351,ERNSH,Louisiana Fiery Hot Pepper Sauce,16.8,10,0.05

200412,12/12/2004,10351,ERNSH,Gula Malacca,15.5,77,0.05

200412,12/12/2004,10351,ERNSH,Côte de Blaye,210.8,20,0.05

200412,12/12/2004,10351,ERNSH,Jack's New England Clam Chowder,7.7,13,0

200412,12/13/2004,10352,FURIB,Guaraná Fantástica,3.6,10,0

200412,12/13/2004,10352,FURIB,Tourtière,5.9,20,0.15

200412,12/14/2004,10353,PICCO,Queso Cabrales,16.8,12,0.2

200412,12/14/2004,10353,PICCO,Côte de Blaye,210.8,50,0.2

200412,12/15/2004,10354,PERIC,Thüringer Rostbratwurst,99,4,0

200412,12/15/2004,10354,PERIC,Chai,14.4,12,0

200412,12/16/2004,10355,AROUT,Ravioli Angelo,15.6,25,0

200412,12/16/2004,10355,AROUT,Guaraná Fantástica,3.6,25,0

200412,12/19/2004,10356,WANDK,Gorgonzola Telino,10,30,0

200412,12/19/2004,10356,WANDK,Pâté chinois,19.2,12,0

200412,12/19/2004,10356,WANDK,Gudbrandsdalsost,28.8,20,0

200412,12/20/2004,10357,LILAS,Ikura,24.8,30,0.2

200412,12/20/2004,10357,LILAS,Gumbär Gummibärchen,24.9,16,0

200412,12/20/2004,10357,LILAS,Camembert Pierrot,27.2,8,0.2

200412,12/21/2004,10358,LAMAI,Sasquatch Ale,11.2,10,0.05

200412,12/21/2004,10358,LAMAI,Inlagd Sill,15.2,20,0.05

200412,12/21/2004,10358,LAMAI,Guaraná Fantástica,3.6,10,0.05

200412,12/22/2004,10359,SEVES,Gorgonzola Telino,10,70,0.05

200412,12/22/2004,10359,SEVES,Camembert Pierrot,27.2,80,0.05

200412,12/22/2004,10359,SEVES,Pavlova,13.9,56,0.05

200412,12/23/2004,10360,BLONP,Rössle Sauerkraut,36.4,30,0

200412,12/23/2004,10360,BLONP,Thüringer Rostbratwurst,99,35,0

200412,12/23/2004,10360,BLONP,Côte de Blaye,210.8,10,0

200412,12/23/2004,10360,BLONP,Maxilaku,16,35,0

200412,12/23/2004,10360,BLONP,Tourtière,5.9,28,0

200412,12/23/2004,10361,QUICK,Chartreuse verte,14.4,54,0.1

200412,12/23/2004,10361,QUICK,Camembert Pierrot,27.2,55,0.1

200412,12/26/2004,10362,BONAP,NuNuCa Nuß-Nougat-Creme,11.2,50,0

200412,12/26/2004,10362,BONAP,Manjimup Dried Apples,42.4,20,0

200412,12/26/2004,10362,BONAP,Tourtière,5.9,24,0

200412,12/27/2004,10363,DRACD,Gorgonzola Telino,10,20,0

200412,12/27/2004,10363,DRACD,Rhönbräu Klosterbier,6.2,12,0

200412,12/27/2004,10363,DRACD,Lakkalikööri,14.4,12,0

200412,12/27/2004,10364,EASTC,Gudbrandsdalsost,28.8,30,0

200412,12/27/2004,10364,EASTC,Fløtemysost,17.2,5,0

200412,12/28/2004,10365,ANTON,Queso Cabrales,16.8,24,0

200412,12/29/2004,10366,GALED,Louisiana Fiery Hot Pepper Sauce,16.8,5,0

200412,12/29/2004,10366,GALED,Original Frankfurter grüne Soße,10.4,5,0

200412,12/29/2004,10367,VAFFE,Sasquatch Ale,11.2,36,0

200412,12/29/2004,10367,VAFFE,Tourtière,5.9,18,0

200412,12/29/2004,10367,VAFFE,Louisiana Fiery Hot Pepper Sauce,16.8,15,0

200412,12/29/2004,10367,VAFFE,Original Frankfurter grüne Soße,10.4,7,0

200412,12/30/2004,10368,ERNSH,Wimmers gute Semmelknödel,26.6,35,0.1

200412,12/30/2004,10368,ERNSH,Sir Rodney's Scones,8,5,0.1

200412,12/30/2004,10368,ERNSH,Rössle Sauerkraut,36.4,13,0.1

200412,12/30/2004,10368,ERNSH,Ravioli Angelo,15.6,25,0

Upon examination of the file, you can see that there is three months worth of data (several records for October, November, and December 2004, indicated by the first field values 200410, 200411, and 200412, respectively). We could say that the major control field is the Year-Month, forming control groups for the sets of records for each month (all the records with the value of 200410 in the first field is one control group, as is all the records with the value of 200411 in the first field, as is all the records with the value of 200412 in the first field). In a printed report, management would want to see subtotals for each month.

Within each month, we see that there are (or can be) several records for each date (for example, only one record for 10/3/2004, but four records for 10/10/2004, and six records for 12/12/2004). It would follow that management would want to see subtotals for each date in addition to the monthly totals.

A final breakdown would be by order number. An order can consist of more than one product; an examination of the data bears this out. For example, order number 10296 on 10/4/2004 consisted of three products; order number 10309 on 10/20/2004 consisted of five products. So we can see that subtotals for each order would also be required on the report.

The last sample program in this article will show you how to create a report with these three levels of control break subtotals (year-month, date, and order number) in addition to grand totals. A note on terminology: when you have two or more control breaks, the highest level break (in this case, year-month) is considered the major control break; the lowest level break (in this case, order number) is considered the minor control break; and any breaks in between (in this case, the date) are considered intermediate control breaks.

Four sample programs will be presented in this article, each one a variation on the other. The four sample programs will respectively produce:

· Sales report with no control breaks (grand totals only)

· Sales report with control break totals for year-month (as well as the grand totals)

· Sales report with control break totals for both year-month and date (as well as the grand totals)

· Sales report with control break totals for year-month, date, and order number (as well as the grand totals)

Excerpts of the outputs of each of the four sample programs are shown below. This will be followed by a discussion of the programming considerations for each, as well as links to download each sample program.

Sample Program 1

Sample Program 1 processes the Northwind input file described above and produces a sales report with grand totals only (no control breaks or subtotals). Excerpts of the output are shown below. Report headings, including the date, time, page number, and column headings are printed at the top of each page. A detail line is printed for each input record, consisting of the year-month, order date, order number, customer ID, product description, unit price, and quantity fields from the input record, along with the calculated fields extended price, discount amount, and sale total (the discount percentage from the input record is used for the calculations but is not itself printed on the report). Grand totals for the three calculated fields are printed in boldface type at the end of the report.

Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 1

Print Time: 21:36:35 NORTHWIND TRADERS SALES REPORT

WITH NO CONTROL BREAKS (GRAND TOTALS ONLY)

YEAR/ ORDER CUST UNIT EXTENDED DISCOUNT

MONTH ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

----- ---------- ------ -- ------------------- ----- --- ----- ------ ----------

200410 10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60

200410 10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00

200410 10/4/2004 10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00

200410 10/4/2004 10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60

200410 10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00

200410 10/5/2004 10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00

200410 10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00

200410 10/6/2004 10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00

200410 10/6/2004 10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00

200410 10/6/2004 10298 HUNGO Tarte au sucre 39.40 15 $591.00 $0.00 $591.00

. . .

200412 12/29/2004 10366 GALED Louisiana Fiery Hot Pepper Sauce 16.80 5 $84.00 $0.00 $84.00

200412 12/29/2004 10366 GALED Original Frankfurter grüne Soße 10.40 5 $52.00 $0.00 $52.00

200412 12/29/2004 10367 VAFFE Sasquatch Ale 11.20 36 $403.20 $0.00 $403.20

200412 12/29/2004 10367 VAFFE Tourtière 5.90 18 $106.20 $0.00 $106.20

200412 12/29/2004 10367 VAFFE Louisiana Fiery Hot Pepper Sauce 16.80 15 $252.00 $0.00 $252.00

200412 12/29/2004 10367 VAFFE Original Frankfurter grüne Soße 10.40 7 $72.80 $0.00 $72.80

200412 12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90

200412 12/30/2004 10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00

200412 12/30/2004 10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88

200412 12/30/2004 10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00

GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17

Sample Program 2

Sample Program 2 processes the same input file as Sample Program 1. The difference in the output is that Sample Program 2 produces subtotals based on the year-month field in addition to the grand totals. And instead of showing the year-month on each detail line, it is "factored out" and printed on a separate "sub-heading" line preceding the detail lines for that year-month. Furthermore, the year-month is formatted with the month name and year (i.e., OCTOBER, 2004 as opposed to 200410).

Excerpts of the output report produced by Sample Program 2 are shown below. Note how the month/year is printed on a separate line in bold at the start of each month/year group. Note also that subtotals for the month/year are printed in bold whenever there is a change (or break) in the month/year groups. As in Sample Program 1, grand totals are printed in bold at the end of the report.

Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 1

Print Time: 21:51:39 NORTHWIND TRADERS SALES REPORT

WITH CONTROL BREAK ON MONTH

ORDER CUST UNIT EXTENDED DISCOUNT

ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

---------- ------ -- ------------------- ----- --- ----- ------ ----------

OCTOBER, 2004

10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60

10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00

10/4/2004 10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00

10/4/2004 10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60

10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00

10/5/2004 10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00

10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00

10/6/2004 10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00

10/6/2004 10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00

. . .

10/28/2004 10316 RATTC Tarte au sucre 39.40 70 $2,758.00 $0.00 $2,758.00

10/28/2004 10316 RATTC Jack's New England Clam Chowder 7.70 10 $77.00 $0.00 $77.00

10/31/2004 10317 LONEP Chai 14.40 20 $288.00 $0.00 $288.00

----------- ----------- -----------

TOTALS FOR OCTOBER, 2004: $27,636.00 $1,254.60 $26,381.40

NOVEMBER, 2004

11/1/2004 10318 ISLAT Jack's New England Clam Chowder 7.70 20 $154.00 $0.00 $154.00

11/1/2004 10318 ISLAT Lakkalikööri 14.40 6 $86.40 $0.00 $86.40

11/2/2004 10319 TORTU Alice Mutton 31.20 8 $249.60 $0.00 $249.60

11/2/2004 10319 TORTU Rössle Sauerkraut 36.40 14 $509.60 $0.00 $509.60

11/2/2004 10319 TORTU Lakkalikööri 14.40 30 $432.00 $0.00 $432.00

. . .

11/30/2004 10342 FRANK Inlagd Sill 15.20 40 $608.00 $121.60 $486.40

11/30/2004 10342 FRANK Pâté chinois 19.20 40 $768.00 $153.60 $614.40

11/30/2004 10342 FRANK Gorgonzola Telino 10.00 56 $560.00 $112.00 $448.00

11/30/2004 10342 FRANK Chang 15.20 24 $364.80 $72.96 $291.84

----------- ----------- -----------

TOTALS FOR NOVEMBER, 2004: $39,617.60 $3,685.88 $35,931.72

DECEMBER, 2004

12/1/2004 10343 LEHMS Scottish Longbreads 10.00 4 $40.00 $2.00 $38.00

12/1/2004 10343 LEHMS Lakkalikööri 14.40 15 $216.00 $0.00 $216.00

12/1/2004 10343 LEHMS Wimmers gute Semmelknödel 26.60 50 $1,330.00 $0.00 $1,330.00

. . .

12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90

12/30/2004 10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00

12/30/2004 10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88

12/30/2004 10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00

----------- ----------- -----------

TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05

GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17

Sample Program 3

Sample Program 3 builds on Sample Program 2. In addition to the year-month control break implemented in Sample Program 2, Sample Program 3 implements an additional control break on the order date.

Excerpts of the output report produced by Sample Program 3 are shown below. Note that the report now includes subtotals by order date in addition to subtotals by year-month. Note also that for each group of items printed where the order date is the same, the date is only displayed on the first line of that group; it is blanked out for the remaining lines. This technique is called group indication. (We are also "group indicating" by printing the year/month in its own sub-heading.) The idea behind group indication is to improve readability of the report and suppress redundant information.

As in both Sample Program 2 and Sample Program 1, grand totals are printed in bold at the end of the report.

Print Date: 05/01/05 THEVBPROGRAMMER.COM Page: 1

Print Time: 14:08:22 NORTHWIND TRADERS SALES REPORT

WITH CONTROL BREAKS ON DATE AND MONTH

ORDER CUST UNIT EXTENDED DISCOUNT

ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

---------- ------ -- ------------------- ----- --- ----- ------ ----------

OCTOBER, 2004

10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60

----------- ----------- -----------

TOTALS FOR 10/3/2004: $121.60 $0.00 $121.60

10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00

10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00

10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60

----------- ----------- -----------

TOTALS FOR 10/4/2004: $1,050.60 $0.00 $1,050.60

10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00

10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00

----------- ----------- -----------

TOTALS FOR 10/5/2004: $1,420.00 $0.00 $1,420.00

10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00

10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00

10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00

10298 HUNGO Tarte au sucre 39.40 15 $591.00 $0.00 $591.00

----------- ----------- -----------

TOTALS FOR 10/6/2004: $3,127.00 $482.00 $2,645.00

. . .

Print Date: 05/01/05 THEVBPROGRAMMER.COM Page: 11

Print Time: 14:08:22 NORTHWIND TRADERS SALES REPORT

WITH CONTROL BREAKS ON DATE AND MONTH

ORDER CUST UNIT EXTENDED DISCOUNT

ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

---------- ------ -- ------------------- ----- --- ----- ------ ----------

----------- ----------- -----------

TOTALS FOR 12/29/2004: $970.20 $0.00 $970.20

12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90

10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00

10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88

10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00

----------- ----------- -----------

TOTALS FOR 12/30/2004: $1,834.20 $144.42 $1,689.78

TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05

GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17

Sample Program 4

Excerpts of the output report produced by Sample Program 4 are shown below. Note that the report now includes subtotals by order number in addition to subtotals by year-month and order date. Note also that the technique of group indication has been applied to the order number (and its associated field, the customer ID, because an order is always associated with a single customer). The order number and customer ID are displayed on the first line of the group of records associated with an order, but blanked out (suppressed) for the remaining lines of the group. As mentioned earlier, the idea behind group indication is to improve readability of the report and suppress redundant information.

As in both Sample Programs 1, 2, and 3, grand totals are printed in bold at the end of the report.

Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 14

Print Time: 22:03:35 NORTHWIND TRADERS SALES REPORT

WITH BREAKS ON ORDER, DATE, AND MONTH

ORDER CUST UNIT EXTENDED DISCOUNT

ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

---------- ------ -- ------------------- ----- --- ----- ------ ----------

TOTALS FOR 12/23/2004: $9,663.80 $227.36 $9,436.44

12/26/2004 10362 BONAP NuNuCa Nuß-Nougat-Creme 11.20 50 $560.00 $0.00 $560.00

Manjimup Dried Apples 42.40 20 $848.00 $0.00 $848.00

Tourtière 5.90 24 $141.60 $0.00 $141.60

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10362: $1,549.60 $0.00 $1,549.60

TOTALS FOR 12/26/2004: $1,549.60 $0.00 $1,549.60

12/27/2004 10363 DRACD Gorgonzola Telino 10.00 20 $200.00 $0.00 $200.00

Rhönbräu Klosterbier 6.20 12 $74.40 $0.00 $74.40

Lakkalikööri 14.40 12 $172.80 $0.00 $172.80

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10363: $447.20 $0.00 $447.20

10364 EASTC Gudbrandsdalsost 28.80 30 $864.00 $0.00 $864.00

Fløtemysost 17.20 5 $86.00 $0.00 $86.00

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10364: $950.00 $0.00 $950.00

TOTALS FOR 12/27/2004: $1,397.20 $0.00 $1,397.20

12/28/2004 10365 ANTON Queso Cabrales 16.80 24 $403.20 $0.00 $403.20

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10365: $403.20 $0.00 $403.20

TOTALS FOR 12/28/2004: $403.20 $0.00 $403.20

12/29/2004 10366 GALED Louisiana Fiery Hot Pepper Sauce 16.80 5 $84.00 $0.00 $84.00

Original Frankfurter grüne Soße 10.40 5 $52.00 $0.00 $52.00

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10366: $136.00 $0.00 $136.00

10367 VAFFE Sasquatch Ale 11.20 36 $403.20 $0.00 $403.20

Tourtière 5.90 18 $106.20 $0.00 $106.20

Louisiana Fiery Hot Pepper Sauce 16.80 15 $252.00 $0.00 $252.00

Original Frankfurter grüne Soße 10.40 7 $72.80 $0.00 $72.80

Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 15

Print Time: 22:03:35 NORTHWIND TRADERS SALES REPORT

WITH BREAKS ON ORDER, DATE, AND MONTH

ORDER CUST UNIT EXTENDED DISCOUNT

ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL

---------- ------ -- ------------------- ----- --- ----- ------ ----------

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10367: $834.20 $0.00 $834.20

TOTALS FOR 12/29/2004: $970.20 $0.00 $970.20

12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90

Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00

Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88

Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00

----------- ----------- -----------

TOTALS FOR ORDER NUMBER 10368: $1,834.20 $144.42 $1,689.78

TOTALS FOR 12/30/2004: $1,834.20 $144.42 $1,689.78

TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05

GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17

When programming reports "by hand" like this, you must account for "report printing logic", such as printing headings on every page, tracking the page count, knowing when to perform a page break by tracking the number of lines currently printed on the page, designing the detail lines so that all fields fit within the maximum number of characters per line, etc. By using a monospaced font like Courier New, we can determine the number of characters that will fit on one line and the number of lines that will fit on one page. Note: A monospaced font is one in which each character takes up the same amount of space, like a typewriter. Examples of monospaced fonts are Courier and Lucida Console. This is contrasted with proportional fonts, where the width of each letter varies (for example, a "W" is much wider than an "i"). Examples of proportional fonts are Times New Roman and Arial (this font). By knowing the number of characters that can fit on one line, we can plan the format of the data to be presented. By knowing the number of lines that will fit on one page, we can use logic to perform page breaks and print headings when a page fills up with data.

Programmers in the days of yore used a printer spacing chart form to design printouts. An actual printer spacing chart form might be hard to come by these days, but any type of form with grid lines like this would get the job done. A freeware Excel template file that replicates the printer spacing chart (as well as other "old-school" forms) is available. To use the Excel template, either print the blank sheet and pencil your design in, or copy the template to a separate work file and enter your design directly in the cells of the Excel template worksheet. The latter method was used to produce the example shown below. The Excel template file, as well as the file containing the sample chart below, can be downloaded here.

In these sample programs, the reports are laid out in landscape orientation, and Courier New 9 point is the font of choice. Taking margins into consideration, it was determined that 118 characters per line was an appropriate limit. The sample programs use the PrintDocument object to handle printing, and in the PrintPage event, a calculation is performed to yield the maximum number of lines per page based on the font size. In that the PrintPage event fires whenever a new page needs to be printed, we can use that information to keep the page count.

A major challenge when converting these sample programs over to VB.NET from their classic VB counterparts was the use of the PrintDocument object, which is event-driven. I found that this clashed with the procedure-oriented way that these report programs need to be written; where the control break logic is intertwined with the report logic. The solution I came up with was to write all the report lines (except for the page heading lines) to an array, then print from the array.

I will now go over some of the logic involved in the sample programs.

First, all of the sample programs use the Input statement to process the input text file (recall that the Input statement reads one field at a time from a comma-delimited file; and you typically use a set of Input statements to read one "record's worth" of fields at a time). In the sample program, a set of form-level variables to represent the fields from the input record is declared:

Private mstrYearMonth As String

Private mstrOrderNbr As String

Private mstrOrderDate As String

Private mstrCustID As String

Private mstrProdDesc As String

Private msngUnitPrice As Single

Private mintQty As Integer

Private msngDiscountPct As Single

The form-level variable mintSalesFileNbr is used for the file number. So to read a "record" from the input file, the following set of Input statements is used:

Input(mintSalesFileNbr, mstrYearMonth)

Input(mintSalesFileNbr, mstrOrderDate)

Input(mintSalesFileNbr, mstrOrderNbr)

Input(mintSalesFileNbr, mstrCustID)

Input(mintSalesFileNbr, mstrProdDesc)

Input(mintSalesFileNbr, msngUnitPrice)

Input(mintSalesFileNbr, mintQty)

Input(mintSalesFileNbr, msngDiscountPct)

The Input statement detects end-of-file (EOF) at the same time the last record in the file is read. That is why a normal file processing loop looks like this:

Do Until EOF(FileNumber)

Input(FileNumber, variable)

'Process current record

Loop

However, for some algorithms (particularly control break processing), it is more convenient have the end of file condition triggered on a separate iteration of the processing loop. (Doing this we are basically delaying the testing for the end-of-file condition on the input text file until after the last record has been read.) This is the type of "input" or "read" loop that uses a priming read outside the loop; all subsequent reads occur at the bottom of the loop. The pseudocode might be written as follows:

READ A RECORD ' priming read

DO UNTIL EOF

PROCESS THE RECORD

READ A RECORD ' second and subsequent reads

LOOP

To simulate this behavior, the sample programs use the ReadSalesRecord Sub to read a record. The setup is as follows: a form-level Boolean variable named mblnEOF is declared, initialized to False. The ReadSalesRecord Sub is coded as follows:

Private Sub ReadSalesRecord()

If EOF(mintSalesFileNbr) Then

mblnEOF = True

Else

Input(mintSalesFileNbr, mstrYearMonth)

Input(mintSalesFileNbr, mstrOrderDate)

Input(mintSalesFileNbr, mstrOrderNbr)

Input(mintSalesFileNbr, mstrCustID)

Input(mintSalesFileNbr, mstrProdDesc)

Input(mintSalesFileNbr, msngUnitPrice)

Input(mintSalesFileNbr, mintQty)

Input(mintSalesFileNbr, msngDiscountPct)

End If

End Sub

And the loop to process the file looks like this:

Call ReadSalesRecord()

Do Until mblnEOF

' process the data for the current record here

' ...

' read the next record

Call ReadSalesRecord()

Loop

With this coding, when the last record is read, the system "knows" that EOF has occurred, but your processing loop won't "know" it until it calls the Sub one last time, at which point the condition EOF(mintSalesFileNbr) will be True, which will cause the form-level Boolean variable mblnEOF to be set to True.

The interface for all four sample programs is identical, except for the title bar description. The interface consists of two buttons, named btnPrint and btnExit, with all of the logic taking place behind btnPrint.

The code behind the "Print" button is identical for all four programs:

Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click

' Generate the set of detail lines to be printed ...

GenerateDetailLineArray()

' Declare an object for the PrintDocument class...

Dim objPrintDocument As PrintDocument = New PrintDocument()

' set the orientation to Landscape ...

objPrintDocument.DefaultPageSettings.Landscape = True

' Set the print font...

mobjPrintFont = New Font("Courier New", 9)

' Add an event handler for the PrintPage event of the

' objPrintDocument object...

AddHandler objPrintDocument.PrintPage, AddressOf objPrintDocument_PrintPage

With PrintPreviewDialog1

.FindForm().StartPosition = FormStartPosition.CenterScreen

.Height = 600

.Width = 800

.PrintPreviewControl.Zoom = 0.75

.Document = objPrintDocument

.ShowDialog()

End With

End Sub

As mentioned earlier, I decided to resolve my control break logic / report logic conflict issues by "writing" the entire report (except for the page heading lines) to an array before doing any printing. This is taken care of in a Sub called GenerateDetailLineArray. You can see from the code above that this is the first thing that is done. It is in the GenerateDetailLineArray Sub that the differences in logic between the four sample programs occurs.

In all cases, the array that is being populated is declared as follows:

Private mastrDetailLines() As String

Notes on Sample Program 1

In the first sample program (which does not do any control breaks; only grand totals are generated), the GenerateDetailLineArray is coded as follows:

Private Sub GenerateDetailLineArray()

Dim decExtPriceDtl As Decimal

Dim decDiscAmtDtl As Decimal

Dim decSaleTotDtl As Decimal

Dim decExtPriceGrand As Decimal = 0

Dim decDiscAmtGrand As Decimal = 0

Dim decSaleTotGrand As Decimal = 0

mintLineX = -1

Erase mastrDetailLines

mblnEOF = False

' open the customer data file ...

mintSalesFileNbr = FreeFile()

FileOpen(mintSalesFileNbr, mstrSalesFileName, OpenMode.Input)

' priming read

Call ReadSalesRecord()

' main outer processing loop

Do Until mblnEOF

decExtPriceDtl = msngUnitPrice * mintQty

decDiscAmtDtl = decExtPriceDtl * msngDiscountPct

decSaleTotDtl = decExtPriceDtl - decDiscAmtDtl

' generate a detail line

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(10) _

& mstrOrderDate.PadRight(10) _

& Space(1) _

& mstrOrderNbr.PadRight(5) _

& Space(2) _

& mstrCustID.PadRight(5) _

& Space(3) _

& mstrProdDesc.PadRight(33) _

& Space(1) _

& FormatNumber(msngUnitPrice, 2).PadLeft(6) _

& Space(3) _

& mintQty.ToString.PadLeft(3) _

& Space(2) _

& FormatCurrency(decExtPriceDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDtl, 2).PadLeft(11)

' "roll" the detail amounts to the grand totals

decExtPriceGrand += decExtPriceDtl

decDiscAmtGrand += decDiscAmtDtl

decSaleTotGrand += decSaleTotDtl

' read the next record

Call ReadSalesRecord()

Loop

' end of outer processing loop - time to generate the grand totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(70) _

& "GRAND TOTALS:" _

& Space(1) _

& FormatCurrency(decExtPriceGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotGrand, 2).PadLeft(11)

' close the input file

FileClose(mintSalesFileNbr)

End Sub

In the code above, the variables decExtPriceDtl, decDiscAmtDtl, and decSaleTotDtl are the variables used for the calculated fields (extended price, discount amount, and sale total). The variables decExtPriceGrand, decDiscAmtGrand, and decSaleTotGrand are the variables for the corresponding grand total. As each input record is read, the calculated field variables are computed, a detail line is formatted and added to the array, and then the calculated values are added to their corresponding grand total variables. After the file has been processed, the grand total line is generated and added to the array.

Notes on Sample Program 2

In the second sample program (which does a control break by month in addition to generating grand totals), the GenerateDetailLineArray is coded as follows:

Private Sub GenerateDetailLineArray()

Dim strPrevMonth As String

Dim decExtPriceDtl As Decimal

Dim decDiscAmtDtl As Decimal

Dim decSaleTotDtl As Decimal

Dim decExtPriceMonth As Decimal

Dim decDiscAmtMonth As Decimal

Dim decSaleTotMonth As Decimal

Dim decExtPriceGrand As Decimal

Dim decDiscAmtGrand As Decimal

Dim decSaleTotGrand As Decimal

mintLineX = -1

Erase mastrDetailLines

mblnEOF = False

' open the customer data file ...

mintSalesFileNbr = FreeFile()

FileOpen(mintSalesFileNbr, mstrSalesFileName, OpenMode.Input)

' priming read

Call ReadSalesRecord()

' main outer processing loop

Do Until mblnEOF

' set up for month break

strPrevMonth = mstrYearMonth

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = UCase(MonthName(CInt(Strings.Right(mstrYearMonth, 2)))) & ", " _

& Strings.Left(mstrYearMonth, 4)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

decExtPriceMonth = 0

decDiscAmtMonth = 0

decSaleTotMonth = 0

' inner loop 1 for month break

Do Until (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

decExtPriceDtl = msngUnitPrice * mintQty

decDiscAmtDtl = decExtPriceDtl * msngDiscountPct

decSaleTotDtl = decExtPriceDtl - decDiscAmtDtl

' generate a detail line

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(10) _

& mstrOrderDate.PadRight(10) _

& Space(1) _

& mstrOrderNbr.PadRight(5) _

& Space(2) _

& mstrCustID.PadRight(5) _

& Space(3) _

& mstrProdDesc.PadRight(33) _

& Space(1) _

& FormatNumber(msngUnitPrice, 2).PadLeft(6) _

& Space(3) _

& mintQty.ToString.PadLeft(3) _

& Space(2) _

& FormatCurrency(decExtPriceDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDtl, 2).PadLeft(11)

' "roll" the detail amounts to the month totals

decExtPriceMonth += decExtPriceDtl

decDiscAmtMonth += decDiscAmtDtl

decSaleTotMonth += decSaleTotDtl

' read the next record

Call ReadSalesRecord()

Loop

' end of inner loop 1

' we have our break on month, so generate the month totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(84) _

& " ----------" _

& Space(1) _

& " ----------" _

& Space(1) _

& " ----------"

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = ("TOTALS FOR " & UCase(MonthName(CInt(Strings.Right(strPrevMonth, 2)))) & ", " _

& Strings.Left(strPrevMonth, 4) & ":").PadLeft(83) _

& Space(1) _

& FormatCurrency(decExtPriceMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotMonth, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the month totals to the grand totals

decExtPriceGrand += decExtPriceMonth

decDiscAmtGrand += decDiscAmtMonth

decSaleTotGrand += decSaleTotMonth

Loop

' end of outer processing loop - time to generate the grand totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(70) _

& "GRAND TOTALS:" _

& Space(1) _

& FormatCurrency(decExtPriceGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotGrand, 2).PadLeft(11)

' close the input file

FileClose(mintSalesFileNbr)

End Sub

In examining the code above, the variable strPrevMonth will be needed to compare the current contents of the year-month field with its previous contents to detect when a change in that field occurs. Also, because we will now have an additional set of totals at the year-month level for the calculated fields extended price, discount amount, and sale total, we declare the variables decExtPriceMonth, decDiscAmtMonth, and decSaleTotMonth for that purpose.

After the start of the main processing loop (after the line Do Until mblnEOF), there are several "setup" steps are coded to provide for the year/month control break. The statement

strPrevMonth = mstrYearMonth

is critical because saves the value of the current year-month field (mstrYearMonth) to the "comparison" variable strPrevMonth.

To deal with printing the "month, year" sub-heading, some nested functions are used to convert what might be "200410" to "OCTOBER, 2004". If we break down UCase(MonthName(CInt(Strings.Right(mstrYearMonth, 2)))), the segment Strings.Right(mstrYearMonth, 2) would give you the string "10", the CInt function then converts that to an integer data type, the MonthName function would then return "October", then the UCase function would convert that to "OCTOBER". All that is concatenated with a comma followed by Strings.Left(mstrYearMonth, 4) which would give you "2004".

The statements

decExtPriceMonth = 0

decDiscAmtMonth = 0

decSaleTotMonth = 0

are critical to producing accurate subtotals at the year-month level. These variables must be cleared at the start of every year-month group.

The statement

Do Until (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

sets up an inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevMonth <> mstrYearMonth)" means that the loop will keep executing until there is a change (or break) in the mstrYearMonth field – the condition checks the value of strPrevMonth (which we have set in the setup steps above) against the value of mstrYearMonth, which is refreshed every time an input record is read. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs.

The statements

decExtPriceMonth += decExtPriceDtl

decDiscAmtMonth += decDiscAmtDtl

decSaleTotMonth += decSaleTotDtl

add the calculated fields at the detail level to their corresponding year-month totals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals – but in Sample Program 2, there is the additional year-month subtotal level, and it is these year-month subtotal accumulators to which the detail variables must be added. The rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.

The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the year-month is detected (which is basically to print the year-month subtotals and roll the year-month subtotals to the grand totals):

Once we move past the

Loop

statement, this means a change has occurred in the value of the year-month field. We have printed all the detail records for one month, and we have the first record of the next month in memory waiting to be processed. However, before we start processing the new month, we have some unfinished business to take care of with the previous month. First, we must generate a subtotal line for the previous month; then the year/month totals are added to their corresponding grand totals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)

decExtPriceGrand += decExtPriceMonth

decDiscAmtGrand += decDiscAmtMonth

decSaleTotGrand += decSaleTotMonth

From that point on, the code for this program is the same as its predecessor. (The Loop statement returns the program to the top of the main outer processing loop, which will do the setup for the next year/month group, process the detail records for that group, generate the group's subtotals, roll those subtotals to the grand totals, and so on for each group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.

In the third sample program (which does control breaks by month and date in addition to generating grand totals), the GenerateDetailLineArray is coded as follows:

Private Sub GenerateDetailLineArray()

Dim strPrevMonth As String

Dim strPrevDate As String

Dim decExtPriceDtl As Decimal

Dim decDiscAmtDtl As Decimal

Dim decSaleTotDtl As Decimal

Dim decExtPriceDate As Decimal

Dim decDiscAmtDate As Decimal

Dim decSaleTotDate As Decimal

Dim decExtPriceMonth As Decimal

Dim decDiscAmtMonth As Decimal

Dim decSaleTotMonth As Decimal

Dim decExtPriceGrand As Decimal

Dim decDiscAmtGrand As Decimal

Dim decSaleTotGrand As Decimal

mintLineX = -1

Erase mastrDetailLines

mblnEOF = False

' open the customer data file ...

mintSalesFileNbr = FreeFile()

FileOpen(mintSalesFileNbr, mstrSalesFileName, OpenMode.Input)

' priming read

Call ReadSalesRecord()

' main outer processing loop

Do Until mblnEOF

' set up for month break

strPrevMonth = mstrYearMonth

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = UCase(MonthName(CInt(Strings.Right(mstrYearMonth, 2)))) & ", " _

& Strings.Left(mstrYearMonth, 4)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

decExtPriceMonth = 0

decDiscAmtMonth = 0

decSaleTotMonth = 0

' inner loop 1 for month break

Do Until (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

' set up for date break

strPrevDate = mstrOrderDate

decExtPriceDate = 0

decDiscAmtDate = 0

decSaleTotDate = 0

' inner loop 2 for date break

Do Until (strPrevDate <> mstrOrderDate) _

Or (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

decExtPriceDtl = msngUnitPrice * mintQty

decDiscAmtDtl = decExtPriceDtl * msngDiscountPct

decSaleTotDtl = decExtPriceDtl - decDiscAmtDtl

' generate a detail line

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(10) _

& mstrOrderDate.PadRight(10) _

& Space(1) _

& mstrOrderNbr.PadRight(5) _

& Space(2) _

& mstrCustID.PadRight(5) _

& Space(3) _

& mstrProdDesc.PadRight(33) _

& Space(1) _

& FormatNumber(msngUnitPrice, 2).PadLeft(6) _

& Space(3) _

& mintQty.ToString.PadLeft(3) _

& Space(2) _

& FormatCurrency(decExtPriceDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDtl, 2).PadLeft(11)

' "roll" the detail amounts to the date totals

decExtPriceDate += decExtPriceDtl

decDiscAmtDate += decDiscAmtDtl

decSaleTotDate += decSaleTotDtl

' read the next record

Call ReadSalesRecord()

Loop

' end of inner loop 2

' we have our break on date, so print the date totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(84) _

& " ----------" _

& Space(1) _

& " ----------" _

& Space(1) _

& " ----------"

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = ("TOTALS FOR " & strPrevDate & ":").PadLeft(83) _

& Space(1) _

& FormatCurrency(decExtPriceDate, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDate, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDate, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the date totals to the month totals

decExtPriceMonth += decExtPriceDate

decDiscAmtMonth += decDiscAmtDate

decSaleTotMonth += decSaleTotDate

Loop

' end of inner loop 1

' we have our break on month, so generate the month totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = ("TOTALS FOR " & UCase(MonthName(CInt(Strings.Right(strPrevMonth, 2)))) & ", " _

& Strings.Left(strPrevMonth, 4) & ":").PadLeft(83) _

& Space(1) _

& FormatCurrency(decExtPriceMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotMonth, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the month totals to the grand totals

decExtPriceGrand += decExtPriceMonth

decDiscAmtGrand += decDiscAmtMonth

decSaleTotGrand += decSaleTotMonth

Loop

' end of outer processing loop - time to generate the grand totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(70) _

& "GRAND TOTALS:" _

& Space(1) _

& FormatCurrency(decExtPriceGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotGrand, 2).PadLeft(11)

' close the input file

FileClose(mintSalesFileNbr)

End Sub

Notes on Sample Program 3

Sample Program 3 builds on Sample Program 2. In addition to the year-month control break implemented in Sample Program 2, Sample Program 3 implements an additional control break on the order date. The report now includes subtotals by order date in addition to subtotals by year-month.

In the code above, the variable strPrevDate will be needed to compare the current contents of the order date field with its previous contents to detect when a change in that field occurs. Also, because we will now have an additional set of totals at the date level for the calculated fields extended price, discount amount, and sale total, we declare the variables decExtPriceDate, decDiscAmtDate, and decSaleTotDate for that purpose.

After the Do Until loop is set up for the year/month break (Do Until (strPrevMonth <> mstrYearMonth) Or (mblnEOF)), we perform the setup steps for the control break on the order date field.

The statement

strPrevDate = mstrOrderDate

performs the critical step of saving the value of the current date field (mstrOrderDate) to the "comparison" variable strPrevDate.

The statements

decExtPriceDate = 0

decDiscAmtDate = 0

decSaleTotDate = 0

perform the critical step of clearing the date subtotals, which must be cleared at the start of every date group.

The statement

Do Until (strPrevDate <> mstrOrderDate) _

Or (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

sets up a second inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevDate <> mstrOrderDate)" means that the loop will keep executing until there is a change (or break) in the mstrOrderDate field – the condition checks the value of strPrevDate (which we have set in the setup steps above) against the value of mstrOrderDate, which is refreshed every time an input record is read. Note also that we are checking for a change in the year/month field (hence the piece Or (strPrevMonth <> mstrYearMonth)). In a control-break program, a break in a higher level automatically causes a break in all lower levels. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs. It should be noted that the end-of-file condition is the "mother" of all control breaks – end-of-file automatically causes a break in all control levels.

The statements

decExtPriceDate += decExtPriceDtl

decDiscAmtDate += decDiscAmtDtl

decSaleTotDate += decSaleTotDtl

add the calculated fields at the detail level to their corresponding date totals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals; in Sample Program 2, the calculated fields at the detail level were added to their corresponding year-month totals; now in Sample Program 3, there is the additional date subtotal level, and it is these date subtotal accumulators to which the detail variables must be added. As mentioned earlier, the rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.

The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the order date is detected (which is basically to print the date subtotals and roll the date subtotals to the year-month subtotals):

Once we move past the

Loop

statement, this means a change has occurred in the value of the order date field. We have printed all the detail records for one date, and we have the first record for the next date in memory waiting to be processed. However, before we start processing the new date, we have some unfinished business to take care of with the previous date. First, we must generate the subtotal line for the previous date. Then, we must add the date subtotals to their corresponding year/month subtotals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)

decExtPriceMonth += decExtPriceDate

decDiscAmtMonth += decDiscAmtDate

decSaleTotMonth += decSaleTotDate

From that point on, the code for this program is very similar to its precedessor. (The Loop statement returns the program to the top of the first inner processing loop, which will do the setup for the next date group, process the detail records for that group, generate the date group's subtotals, roll those subtotals to the year/month subtotals, and so on for each date group. When there is a break on year/month, control will return to the main outer processing loop, which will do the setup for the next year/month group, process the date groups for that year/month group, generate the year/month group's subtotals, roll those year/month subtotals to the grand totals, and so on for each year/month group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.

Notes on Sample Program 4

In the fourth sample program (which does control breaks by month, date, and order number in addition to generating grand totals), the GenerateDetailLineArray is coded as follows:

Private Sub GenerateDetailLineArray()

Dim strPrevMonth As String

Dim strPrevDate As String

Dim strPrevOrderNbr As String

Dim decExtPriceDtl As Decimal

Dim decDiscAmtDtl As Decimal

Dim decSaleTotDtl As Decimal

Dim decExtPriceOrd As Decimal

Dim decDiscAmtOrd As Decimal

Dim decSaleTotOrd As Decimal

Dim decExtPriceDate As Decimal

Dim decDiscAmtDate As Decimal

Dim decSaleTotDate As Decimal

Dim decExtPriceMonth As Decimal

Dim decDiscAmtMonth As Decimal

Dim decSaleTotMonth As Decimal

Dim decExtPriceGrand As Decimal

Dim decDiscAmtGrand As Decimal

Dim decSaleTotGrand As Decimal

mintLineX = -1

Erase mastrDetailLines

mblnEOF = False

' open the customer data file ...

mintSalesFileNbr = FreeFile()

FileOpen(mintSalesFileNbr, mstrSalesFileName, OpenMode.Input)

' priming read

Call ReadSalesRecord()

' main outer processing loop

Do Until mblnEOF

' set up for month break

strPrevMonth = mstrYearMonth

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = UCase(MonthName(CInt(Strings.Right(mstrYearMonth, 2)))) & ", " _

& Strings.Left(mstrYearMonth, 4)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

decExtPriceMonth = 0

decDiscAmtMonth = 0

decSaleTotMonth = 0

' inner loop 1 for month break

Do Until (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

' set up for date break

strPrevDate = mstrOrderDate

decExtPriceDate = 0

decDiscAmtDate = 0

decSaleTotDate = 0

' inner loop 2 for date break

Do Until (strPrevDate <> mstrOrderDate) _

Or (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

' set up for order break

strPrevOrderNbr = mstrOrderNbr

decExtPriceOrd = 0

decDiscAmtOrd = 0

decSaleTotOrd = 0

' inner loop 3 for order number break

Do Until (strPrevOrderNbr <> mstrOrderNbr) _

Or (strPrevDate <> mstrOrderDate) _

Or (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

decExtPriceDtl = msngUnitPrice * mintQty

decDiscAmtDtl = decExtPriceDtl * msngDiscountPct

decSaleTotDtl = decExtPriceDtl - decDiscAmtDtl

' generate a detail line

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(10) _

& mstrOrderDate.PadRight(10) _

& Space(1) _

& mstrOrderNbr.PadRight(5) _

& Space(2) _

& mstrCustID.PadRight(5) _

& Space(3) _

& mstrProdDesc.PadRight(33) _

& Space(1) _

& FormatNumber(msngUnitPrice, 2).PadLeft(6) _

& Space(3) _

& mintQty.ToString.PadLeft(3) _

& Space(2) _

& FormatCurrency(decExtPriceDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDtl, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDtl, 2).PadLeft(11)

' "roll" the detail amounts to the order totals

decExtPriceOrd += decExtPriceDtl

decDiscAmtOrd += decDiscAmtDtl

decSaleTotOrd += decSaleTotDtl

' read the next record

Call ReadSalesRecord()

Loop

' end of inner loop 3

' we have our break on order number, so generate the order totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(84) _

& "-----------" _

& Space(1) _

& "-----------" _

& Space(1) _

& "-----------"

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(53) _

& "TOTALS FOR ORDER NUMBER " & strPrevOrderNbr & ":" _

& Space(1) _

& FormatCurrency(decExtPriceOrd, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtOrd, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotOrd, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the order totals to the date totals

decExtPriceDate += decExtPriceOrd

decDiscAmtDate += decDiscAmtOrd

decSaleTotDate += decSaleTotOrd

Loop

' end of inner loop 2

' we have our break on date, so print the date totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = ("TOTALS FOR " & strPrevDate & ":").PadLeft(83) _

& Space(1) _

& FormatCurrency(decExtPriceDate, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtDate, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotDate, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the date totals to the month totals

decExtPriceMonth += decExtPriceDate

decDiscAmtMonth += decDiscAmtDate

decSaleTotMonth += decSaleTotDate

Loop

' end of inner loop 1

' we have our break on month, so generate the month totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = ("TOTALS FOR " & UCase(MonthName(CInt(Strings.Right(strPrevMonth, 2)))) & ", " _

& Strings.Left(strPrevMonth, 4) & ":").PadLeft(83) _

& Space(1) _

& FormatCurrency(decExtPriceMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtMonth, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotMonth, 2).PadLeft(11)

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = " "

' "roll" the month totals to the grand totals

decExtPriceGrand += decExtPriceMonth

decDiscAmtGrand += decDiscAmtMonth

decSaleTotGrand += decSaleTotMonth

Loop

' end of outer processing loop - time to generate the grand totals

mintLineX += 1

ReDim Preserve mastrDetailLines(mintLineX)

mastrDetailLines(mintLineX) = Space(70) _

& "GRAND TOTALS:" _

& Space(1) _

& FormatCurrency(decExtPriceGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decDiscAmtGrand, 2).PadLeft(11) _

& Space(1) _

& FormatCurrency(decSaleTotGrand, 2).PadLeft(11)

' close the input file

FileClose(mintSalesFileNbr)

End Sub

Sample Program 4 builds on Sample Program 3. In addition to the year-month and order date control breaks implemented in Sample Program 3, Sample Program 4 implements an additional control break on order number. The report now includes subtotals by order number in addition to subtotals by year-month and order date. As in Sample Programs 1, 2, and 3, grand totals are printed in bold at the end of the report.

In the code above, the variable strPrevOrderNbr will be needed to compare the current contents of the order number field with its previous contents to detect when a change in that field occurs. Because we will now have an additional set of totals at the order number level for the calculated fields extended price, discount amount, and sale total, we declare the variables decExtPriceOrd, decDiscAmtOrd, and decSaleTotOrd for that purpose.

After the Do Until loop is set up for the date break (Do Until (strPrevDate <> mstrOrderDate) Or (strPrevMonth <> mstrYearMonth) Or (mblnEOF)), we perform the setup steps for the control break on the order number field.

The statement

strPrevOrderNbr = mstrOrderNbr

performs the critical step of saving the value of the current order number field (mstrOrderNbr) to the "comparison" variable strPrevOrderNbr.

The statements

decExtPriceOrd = 0

decDiscAmtOrd = 0

decSaleTotOrd = 0

perform the critical step of clearing the order number subtotals, which must be cleared at the start of every order number group.

The statement

Do Until (strPrevOrderNbr <> mstrOrderNbr) _

Or (strPrevDate <> mstrOrderDate) _

Or (strPrevMonth <> mstrYearMonth) _

Or (mblnEOF)

sets up a third inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevOrderNbr <> mstrOrderNbr)" means that the loop will keep executing until there is a change (or break) in the mstrOrderNbr field – the condition checks the value of strPrevOrderNbr (which we have set in the setup steps above) against the value of mstrOrderNbr, which is refreshed every time an input record is read. Note also that we are checking for a change in the date field (hence the piece Or (strPrevDate <> mstrOrderDate)) and we are checking for a change in the year/month field (hence the piece Or (strPrevMonth <> mstrYearMonth)). As mentioned several times in this article, in a control-break program, a break in a higher level automatically causes a break in all lower levels. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs. Recall that the end-of-file condition is the "mother" of all control breaks – end-of-file automatically causes a break in all control levels.

The statements

decExtPriceOrd += decExtPriceDtl

decDiscAmtOrd += decDiscAmtDtl

decSaleTotOrd += decSaleTotDtl

add the calculated fields at the detail level to their corresponding order number subtotals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals; in Sample Program 2, the calculated fields at the detail level were added to their corresponding year-month totals; in Sample Program 3, the calculated fields at the detail level were added to their corresponding date totals; now in Sample Program 4, there is the additional order number subtotal level, and it is these order number subtotal accumulators to which the detail variables must be added. As mentioned earlier, the rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.

The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the order number is detected (which is basically to print the order number subtotals and roll the order number subtotals to the date subtotals):

Once we move past the

Loop

statement, this means a change has occurred in the value of the order number field. We have printed all the detail records for one order number, and we have the first record for the next order number in memory waiting to be processed. However, before we start processing the new order number, we have some unfinished business to take care of with the previous order number. First, we must print the subtotals for the previous order number. Then, we must add the order number subtotals to their corresponding date subtotals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)

decExtPriceDate += decExtPriceOrd

decDiscAmtDate += decDiscAmtOrd

decSaleTotDate += decSaleTotOrd

From that point on, the code for this program is very similar to its precedessor. (The Loop statement returns the program to the top of the second inner processing loop, which will do the setup for the next order number group, process the detail records for that group, generate the order number group's subtotals, roll those subtotals to the date subtotals, and so on for each order number. When there is a break on date, control will return to the first inner processing loop, which will do the setup for the next date group, process the order number groups for that date group, generate the date group's subtotals, and roll those subtotals to the year/month subtotals, and so on for each date group. When there is a break on year/month, control will return to the main outer processing loop, which will do the setup for the next year/month group, process the date groups for that year/month group, generate the year/month group's subtotals, roll those year/month subtotals to the grand totals, and so on for each year/month group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.

Notes on Printing

The printing routines for all four sample programs are identical, with the exception of the heading line that specifies "WITH CONTROL BREAK ON MONTH", or "WITH CONTROL BREAKS ON MONTH, DATE, AND ORDER NUMBER". etc. In all cases, printing is triggered when the Document property of the PrintPreviewDialog is assigned the PrintDocument object. This causes the PrintPage event to fire, which does the work of populating the document with the contents of the detail line array that has been built previously by the program. Helper routines handle the generation and printing of page heading lines and also handles the case where a line is to be printed in bold font.

A Final Note (Regarding Sorting)

The first statement in this article begins "Control break processing is a technique used to process a sorted sequential file that contains groups of records ...". It must be emphasized that in order to process a file or set of data using the control break techniques discussed in this article, that file or set of data must be sorted on the key field or fields on which you are going to be breaking. For example, the comma-delimited file used in all of the examples in this article was pre-sorted on year-month, then date, then order number.

If you were to adapt the techniques learned in this article to processing a recordset that was the result of a database query, your query would specify the ORDER BY clause on the required fields. If you wanted to process a sequential file like the one used in the examples, and the file was not already sorted in the required sequence, you would have to look into ways of getting sorted prior to running your program. One option, if the file was in comma-delimited format, would be to import it into Excel, use Excel's Data Sort feature, then re-save the sorted results to a new file.

The bottom line is that control break processing requires that the input data is sorted on the control fields.