| **Navigation:**  [[welcome to my product .htm|User's Guide and Tutorials]] > Guided Tour of Working in Clarion >====== 12 - Creating Reports ====== | [[lc advanced topics.htm|{{btn_prev_n.gif|Previous page}}]][[welcome to my product .htm|{{btn_home_n.gif|Return to chapter overview}}]][[lc clarion language tutorial.htm|{{btn_next_n.gif|Next page}}]] | | || **Overview** The last item to cover is adding reports. First, we'll create a simple customer list to introduce you to the Report Designer. Then we'll create an Invoice Report to demonstrate how you can easily create Relational reports with multi-level group breaks, group totals, and page formatting. Then we'll copy the Invoice Report and limit the copy to print invoice for only one customer at a time. **Starting Point:** **LCLESSON.APP should be open, and the Application Tree displayed.** **Updating the Main Menu** First, we need to add menu selections so the user can call the reports, and so the Application Generator will call the appropriate "ToDo" procedures. __**Add a menu item**__ 1.RIGHT-CLICK on the //Main// procedure in the Application Tree dialog and choose **Window** from the popup menu. 2.RIGHT-CLICK on the Menubar on the window and select **Edit Menu**. The Menu Editor is now open. 3.Highlight the //P&rint Setup// item in the //Menu Editor// list. 4.Press the **Add** **New Item (Insert)** {{newmenuitem.jpg|NewMenuItem.jpg}} button. 5.Type //Print &Customer List// in the **Text** property entry, then press the TAB key. {{l12addmenuitem.jpg|L12AddMenuItem.jpg}} __**Specify the new item's action**__ 1.RIGHT-CLICK on the //Print &Customer List// item in the //Menu Editor// list, and select the **Actions** item from the popup menu. 2.Choose //Call a Procedure// from the **When Pressed** drop list. 3.Type //CustReport// in the **Procedure Name** entry. 4.Check the **Initiate Thread** box. 5.Press **OK** to close the Actions (//?ITEM1 Prompts//) dialog. __**Add a second menu item**__ 1.Highlight the //Print &Customer List// item in the //Menu Editor// list, and then press the **Add** **New Item (Insert)** {{newmenuitem.jpg|NewMenuItem.jpg}} button. 2.Type //Print &All Invoices// in the **Text** property entry, then press the TAB key. 3.RIGHT-CLICK on the //Print &All Invoices// item in the //Menu Editor// list, and select the **Actions** item from the popup menu. 4.Choose //Call a Procedure// from the **When Pressed** drop down list. 5.Type //InvoiceReport// in the **Procedure Name** entry. 6.Check the **Initiate Thread** box. __**Add a third menu item**__ 1.Highlight the //Print &All Invoices// item in the //Menu Editor// list, and then press the **Add** **New Item (Insert)** {{newmenuitem.jpg|NewMenuItem.jpg}} button. 2.Type //Print &One Customer's Invoices// in the **Text** property entry, then press the TAB key. 3.RIGHT-CLICK on the //Print &One Customer's Invoices// item in the //Menu Editor// list, and select the **Actions** item from the popup menu. 4.Choose //Call a Procedure// from the **When Pressed** drop down list. 5.Type //CustInvoiceReport// in the **Procedure Name** entry. 6.Check the **Initiate Thread** box. 7.Press the **Save and** **Close** button to close the Menu Editor. 8.Press the **Save and** **Close** button to close the Window Designer. 9.Press the **Save and** **Close** button in the Window Designer Editor to return to the Application Tree. 10.Save your work by pressing the **Save** button {{savebutton.jpg|savebutton.jpg}} on the IDE toolbar. {{blk2blue.jpg|blk2blue.jpg}} **Creating the Report** Now you can create the first report, using the Report Designer. 1.Highlight the //CustReport// procedure in the Application Tree. 2.Press the **Properties** {{propbut.jpg|propbut.jpg}} button. 3.Choose the **Defaults** tab and then highlight //Report (Paper size Letter ' Portrait)// in the //Select Procedure Type// dialog. Press the **Select** button. 4.Press the **Report** button in the //Procedure Properties// dialog. 5.Press the **Designer** button to open the Report Designer. The Report Designer appears. Here you can visually edit the report and its controls. The Report Designer represents the four basic parts of the REPORT data structure by showing the Page Header, Detail, Page Footer, and Form as four "bands." Each band is a single entity that prints all together. See the User's Guide chapter on Using the Report Designer for more information on the parts of the report and how the print engine generates them. For this report, you'll place page number controls in the header, then place the columns from the Customer table in the Detail band. __**Place a string constant**__ 1.With the Report Designer active, choose **View** {{blttria.jpg|BLTTRIA.jpg}} **Toolbox** (or press CTRL + ALT + X). 2.Select the STRING control in the Toolbox, and DRAG the control to the top left of the **Page Header** band, and DROP. {{l12populatestring.jpg|L12PopulateString.jpg}} 3.RIGHT-CLICK on the control, then choose **Properties** from the popup menu. The //String Properties// dialog appears. 4.In the Properties Pad, type //Page Number:// in the **Text** property. __**Place a control template to print the Page Number**__ 1.Open the** Control Templates Pad**, if not already opened. 2.Highlight //ReportPageNumber// then DRAG to the right of the previously placed string. **Populating the Detail** The Detail band prints once for each row in the report. For this procedure, you'll place the columns in a block arrangement, which creates a label report at print time. 1.Select the **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Populate** {{blttria.jpg|BLTTRIA.jpg}} **Multiple Columns **from the IDE toolbar. {{selectcolumn2.jpg|SelectColumn2.jpg}} 2.In the //Select Column// dialog, highlight the //<;ToDo>// folder, then press the **Add** button. 3.Select the //Customer// table from the //Select a Table// dialog, then press the **Select** button. 4.Highlight the //Customer// table, and press the **Change** button. 5.Highlight //KeyCustNumber// in the //Select Key from CUSTOMER// dialog, then press the **Select** button. 6.Highlight //Company// in the //Select// //Columns// list in the right pane,and press the **Select** button. 7.CLICK inside the **Detail** band, near its top left corner. 8.Highlight //FirstName// in the //Select// //Columns// list and press the **Select** button. 9.CLICK inside the **Detail** band, just below the first control. 10.Highlight //LastName// in the //Select// //Columns// list and press the **Select** button. 11.CLICK inside the **Detail** band, to the right of the control you just placed. 12.Highlight //CUS:Address// in the //Select// //Columns// list and press the **Select** button. 13.CLICK inside the **Detail** band, below the second control you placed. __**Resize the Detail band**__ At this point, you probably have very little room left in the Detail band, and need to make it longer. 1.Press the **Cancel** button to exit multi-populate mode. 2.CLICK inside the **Detail** band, but not on one of the string controls. The Detail area's handles appear. 3.Resize the **Detail** band by dragging the middle handle on the bottom down'allow for enough room for about two more lines. {{l12resizedetail.jpg|L12ResizeDetail.jpg}} __**Place the rest of the columns**__ 1.Choose **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Populate** {{blttria.jpg|BLTTRIA.jpg}} **Multiple Fields** from the IDE Menu. 2.Highlight //City// in the //Columns// list, then press the **Select** button. 3.CLICK inside the **Detail** band, below the last control you placed. 4.Highlight //State// in the //Columns// list, then press the **Select** button. 5.CLICK inside the **Detail** band, to the right of the previously placed control. 6.Highlight //ZipCode// in the //Columns// list, then press the **Select** button. 7.CLICK inside the **Detail** band, to the right of the previously placed control. 8.Press the **Cancel** button to exit multi-populate mode. Notice that you have the same set of alignment tools in the Report Designer that you have already used in the Window Designer. Feel free to align and adjust the position of your controls at this time. __**Select a base font for the Report**__ 1.Open the **Properties Pad **(F4 Key), and select the **Report** control from the drop list as shown: {{l12selreport.jpg|L12SelReport.jpg}} 2.In the //Property Pad//, locate the **TextFont** property, and press the ellipsis button to the right. 3.Select a font, style, and size to use as the base font for the report. If you don't select a font, it uses the printer's default font. 4.Press the **OK** button to close the //Select Font// dialog. __**Preview the Report**__ 1.In the Report Designer toolbar, press the **Print** **Preview** button {{l12printpreview.jpg|L12PrintPreview.jpg}} to "visualize" how the printed page will appear. {{l12printpreviewdetail.jpg|L12PrintPreviewDetail.jpg}} 2.Highlight //Detail// in the //Details// list then press the **Add** button several times. This populates the preview with some print bands to view. Because you can have many bands of various types within a single report, you have to select which to see before going to print preview. This way, the Report Designer knows what to compose on the screen. 3.Press the **OK** button. 4.When done "previewing," press the **Close **button. 5.Press the **Save and Close **button** **to return to the Procedure Properties dialog. 6.Press the **Save and Close **button to close the Report Designer Editor dialog. 7.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the Save button {{savebutton.jpg|savebutton.jpg}} on the tool bar to save your work. {{blk2blue.jpg|blk2blue.jpg}} **An Invoice Report** Next, we will create one of the most common types of reports. An invoice will make use of most of the tables in the data dictionary, demonstrating how to create group breaks and totals. It will also show you how to control pagination based on group breaks. **Creating the Report** 1.Highlight the //InvoiceReport// procedure. 2.Press the **Properties** {{propbut.jpg|propbut.jpg}} button. 3.Select the **Defaults** tab, then highlight //Report (Paper size Letter ' Portrait)// in the //Select Procedure Type// dialog, then press the **Select** button. The //Procedure Properties //dialog appears. __**Specify the tables for the Report**__ 1.If not already opened, open and select the **Data / Tables Pad**. 2.Highlight the //<;ToDo>// folder, then press the **Add** button on the Pad toolbar. 3.Select the //Customer// table from the //Select// dialog, then press the **Select** button. 4.Highlight the the //Customer// table, and press the **Change** {{fspedit.jpg|FSPEdit.jpg}} button. 5.Highlight //KeyCustNumber// in the //Select Key from CUSTOMER// dialog, then press the **Select** button. The report will process all the Customer table rows in CustNumber order. 6.Highlight the //Customer// table, then press the **Add** button. 7.Select the //Orders// table from the **Related Tables** tab, then press the **Select** button. It will process all the Orders for each Customer. 8.Highlight the //Orders// table, then press the **Add** button. 9.Select the //Detail// table from the **Related Tables** tab, then press the **Select** button. Each Order will print all the related Detail rows. 10.Highlight the //Detail// table, then press the **Add** button. 11.Select the //Products// table from the **Related Tables** tab, then press the **Select** button. Each Detail row will lookup the related Products table row. **Populating the Report Form Band** The Report Form band prints once for each page in the report. Its content is only composed once, when the report is opened. This makes it useful for constant information that will always be on every page of the report. __**Place a string constant**__ 1.From the //Procedure Properties// window, press the **Report** tab, and then press the **Designer** button to reenter the Report Designer. 2.With the Report Designer active, choose **View** {{blttria.jpg|BLTTRIA.jpg}} **Toolbox** (or press CTRL + ALT + X). 3.Select the STRING control in the Toolbox, and DRAG the control to the top middle of the Report Form band. 4.RIGHT-CLICK on the control just populated, and select **Properties'** from the popup menu. The //Property Pad// now has focus. 5.Type //Invoice// in the **Text** property entry. 6.Locate the **TextFont** property, and press the ellipsis button to the right. 7.Select a font, style, and size to use for the text (something large and bold would be appropriate for this). 8.Press the **OK** button to close the //Select Font// dialog. 9.If needed, resize the control so that it's large enough to hold the text, by dragging its handles. __**Place the next string constant**__ 1.Select the STRING control in the Toolbox, and DRAG the control just below the last string you placed. 2.RIGHT-CLICK on the control, then choose **Properties** from the popup menu. 3.Type the name of your company in the **Text** entry. 4.Press the **Font** button and select a font, style, and size to use for the text (something just a little smaller than the previous column would be appropriate for this). 5.Press the **OK** button to close the //Select Font// dialog. 6.If needed, resize the control so that it's large enough to hold the text, by dragging its handles. {{l12reportform.jpg|L12ReportForm.jpg}} **Populating the Detail Band** The Detail band will print every time new information is read from the lowest level "Child" table in the Table Schematic. For this Invoice report, the lowest level "Child" table is the //Detail// table (remember that //Products// is a Many to One "lookup" table from the //Detail// table). In the last report, we used the **Populate > Multiple Fields** option. In this section, we will use the **Data / Tables Pad** as an alternate design trechnique. 1.If not already opened, open and select the //Data / Tables Pad//. 2.Highlight //Detail// in the //Tables// list then select //Quantity// in the //Columns// list and DRAG the column inside the Detail band, and DROP near its top left corner. {{fsp2.jpg|FSP2.jpg}} 3.Back in the //Data / Tables Pad//, highlight //ProdNumber// in the //Columns// list and DRAG the column and DROP directly to the right of the first control. 4.Highlight //Products// in the //Tables// list then DRAG //ProdDesc// in the //Columns// list and DROP it to the right of the control just placed. 5.Highlight //Detail// in the //Tables// list then DRAG //ProdAmount// in the //Columns// list and DROP it to the right of the control just placed. 6.Highlight //LOCAL DATA InvoiceReport// in the //Tables// list, then press the **Add** button. This local variable will be used to display the total price for each line item. 7.Type //LOC:ItemTotal// in the **Column Name** entry. 8.Select //DECIMAL// from the **Data Type** drop list. 9.Type //7// in the **Characters** entry. 10.Type //2// in the **Places** entry then press the **OK** button. 11.Highlight //LOCAL DATA InvoiceReport, //and DRAG //LOC:ItemTotal// and DROP it to the right of the last control placed. 12.Move all the controls to the top of the Detail band, aligned horizontally, then resize the band so it is just a little taller than the controls. Also, modify the properties of the //DTL:ProdAmount// and //LOC:ItemTotal// to change the **Justification** to //Right Justified// and the Offset to **2**. **Adding Group Breaks** We need to print different information on the page for each Invoice. Therefore, we need to create BREAK structures to provide the opportunity to print something every time the //Orders// table information changes and every time the //Customer// table information changes. 1.CLICK on the //Detail// band, and then choose **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Bands** {{blttria.jpg|BLTTRIA.jpg}} **Surrounding Break**. {{l12surrbreak.jpg|L12SurrBreak.jpg}} The //Format Break Properties// dialog appears. {{l12formatbreak.jpg|L12FormatBreak.jpg}} 2.In the **Variable** entry, type //CUS:CustNumber//. 3.Type //CustNumberBreak// in the **Label** entry then press the **OK** button. A Break (CUS:CustNumber) band appears above the Detail band, which appears indented, meaning it is within the Break structure. 4.CLICK on the Detail band, and then select **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Bands** {{blttria.jpg|BLTTRIA.jpg}} **Surrounding Break**. The //Format Break Properties// dialog appears. 5.In the **Variable** entry, type //ORD:OrderNumber//. 6.Type //OrderNumberBreak// in the **Label** entry then press the **OK** button. __**Create the group Headers and Footers**__ 1.CLICK on the //Break (ORD:OrderNumber)// band, and then select **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Bands** {{blttria.jpg|BLTTRIA.jpg}} **Group** **Header**. The Group Header (ORD:OrderNumber) band appears above the Detail band. This band will print every time the value in the ORD:OrderNumber column changes, at the beginning of each new group of rows. We will use this to print the company name, address, along with the invoice number and date. 2.CLICK on the Break (//ORD:OrderNumber//) band, and then select **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Bands** {{blttria.jpg|BLTTRIA.jpg}} **Group Footer**. The //Group Footer (ORD:OrderNumber)// band appears below the Detail band. This band will print every time the value in the ORD:OrderNumber column changes, at the end of each group of rows. We will use this to print the invoice total. 3.RIGHT-CLICK on the //Group Footer// (//ORD:OrderNumber//) band then choose **Properties** from the popup menu. The //Properties Pad Group Footer// band now has focus. 4.Enter //1// in the **PageAfter** property. This causes the print engine to print this band, then initiate Page Overflow. This will compose the Page Footer band, issue a form feed to the printer, then compose the Page Header band for the next page. 5.CLICK on the Break (//CUS:CustNumber//) band, and select **Report Designer** {{blttria.jpg|BLTTRIA.jpg}} **Bands** {{blttria.jpg|BLTTRIA.jpg}} **Group Footer**. The //Group Footer (CUS:CustNumber)// band appears below the //Group Footer (ORD:OrderNumber)// band. This band will print every time the value in the CUS:CustNumber column changes, at the end of each group of rows. We will use this to print invoice summary information for each company. 6.RIGHT-CLICK on the //Group Footer// (//CUS:CustNumber//) band then choose **Properties** from the popup menu. 7.Enter //1// in the **PageAfter** property. **Populating the Group Header Band** __**Place the Customer table columns**__ 1.Open the //Data / Tables Pad// (if not already opened). 2.Highlight //Customer// in the //Tables// list then DRAG //Company// in the //Columns// list and DROP inside the //Group Header// (//ORD:OrderNumber//) band, near its top left corner. 3.Back in the Data / Tables Pad, DRAG //FirstName// in the //Columns// list and DROP it inside the Group Header (//ORD:OrderNumber//) band, just below the first control. 4.DRAG //LastName// in the //Columns// list and DROP inside the Group Header (//ORD:OrderNumber//) band, to the right of the control you just placed. 5.DRAG //Address// in the //Columns// list and DROP inside the Group Header (//ORD:OrderNumber//) band, below the second control you placed. 6.DRAG CUS:City in the //Columns// list and DROP inside the Group Header (//ORD:OrderNumber//) band, below the last control you placed. 7.DRAG //State// in the //Columns// list, and DROP inside the Group Header (//ORD:OrderNumber//) band, to the right of the previously placed control. 8.DRAG //ZipCode// in the //Columns// list, and DROP inside the Group Header (//ORD:OrderNumber//) band, to the right of the previously placed control. {{l12groupheader.jpg|L12GroupHeader.jpg}} __**Place the Orders table columns**__ 1.Highlight //Orders// in the //Tables// list then DRAG //OrderNumber// in the //Columns// list and DROP inside the Group Header (//ORD:OrderNumber//) band, near its top right corner. 2.DRAG //OrderDate// in the Columns list, then inside the Group Header (//ORD:OrderNumber//) band, below the last control you placed. __**Place the constant text and column headings**__ 1.Open the Control ToolBox if not opened already. DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, left of the //ORD:OrderNumber// control you placed. 2.As the control is populated, select the **Edit Value** "smart link" and enter //Order Number:// in the //Edit Text Value// dialog, and press the **Accept** button.. {{l12editvalue.jpg|L12EditValue.jpg}} 3.Again, from the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, left of the //ORD:OrderDate// control you placed. 4.As the control is populated, select the **Edit Value** "smart link" and enter //Order Date:// in the //Edit Text Value// dialog, and press the **Accept** button. 5.Again, from the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, at the left end below the Customer table controls you placed. 6.As the control is populated, select the **Edit Value** "smart link" and enter //Quantity:// in the //Edit Text Value// dialog, and press the **Accept** button. 7.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, inside the Group Header (//ORD:OrderNumber//) band, to the right of the last string you placed. 8.As the control is populated, select the **Edit Value** "smart link" and enter //Product:// in the //Edit Text Value// dialog, and press the **Accept** button. 9.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, inside the Group Header (//ORD:OrderNumber//) band, to the right of the last string you placed, directly above the //DTL:ProdAmount// control in the Detail band. 10.As the control is populated, select the **Edit Value** "smart link" and enter //At:// in the //Edit Text Value// dialog, and press the **Accept** button. 11.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, inside the Group Header (//ORD:OrderNumber//) band, to the right of the last string you placed, directly above the //LOC:ItemTotal// control in the Detail band. 12.As the control is populated, select the **Edit Value** "smart link" and enter //Item Total// in the //Edit Text Value// dialog, and press the **Accept** button. {{l12groupheader2.jpg|L12GroupHeader2.jpg}} __**Place a thick line under the column headings**__ 1.From the Toolbox Control Pad, DRAG the **LINE** from the //Controls// toolbox and DROP inside the Group Header (//ORD:OrderNumber//) band, inside the Group Header (//ORD:OrderNumber//) band, under the //Quantity// string you placed. 2.Resize the line by dragging its handles until it appears to be a line all across the report under the column headers. 3.In the //Properties Pad//, select the line control from the drop list as shown: {{lineproperties.jpg|LineProperties.jpg}} 4.Type //5// in the **LineWidth** property. This makes the line much thicker. **Populating the Invoice Group Footer Band** __**Place the constant text and total column**__ 1.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP in the middle of the Group Footer (//ORD:OrderNumber//) band. 2.As the control is populated, select the **Edit Value** "smart link" and enter //Order Total:// in the //Edit Text Value// dialog, and press the **Accept** button. 3.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Footer (//ORD:OrderNumber//) band, to the right of the string you just placed. 4.RIGHT-CLICK and choose **Properties** from the popup menu. 5.In the //Properties Pad//, set the **VariableString** property to //TRUE//. 6.Enter //LOC:ItemTotal// in the **Use** property (or press the ellipsis to select the column). 7.Type //@N9.2// in the **Text** property. 8.Select //Sum// in the **TotalType** property drop list. 9.Select //OrderNumberBreak// from the **Reset **property** **drop list. This will add up all the //LOC:ItemTotal// contents for the Invoice and will reset to zero when the value in the //ORD:OrderNumber// column changes. __**Place a line above the total**__ 1.From the Toolbox Control Pad, DRAG the **LINE** from the //Controls// toolbox and DROP inside the Group Footer (//ORD:OrderNumber//) band, above the controls you just placed. 2.Resize the line by dragging its handles until it appears to be above both the controls you just placed. 3.In the //Properties Pad//, type //5// in the **LineWidth** entry. This makes the line just a little bit thicker. **Populating the Customer Group Footer Band** __**Place the constant text**__ 1.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP in the middle of the Group Footer (//CUS:CustNumber//) band. 2.As the control is populated, select the **Edit Value** "smart link" and enter //Invoice Summary for:// in the //Edit Text Value// dialog, and press the **Accept** button. 3.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Footer (//CUS:CustNumber//) band, below the string you just placed. 4.As the control is populated, select the **Edit Value** "smart link" and enter //Total Orders:// in the //Edit Text Value// dialog, and press the **Accept** button. __**Enter additional local data**__ 1.Open and select the **Data / Tables Pad**. 2.Highlight //LOCAL DATA InvoiceReport// in the //Tables// list, then press the **Add** button. 3.Type //LOC:InvoiceCount// in the **Column Name** entry. This is a column that will print the number of invoices printed for an individual company. 4.Select //LONG// from the Data Type drop list. 5.Type //@N3// in the **Screen Picture** entry, then press the **OK** button. __**Place the total columns**__ 1.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Footer (//CUS:CustNumber//) band, just right of the string //Total Orders://. 2.RIGHT-CLICK and choose **Properties** from the popup menu. 3.Set the **VariableString** property to TRUE. {{l12smarttotals.jpg|L12SmartTotals.jpg}} 4.Type //LOC:InvoiceCount// in the **Use** property entry. 5.Type //@N3// in the **Text** property entry. 6.Select //Count// from the **TotalType** property drop list. 7.Select //CustNumberBreak// from the **Reset** property drop list. This is the same type of total column that we placed in the //ORD:OrderNumber// group footer, but it will only reset when //CUS:CustNumber// changes. 8.Select //OrderNumberBreak// in the **Tally** property list, and **press the Enter key to select it**. This total column will count the number of invoices that print for each customer. The **Tally** list allows you to select the point(s) at which the total increments. By selecting //OrderNumberBreak// from the list, the count will only increment when a new invoice begins. 9.From the Toolbox Control Pad, DRAG the **STRING** from the //Controls// toolbox and DROP inside the Group Footer (//CUS:CustNumber//) band, to the right of the string you just placed. 10.RIGHT-CLICK and choose **Properties** from the popup menu to bring focus to the //Properties Pad//. 11.Set the **VariableString** property to TRUE. 12.Type //LOC:ItemTotal// in the **Use** property entry. 13.Select //Sum// from the **TotalType** drop property list. 14.Select //CustNumberBreak// from the **Reset** property drop list. This is the same type of total column that we placed in the //ORD:OrderNumber// group footer, but it will only reset when //CUS:CustNumber// changes. __**Place the display column then exit**__ 1.Open and select the **Data / Tables Pad**. 2.Highlight //Customer// in the //Tables// list then DRAG //Company// in the //Columns// list and DROP inside the Group Footer (//CUS:CustNumber//) band, just right of the //Invoice Summary for:// string you placed. **Your report design is now complete! Resize and tighten up the bands as needed.** **Here is your report so far:** {{l12invoicereport.jpg|L12InvoiceReport.jpg}} 3.Press the **Save and Close** button to return to the //Procedure Properties// dialog. **Adding a Formula** To make the //ItemTotal// column contain the correct amount for each Detail row in the invoice, you need to add a Formula to the procedure. 1.Press the **Formulas** tab in the //Procedure Properties// dialog. 2.Press the **Insert** button in the //Formula Editor// dialog. The //Formula Editor// design dialog appears. 3.Type //Item Total Formula// in the **Name** entry. 4.In the **Class** entry drop list, select //Before Print Detail// in the //Template Classes// list. The //Before Print Detail// class tells the Report template to perform the calculation each time it gets ready to print a Detail. 5.Press the ellipsis ( ... ) button for the **Result** entry. 6.Highlight //LOCAL DATA InvoiceReport// in the //Tables// list, select //LOC:ItemTotal// from the //Columns// list, then press the **Select** button. 7.Press the **Data** button in the **Operands** group. 8.Highlight the //Detail// table in the //Tables// list, select //DTL:Quantity// from the //Columns// list, then press the **Select** button. This places the DTL:Quantity column in the **Statement** entry for you. The Statement entry contains the expression being built, and you can also type directly into it to build the expression, if you wish. 9.Press the ***** button in the **Operators** group. 10.Press the **Data** button in the **Operands** group. 11.Highlight the //Detail //table in the //Tables// list, select //DTL:ProdAmount// from the //Columns// list, then press the **Select** button. 12.Press the **Check** button to check the expression's syntax. 13.Press the **Save and Close** button to close the //Formula Editor// design window. __**Row Filters vs Inner Joins**__ We want to make sure the report only prints invoices for the companies that have orders. You could add a row filter that would work by following these steps (but don't actually do them): 1.Press the **Properties** tab in the //Procedure Properties// dialog. 2.Press the **Actions** button. 3.Press the **Report Properties** button. The //Report Properties// dialog appears. 4.Type //ORD:OrderNumber <;> 0// in the **Record Filter** entry. 5.Press the **OK** button to close the //Report Properties// dialog, then press **OK** again to return to the //Procedure Properties// dialog. This will eliminate all the customers who have not ordered anything. Internally, the Report Template generates a [[view declare a virtual file .htm|VIEW]] structure for you. This VIEW structure, by default, performs an "outer join" on the tables you placed in the Table Schematic. "Outer join" is a standard term in Relational Database theory'it just means that the VIEW will retrieve all Parent table rows, whether there are any related Child table rows or not. If it retrieves a Parent row without a Child, the columns in the Child table are all blank or zero, while the Parent table's columns contain valid data. Therefore, this is the condition for which we test. //ORD:OrderNumber <;> 0// checks to see if the ORD:OrderNumber column has any value in it other than zero. Since ORD:OrderNumber is the key column in the Orders table that creates the link to the related Customers table row, it must contain a value if there are existing Orders table rows for the current Customer. If ORD:OrderNumber does not contain a value other than zero, the current Customers table row is skipped ("filtered out"). This eliminates printing Parent rows without related Children (in this case, any Customers without Orders). This means that a filter would work. However, since the VIEW structure can do the filtering required, there is a better way. This is the "inner join". What this means is that there is a Customer row only if there is a related Order row. This makes the VIEW smaller, and thus more effecient than a filter. To use this feature, follow these steps: **1.**Open the **Data / Tables Pad** **2.**Select the //Orders// table and press the **Change** button. **3.**Check the **Inner** box, then press the **OK** button. **4.**Press the **OK** button to close this dialog. {{l12innerjoin.jpg|L12InnerJoin.jpg}} __**Change the Progress Window**__ 1.Press the **Window** tab in the //Procedure Properties// dialog, and the **Designer** button to enter the Window Designer. 2.Click on the title bar of the window, and press the **F4 key** to bring focus to the //Properties Pad//. 3.Type //Invoice Progress// in the **Title** property. 4.Press **Save and Close** to return to the //Procedure Properties// dialog. __**Exit and Save**__ 1.Press the **Save and Close** button in the //Procedure Properties// dialog to close it. 2.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the Save button {{savebutton.jpg|savebutton.jpg}} on the toolbar. {{blk2blue.jpg|blk2blue.jpg}} **A Range Limited Report** Next, we will limit the range of rows that will print. **Creating the Report** 1.From the Application Tree, highlight the //InvoiceReport// procedure. 2.Choose **Application** {{blttria.jpg|BLTTRIA.jpg}} **Copy Procedure**. 3.The //New Procedure// dialog appears. 4.Type //CustInvoiceReport// in the entry box, then press the **OK** button. The copied procedure appears in the application tree, replacing its "ToDo". **Modify the new report** 1.Highlight the //CustInvoiceReport// procedure. 2.RIGHT-CLICK and choose **Embeds** from the popup menu. 3.Press the **Contract All** button. This will make it easier to locate the specific embed point you need. 4.Locate the //Local Objects// folder, then CLICK on its **+ **sign to expand its contents. Do the same for the //ABC Objects// folder that appears under it. The ABC templates generate object-oriented code. Each procedure instantiates a set of objects which are derived from the ABC Library. The //Local Objects// folder shows you all the object and methods that you can override for the procedure by simply embedding your own code to enhance the ABC functionality. See the Easing Into OOP and Object Oriented Programming articles in the //Clarion Language Programming// PDF for more on this powerful technique. 5.Locate the //WindowManager// folder, then CLICK on it to expand it. 6.Locate the //Init PROCEDURE(),BYTE,VIRTUAL// folder, then CLICK on it to expand it. 7.Locate the //CODE// folder, then CLICK on it to expand it. 8.Highlight //Open Files// then press the **Insert** button. {{l12embeds.jpg|L12Embeds.jpg}} This embed point is at the beginning of the procedure, before the report has begun to process. It's important that the tables for the report already be open because we will call another procedure for the user to select a Customer row. If the tables for the report weren't already open, the procedure we call would open the Customer table for itself then close it again and we would lose the data that we want to have for the report. This has to do with multithreading and the Multiple Document Interface (MDI)'see THREAD in the Language Reference for more on this. 9.Highlight //Source// then press the **Select** button to call the Text Editor. 10.Type in the following code: **GlobalRequest = SelectRecord** This code sets up a Browse procedure to select a row (it enables the Browse procedure's Select button). 11.Press the **Save and Close** button {{saveclose.jpg|SaveClose.jpg}} to return to the //Embedded Source// dialog. 12.Highlight the //SOURCE// you just added then press the **Insert** button. 13.Highlight //Call a procedure// then press the **Select** button. 14.Select //BrowseCustomers// in the list, then press the **OK** button. This will generate a procedure call to the //BrowseCustomers// Browse procedure to allow the user to select which Customer's Invoices to print. Notice that there are now two entries displayed under the embed point. At each embed point you can place as many items as you want, mixing Code Templates with your own SOURCE or PROCEDURE Calls. You can also move the separate items around within the embed point using the arrow buttons, changing their logical execution order (the first displayed is the first to execute). Note well that moving them will change the assigned **Priority** option setting for the moved item if you attempt to move a higher priority item in front of another with a lower priority setting. 15.Press the **Save and Close** button to return to the //Procedure Properties// dialog. __**Set the Range Limit**__ 1.Press the **Properties** button, and then press the **Actions** button. 2.In the Properties dialog, press the **Report Properties** button. The //Report Properties// dialog appears. This dialog allows you to set either Row Filters or Range Limits (along with Hot Columns and Detail Filters). Row Filters and Range limits are very similar. A Row Filter is a conditional expression to filter out unwanted rows from the report, while a Range Limit limits the rows printed to only those matching specific key column values. They can both be used to create reports on a subset of your tables, but a Range Limit requires a key and a Row Filter doesn't. This makes a Row Filter completely flexible while a Range Limit is very fast. You can use both capabilities if you want to limit the range then filter out unneeded rows from that range. 3.Select the **Range Limits** tab. 4.Press the ellipsis ( ... ) button for the **Range Limit Field**. The //CUS:CustNumber// control, the only logical choice, is automatically populated.. 5.Leave //Current Value// as the **Range Limit Type** then press the **OK** button. //Current Value// indicates that whatever value is in the column at the time the report begins is the value on which to limit the report. Since the user will choose a Customer row from the //BrowseCustomer// procedure, the correct value will be in the //CUS:CustNumber// column when the report begins. **Exit and Save** 1.Press the **OK** button in the //Procedure Properties// dialog to close it. 2.Press the **Save and Close** button to return to the Application Tree. {{blk2blue.jpg|blk2blue.jpg}} **A Single Invoice Report** Next, we will print a single invoice from the Browse list of orders. **Creating the Report** 1.In the Application Tree, highlight the //CustInvoiceReport// procedure. 2.Choose **Application** {{blttria.jpg|BLTTRIA.jpg}} **Copy Procedure**. The //New Procedure// dialog appears. 3.Type //SingleInvoiceReport// in the entry box, then press the **OK** button. 4.Press the **Same** button in the //Procedure name clash// dialog. The copied procedure appears unattached at the bottom of the application tree. We'll "connect the lines" after we finish with the report. __**Delete the embed code**__ 1.Highlight the //SingleInvoiceReport// procedure. 2.RIGHT-CLICK and choose **Embeds** from the popup menu. 3.Press the **Next Filled Embed** button {{nextfembbut.jpg|NextFEmbbut.jpg}}. 4.Press the **Delete** button. 5.Answer //Yes// to the **Are you sure?** question. 6.Press the **Delete** button again and answer //Yes// to the **Are you sure?** question. 7.Press the **Save and Close** button. **Change the Table Schematic** First, we need to change the order of the tables in the Table Schematic. We'll end up with all the same tables, but instead of the Customer table as the Primary table (first table in the Table Schematic), we need the Orders table to be the Primary table for the procedure so we can easily limit the range to a single invoice. 1.Highlight the //SingleInvoiceReport// procedure, and select the** Data / Tables Pad**. 2.Highlight the //Customer// table then press the **Delete** button. This causes all the tables to disappear. 3.Highlight the //<;ToDo>// folder, then press the **Add** button. 4.Select the //Orders// table from the //Select// dialog, then press the **Select** button. 5.Press the **Change** button. 6.Highlight //KeyOrderNumber// in the //Select Keyfrom Orders// dialog, then press the **Select** button. 7.Highlight the //Orders// table, then press the **Add** button. 8.Select the //Detail// table from the **Related Table** tab, then press the **Select** button. 9.Highlight the //Detail// table, then press the **Add** button. 10.Select the //Products// table from the **Related Table** dialog, then press the **Select** button. 11.Highlight the //Orders// table again, then press the //Insert// button. 12.Select the //Customer// table from the **Related Table** dialog, then press the **Select** button. We've selected all the same tables, but now the Primary table is the //Orders// table and the related //Customer// table row will be looked up. This is important, because we need to limit this report to a single invoice and that would be much more difficult to do if the //Customer// table were the Primary. {{fsp3.jpg|FSP3.jpg}} __**Set the Range Limit**__ 1.Press the **Properties** button, and then press the **Actions** button. 2.In the Properties dialog, press the **Report Properties** button. 3.Select the **Range Limits** tab. 4.Press the ellipsis ( ... ) button for the **Range Limit Field**. The //ORD:OrderNumber// control is automatically selected as the only logical choice. 5.Leave //Current Value// as the **Range Limit Type** then press the **OK** button. 6.Press the **OK** button to return to the //Procedure Properties// window. //Current Value// indicates that whatever value is in the column at the time the report begins is the value on which to limit the report. Since the user will run this report from the //BrowseOrders// procedure, the correct value will be in the //ORD:OrderNumber// column when the report begins. __**Modify the new report**__ Now we need to change the report itself, to only print a single invoice. 1.Press the **Report** tab, and press the **Designer** button to enter the Report Designer. 2.CLICK on the Break(//CUS:CustNumber//) band and press the **Delete** key. This removes not only the Group Break, but also the Group Footer that was associated with it. 3.Choose **Save and Close** to return to the //Procedure Properties// dialog. __**Exit and Save**__ 1.Press the **Save and Close** button in the //Procedure Properties// dialog to close it. 2.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the Save button {{savebutton.jpg|savebutton.jpg}} on the toolbar to save your work. __**Connect the Lines**__ 1.Highlight the //BrowseOrders// procedure. 2.RIGHT-CLICK and choose **Window** from the popup menu. 3.From the** Control Templates **Pad, DRAG //BrowsePrintButton// > //Browse on ORDERS// to the right of the **Delete** button to place the new button control.. 4.RIGHT-CLICK on the new button and choose **Properties** from the popup menu. 5.Type //&Print Invoice// into the **Text** property entry. 6.Type //?PrintInvoice// into the **Use** property entry. 7.RIGHT-CLICK on the new button and choose **Actions** from the popup menu. 8.Select //SingleInvoiceReport// from the **Report Procedure** drop list. This Control Template is specifically designed to run a range-limited report based on the currently highlighted row in the list box we selected (Browse on Orders). The //Orders// table row buffer will contain the correct value to allow the //Current Value// Range Limit on the SingleInvoiceReport to work. It also automatically adds this button's action to the popup menu for the browse. 9.Press the **OK** button. 10.Press the **Save and Close** button to return to the Procedure Properties. 11.Press the **Save and Close** button to return to the Application Tree. 12.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Save**, or press the Save button {{savebutton.jpg|savebutton.jpg}} on the toolbar to save your work. **OK, What Did I Just Do?** Here's a quick recap of what you just accomplished: | {{blueck.jpg|blueck.jpg}} | You added several menu items to your main menu. | | {{blueck.jpg|blueck.jpg}} | You created a simple Customer List report. | | {{blueck.jpg|blueck.jpg}} | You created a relational report to print all Invoices. | | {{blueck.jpg|blueck.jpg}} | You range-limited a report to print Invoices for a single customer. | | {{blueck.jpg|blueck.jpg}} | You range-limited a report to print a single Invoice from the current row highlighted in a Browse list. | Now we'll look at where to go next. **What's Next?** **Congratulations, you made it to the end of the Application Generator lesson!** Here is the completed application, created from scratch and without the help of the wizards: {{endoflesson12.jpg|EndOfLesson12.jpg}} Welcome to the growing community of Clarion developers! While this lesson application is by no means a "shrink-wrap" program, it has demonstrated the normal process of using the Application Generator and all its associated tools to create an application that actually performs some reasonably sophisticated tasks. Along the way, you have used most of Clarion's high-level tool set, and seen just how much work can be done for you without writing source code. You have also seen how just a little embedded source can add extra functionality to the template-generated code, and how you can easily override the default ABC Library classes. __**A Short Resource Tour**__ You have many resources at your disposal to help you with your Clarion programming. Here is a short tour of two of the more important ones that you have right at your fingertips: 1.Choose **Help** {{blttria.jpg|BLTTRIA.jpg}} **Context Help**. This is the Contents page for Clarion's extensive on-line Help system. 2.Press the **Contents** tab in the left pane. Examine the FAQ sections. This opens Clarion's on-line Help file and takes you to a section of commonly asked questions and their answers. This list of topics is the first place you should look whenever you ask yourself any question about Clarion programming that starts with "How do I ... ?" These topics answer many of the most common questions that newcomers to Clarion have, so quite often, you'll find the answer is here. 3.Examine the **Guide to Examples** section. This topic provides jumps to the discussions of all the example programs that come with Clarion. Here you'll find the various tips, tricks, and techniques that the examples demonstrate so you can adapt them for use in your own programs. 4.Examine the **Whats New?** section. This topic always gives you the latest, up-to-the-minute information about the most current release of Clarion you have installed. You should always go through this section any time you get a major upgrade or interim release. There are generally a few last-minute details which you will find are only documented in this section. That makes it well worth the reading. [[lc clarion language tutorial.htm|Click here to jump to the next lesson]]