User Tools

Site Tools


lc_creating_reports.htm
Navigation:  User's Guide and Tutorials > Guided Tour of Working in Clarion >====== 12 - Creating Reports ====== Previous pageReturn to chapter overviewNext 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 button.

5.Type Print &Customer List in the Text property entry, then press the TAB key.

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 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 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 on the IDE toolbar.

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

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 Populate BLTTRIA.jpg Multiple Columns from the IDE toolbar.

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

Place the rest of the columns

1.Choose Report Designer BLTTRIA.jpg Populate 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

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 to “visualize” how the printed page will appear.

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 Save, or press the Save button savebutton.jpg on the tool bar to save your work.

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

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

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 Bands BLTTRIA.jpg Surrounding Break.

L12SurrBreak.jpg

The Format Break Properties dialog appears.

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

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

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

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

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

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

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

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 Save, or press the Save button savebutton.jpg on the toolbar.

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

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

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

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

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 Save, or press the Save button 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 &amp;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 Save, or press the Save button 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 You added several menu items to your main menu.
blueck.jpg You created a simple Customer List report.
blueck.jpg You created a relational report to print all Invoices.
blueck.jpg You range-limited a report to print Invoices for a single customer.
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

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

Click here to jump to the next lesson

lc_creating_reports.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1