Control Break Concepts

Control Break Concepts

Control break processing is a technique used to process a sorted sequential file 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 VB Data Report Designer, 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 I/O), 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.) We will also examine good old fashioned report program logic (come on, gang, let's step into that wayback machine!).

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)

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 calcluated 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

This sample program introduces report logic techniques that are used in all of the subsequent examples for this article. The native VB Printer object will be used to generate the report output. Although the VB Printer object can be used to generate "fancy" printouts that include graphics, lines, boxes, a mixture of fonts, etc., we are going to limit the printing to plain-text only, and use only one font in one size, Courier New 10 point. As a "special treat", we will make the total line bold.

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.

Assuming a standard page size of 8 ½" by 11" and a font of Courier New 10 point (which gives you 12 characters per inch), you can plan your report layouts and logic as follows:

Portrait Mode: 80 characters per line, 60 lines per page

Landscape Mode: 118 characters per line, 47 lines per page

The above figures are subjective to a degree, as they take into account room for top, bottom, left and right margins. If you want bigger or smaller margins, or if you want a different font style and/or font size, the number of characters per line and the number of lines per page will vary accordingly. (Keep in mind however, that designing printouts of this nature would be very difficult if not impossible with a proportional font – that is why you should use a monospaced font.)

The reports produced by the sample programs for this article all print in landscape mode, and 118 characters per line, 47 lines per page are the assumed limits.

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.

The sample programs use the following methods and properties of the Printer object:

Method	Description
Print	Generates output as specified by the expression list that follows the keyword Print. As examined in the earlier tutotials on the Print method, the expression list may contain a mix of constants, variables, the Tab() and Spc() functions, and can use the comma ("print zone") and/or semicolon separators.
NewPage	Advances to the next printer page and resets the print position to the upper-left corner of the new page. It is not necessary to use the NewPage method for the first page to be printed (this would cause an extra blank page to be generated).
EndDoc	This is a very important method (without it, you may wonder where your output is). The EndDoc method terminates a print operation sent to the Printer object, releasing the document to the print device or spooler.

Property	Description
Fonts	Property array containing the names of the fonts available on the current default printer. Should be referenced with an index (i.e. Printer.Fonts(X), where X is a number between 0 and Printer.FontCount – 1).
FontCount	The number of fonts available on the current default printer.
FontName	The name of the current font being used on the current default printer.
FontSize	The size of the current font being used on the current default printer.
FontBold	Boolean indicating whether or not the bold style is applied to the current font being used on the current default printer.
Orientation	Indicates whether the current default printer is printing in portrait or landscape mode. A value of 1 (or the built-in constant vbPRORPortrait) indicates portrait mode; a value of 2 (or the built-in constant vbPRORLandscape) indicates landscape mode. One thing to keep in mind is pointed out by the MSDN help: The effect of the properties of the Printer object depends on the driver supplied by the printer manufacturer. Some property settings may have no effect, or several different property settings may all have the same effect. Settings outside the accepted range may or may not produce an error. For more information, see the manufacturer's documentation for the specific driver.

The interface for the program is quite simple: two command buttons named cmdPrint and cmdExit. All of the code for this program resides behind the default form (Form1) with the majority of the code in the Click event of the cmdPrint button.

The code for the General Declarations section, where the form-level variables are declared, is shown below.

The first set of variables (mstrYearMonth through msngDiscountPct) are used to hold the fields of the current input record.

The variable mintLineCtr is used to keep track of how many lines have been printed on the page so that we know when the lines per page limit has been reached. The variable mintPageCtr is used to keep track of the page number so that it can be printed on each page heading.

The variable mintSalesFileNbr is the file number used to refer to the input file.

The variable mblnEOF is used to test whether or not we have reached the end-of-file condition. The reason that this variable is used instead of just using the EOF function will be explained a little further below.

The constant mintLINE_START_POS is used to control the left margin of the page. By setting this to 6, we are saying we want a margin of 5 characters. The Tab functions used in the Print statements use this constant to determine the actual starting position of the item to be printed. For example, the statement Printer.Print Tab(mintLINE_START_POS + 20); "HELLO" would cause "HELLO" to print in position 26 of the current line. However, if later on, I decided I wanted a wider margin, I would only have to change the value of the constant mintLINE_START_POS (to, say a value of 8) rather than change every single Printer.Print statement.

Option Explicit

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

Private mintLineCtr As Integer

Private mintPageCtr As Integer

Private mintSalesFileNbr As Integer

Private mblnEOF As Boolean

Private Const mintLINE_START_POS As Integer = 6

Before we look at the code behind the cmdPrint button, let us examine the two programmer-defined Subs used in the program. The Sub ReadSalesRecord implements a way to delay the testing for the end of file condition until after the last record has been read. As you know, the Input #n statement detects 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-list

Process current record

Loop

However, for many algorithms, it is more convenient have the end of file condition triggered on a separate iteration of the processing loop. 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 ReadSalesRecord Sub is called when we want to read a record. 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.

'-----------------------------------------------------------------------------

Private Sub ReadSalesRecord()

'-----------------------------------------------------------------------------

If EOF(mintSalesFileNbr) Then

mblnEOF = True

Else

Input #mintSalesFileNbr, mstrYearMonth, _

mstrOrderDate, _

mstrOrderNbr, _

mstrCustID, _

mstrProdDesc, _

msngUnitPrice, _

mintQty, _

msngDiscountPct

End If

End Sub

Thus, the ReadSalesRecord Sub can be used as follows:

Call ReadSalesRecord ' priming read

Do Until mblnEOF

' PROCESS THE RECORD

Call ReadSalesRecord ' second and subsequent reads

Loop

Note: Recall that the keyword Call is optional when calling a Sub, so if desired, the keyword Call could be omitted from the above code.

The Sub PrintHeadings is called when a new page must be printed (when printing the first page, or when the line limit of the current page has been exceeded). Note that it is in this Sub that the page counter variable (mintPageCtr) is incremented so that that the page number will appear in the first heading line, and that at the bottom of the Sub, the line counter variable (mintLineCtr) is reset.

'-----------------------------------------------------------------------------

Private Sub PrintHeadings()

'-----------------------------------------------------------------------------

' If we are about to print any page other than the first, invoke the NewPage

' method to perform a page break. The NewPage method advances to the next

' printer page and resets the print position to the upper-left corner of the

' new page.

If mintPageCtr > 0 Then

Printer.NewPage

End If

' increment the page counter

mintPageCtr = mintPageCtr + 1

' Print 4 blank lines, which provides a for top margin. These four lines do NOT

' count toward the limit of 47 lines.

Printer.Print

' Print the main headings

Printer.Print Tab(mintLINE_START_POS); _

"Print Date: "; _

Format$(Date, "mm/dd/yy"); _

Tab(mintLINE_START_POS + 50); _

"THEVBPROGRAMMER.COM"; _

Tab(mintLINE_START_POS + 110); _

"Page:"; _

Format$(mintPageCtr, "@@@")

Printer.Print Tab(mintLINE_START_POS); _

"Print Time: "; _

Format$(Time, "hh:nn:ss"); _

Tab(mintLINE_START_POS + 45); _

"NORTHWIND TRADERS SALES REPORT"

Printer.Print Tab(mintLINE_START_POS + 39); _

"WITH NO CONTROL BREAKS (GRAND TOTALS ONLY)"

Printer.Print

' Print the column headings

Printer.Print Tab(mintLINE_START_POS); _

"YEAR/"; _

Tab(23 + mintLINE_START_POS); _

"ORDER CUST"; _

Tab(72 + mintLINE_START_POS); _

"UNIT"; _

Tab(87 + mintLINE_START_POS); _

"EXTENDED"; _

Tab(99 + mintLINE_START_POS); _

"DISCOUNT"

Printer.Print Tab(mintLINE_START_POS); _

"MONTH"; _

Tab(11 + mintLINE_START_POS); _

"ORDER DATE NUMBER ID"; _

Tab(37 + mintLINE_START_POS); _

"PRODUCT DESCRIPTION"; _

Tab(71 + mintLINE_START_POS); _

"PRICE QTY"; _

Tab(88 + mintLINE_START_POS); _

"PRICE"; _

Tab(100 + mintLINE_START_POS); _

"AMOUNT SALE TOTAL"

Printer.Print Tab(mintLINE_START_POS); _

"-----"; _

Tab(11 + mintLINE_START_POS); _

"---------- ------ --"; _

Tab(37 + mintLINE_START_POS); _

"-------------------"; _

Tab(71 + mintLINE_START_POS); _

"----- ---"; _

Tab(88 + mintLINE_START_POS); _

"-----"; _

Tab(100 + mintLINE_START_POS); _

"------ ----------"

Printer.Print

' reset the line counter to reflect the number of lines that have now

' been printed on the new page.

mintLineCtr = 8

End Sub

The code for the cmdPrint_Click event is shown below. This is where most of the work occurs in the program. The code is shown in its entirety a little further below; but right here we will analyze it piece by piece.

First, the local variables (and constant) are declared as follows:

Dim intX As Integer

Dim strCustFileName As String

Dim strBackSlash As String

Dim dblExtPriceDtl As Double

Dim dblDiscAmtDtl As Double

Dim dblSaleTotDtl As Double

Dim dblExtPriceGrand As Double

Dim dblDiscAmtGrand As Double

Dim dblSaleTotGrand As Double

Const intLINES_PER_PAGE As Integer = 47

The variable intX is an integer that will be used for looping through the Fonts property array. The variables dblExtPriceDtl, dblDiscAmtDtl, and dblSaleTotDtl are the variables used for the calculated fields (extended price, discount amount, and sale total). The variables dblExtPriceGrand, dblDiscAmtGrand, and dblSaleTotGrand are the variables for the corresponding grand totals; as each input record is read and the calculated field variables are computed, the calculated values are added to their corresponding grand total variables. The constant intLINES_PER_PAGE is used to compare against the line counter variable (mintLineCtr, declared in the General Declarations section, updated every time a line is printed) to detect when we've filled up a page.

Following the variables and constant declaration, an error handler is set up (so that if there is a problem with the printer or with the input file, we can handle the error ourselves rather than let VB simply crash):

On Error GoTo cmdPrint_Click_Error

With the following code, we give the user an opportunity to make sure his or her printer is ready (or to back out of printing if so desired):

If MsgBox("Make sure your printer is on-line and " _

& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _

Then

Exit Sub

End If

The following code segment is used to set the printer's font to Courier. It loops through the Printer object's Fonts property array, looking for the first font name that starts with "Courier". Once such a font name is found, that font is assigned to the FontName property, thus making Courier (or "Courier New" or "Courier PS") the current font for the printer, and the loop then exits. If, for whatever reason, no font name starting with "Courier" is found, the FontName property will not be explicitly set, so the Printer object will just use the default font for the printer.

For intX = 0 To Printer.FontCount - 1

If Printer.Fonts(intX) Like "Courier*" Then

Printer.FontName = Printer.Fonts(intX)

Exit For

End If

After the font name is set, the font size is set to 10 and the printer orientation is set to landscape with the following two lines:

Printer.FontSize = 10

Printer.Orientation = vbPRORLandscape

The page counter variable is then initialized to 0 (by default this variable would already be initialized to 0; it is done here explicitly for improved readability of the code) :

mintPageCtr = 0

The file name and file number are then set up, and the Open statement is issued to start the processing (this code should be familiar from the previous articles on sequential file processing) :

' prepare file name & number

strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")

strCustFileName = App.Path & strBackSlash & "NWSALES.txt"

mintSalesFileNbr = FreeFile

' open the input file

Open strCustFileName For Input As #mintSalesFileNbr

A call to the PrintHeadings Sub is made to print the headings for the first page of the report:

Call PrintHeadings

The grand total variables are then initialized to 0 (by default these variables would already be initialized to 0; it is done explicitly here for improved readability of the code – showing that these variables are cleared at the beginning of the processing and will be accumulated into as each record is processed) :

dblExtPriceGrand = 0

dblDiscAmtGrand = 0

dblSaleTotGrand = 0

The priming read to get the first input record is then made:

Call ReadSalesRecord

The main processing loop then begins:

Do Until mblnEOF

A check is made to see if the number of lines printed so far exceeds the maximum number of lines allowed on a page, and if so, the PrintHeadings Sub is called to start a new page:

If mintLineCtr > intLINES_PER_PAGE Then

Call PrintHeadings

End If

Computations for the calculated fields (extended price, discount amount, and sale total) are then performed:

dblExtPriceDtl = msngUnitPrice * mintQty

dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct

dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl

A detail line, consisting of fields from the input record, along with the calculated fields, is then printed. The use of the Tab function and the various Format$ options has been examined in previous articles. Note how nested Format$ functions are used to right-justify the numeric values.

Printer.Print Tab(mintLINE_START_POS); _

mstrYearMonth; _

Tab(11 + mintLINE_START_POS); _

mstrOrderDate; _

Tab(23 + mintLINE_START_POS); _

mstrOrderNbr; _

Tab(30 + mintLINE_START_POS); _

mstrCustID; _

Tab(37 + mintLINE_START_POS); _

mstrProdDesc; _

Tab(70 + mintLINE_START_POS); _

Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _

Tab(79 + mintLINE_START_POS); _

Format$(mintQty, "@@@"); _

Tab(84 + mintLINE_START_POS); _

Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _

Tab(96 + mintLINE_START_POS); _

Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _

Tab(108 + mintLINE_START_POS); _

Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))

The line counter variable is then incremented to account for the detail line we just printed:

mintLineCtr = mintLineCtr + 1

The calculated field variables are then added to their corresponding grand total variables. This is sometimes called "rolling" the values to the totals.

dblExtPriceGrand = dblExtPriceGrand + dblExtPriceDtl

dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtDtl

dblSaleTotGrand = dblSaleTotGrand + dblSaleTotDtl

Having completed the work required for the current input record, the next record is then read via a call to the ReadSalesRecord Sub at the bottom of the loop, and the Loop statement then returns us to the top of the Do loop for another processing iteration:

Call ReadSalesRecord

Loop

Once we have processed all of the input records and the end of file condition has been met, we continue with the task of printing the grand totals. Before we do so, we check first to see if there is enough room on the current page to print them. A blank line will precede the total line, so we check to make sure there is enough room to print two lines as follows:

If mintLineCtr > (intLINES_PER_PAGE - 2) Then

Call PrintHeadings

End If

We then print the grand totals. A blank line is printed first (with the Printer.Print statement). We then turn bold styling on by setting the FontBold property to True, print the actual total line, and then turn bold styling off by setting the FontBold property to False:

Printer.Print

Printer.FontBold = True

Printer.Print Tab(70 + mintLINE_START_POS); _

"GRAND TOTALS:"; _

Tab(84 + mintLINE_START_POS); _

Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _

Tab(96 + mintLINE_START_POS); _

Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _

Tab(108 + mintLINE_START_POS); _

Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))

Printer.FontBold = False

We then wrap things up by closing the input file, executing the EndDoc method on the Printer object (remember, this is very important, as the EndDoc method actually releases the output to the printer), setting focus to the Exit button as a visual indication that the print processing is complete, and finally exiting the sub.

Close #mintSalesFileNbr

Printer.EndDoc

cmdExit.SetFocus

Exit Sub

If an error occurs in the processing, the error handling code below will be executed. It shows the user what the error is and asks whether or not they want to continue. If yes, the program will try to continue with the statement past the one that caused the error; if no, the program will end.

cmdPrint_Click_Error:

If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _

"Do you want to continue?", _

vbYesNo + vbQuestion, _

"Error") = vbYes Then

Resume Next

Else

End

End If

Here is the code for the entire cmdPrint_Click event:

'-----------------------------------------------------------------------------

Private Sub cmdPrint_Click()

'-----------------------------------------------------------------------------

Dim intX As Integer

Dim strCustFileName As String

Dim strBackSlash As String

Dim dblExtPriceDtl As Double

Dim dblDiscAmtDtl As Double

Dim dblSaleTotDtl As Double

Dim dblExtPriceGrand As Double

Dim dblDiscAmtGrand As Double

Dim dblSaleTotGrand As Double

Const intLINES_PER_PAGE As Integer = 47

On Error GoTo cmdPrint_Click_Error

' Have the user make sure his/her printer is ready ...

If MsgBox("Make sure your printer is on-line and " _