Lesson 3:  Creating and Printing Reports from Multiple Tables

In the last report, you learned how to create a report using a single table from the database.  In this lesson you will learn how to create a report that will take data from not one table, but several.  Before going through the step-by-step process of creating the report for this lesson, you may want to first examine the fully executed report called lesson3 which can be found in the tutorial catalog. 

The following key points will be addressed in this lesson:

• A Standard Report with Multiple Tables
• How to Create a Report with Multiple Tables
• Using Report Wizard
• Formatting and Editing Your Rough Report
• Adding Color to Your Report
• Adding Images to Your Report
• Adding and Editing Formulas and Summaries
• Formatting Fields
• Final Formats and Edits
• Printing and Exporting to HTML
  

A Standard Report with Multiple Tables

Jinfonet Gourmet Java
6/19/98

This report can be created by JReport by simply adding fields from the Customers, Products, Orders, and Orders Detail tables found in the demo database.   Subtotals, Taxes, and Total Order Amount are calculated using the Formula and summary objects of JReport.  

How to Create a Report with Multiple Tables

Part I: Using Report Wizard

1. Start JReport according to the instructions in Lesson 1 and create a new report using the Standard Report wizard.
   
2. With the data tab of the Standard Report Wizard you will select Customers, Products, Orders, and Orders Detail tables for the Working Set.
   
3. Click the Query tab.  You will now notice that the four tables in the Working Set are displayed and joined automatically based on the default join criteria.  You can also manually join fields by dragging and dropping fields together. Type >3010 in the Order ID column in order to see order IDs greater than 3010.

   
   

4. Click the Display tab. 

    

   For this example, select:
   
   Products Table: Products_Product Name, Category
   Customers Table:  Customer Name
   Orders Detail Table: Unit Price, Quantity, Discount
   Orders Table: Shipping Cost, Order Date, Ship Via.
   

5. Click the Group/Sort tab.
   

For this example, we will group the data by Orders_Order ID, which means putting the data with the same Orders_order ID together in one group, choose Orders_Order ID to group by this field.


6. Skip Summary and Graph tab.  We will not be focusing yet on these two functions.
    
7. Now click the Styles tab and enter a title for the report: "Jinfonet Gourmet Blends." We chose to use Left Alignment in the Style menu.  Click Finish to generate the report.
    
8. Part II: Formatting and Editing Your Rough Report

1. Your report design should look like this so far:
   
   
   
   
   This is very rough and far from the nice report you would like to have.  However, with JReport it is easy to format this report and give it a professional look.
   
   Start out by picturing in your mind  where you want the data.  Do you want to put the ordering information in the group header and summary in the group footer?  Perhaps drawing a rough sketch of your report on paper would be very helpful.
   
2. You can start by editing the system generated field labels into something more readable.  Use the mouse to select a label.  You can either edit the label directly by double clicking it.  Resize, Drag and drop labels to make them look the way you wanted.
   
3. The example below can be used as a guide to organize the fields.  Use the View tab frequently to examine the progress of your design.
   
   

Part III: Adding Color to Your Report

Reports utilizing plain text can look boring.  JReport has the option of changing the background and foreground color of any object.

1. Make sure all of your label objects are aligned like the example given above.  Whenever you click an object, it gets in focus and its property values are displayed in the Report Inspector.  You can manually adjust any of its values.  For example, you can change the width of a field precisely by entering the exact number of pixels.
   
   
   
   
2. Select the label field Product Name.  Go into Report Inspector and under Color select Background and choose dark blue.  Next, under Color, select Foreground and choose white.
   
   
   
   
3. The result of your color selection will be shown instantly on the screen like this:
   
   
   
   
   Repeat step 2 to highlight and resize other labels.  Try to make your highlights look like the example below:
   
   
   
   
   Hint: A quicker way to change all these colors is to click and drag the mouse and define a box that selects all the objects at once.  Then use Report Inspector to change the color in one try.
   

Part IV: Adding Images to Your Report

Adding an image to the report is a very simple task.

1. In the main design window, go to the Insert menu and select Image... 
   
2. Select any GIF or JPG file.  You may use the image file Coffee.jpg provided by JReport in the directory \JReport\demo\reports\tutorial.
   
3. Place the image in the upper right corner (or anywhere else) on the report.  You may resize as necessary by dragging the handles or by using Report Inspector.

Part V: Adding and Editing Formulas and Summaries

In this section you will learn how to define Formulas and Summaries in the Catalog Browser and add them to the report.

1. The example invoice report has the following components:
   
   • A billing address
   • Total price for each product ordered
   • The total cost of all the products ordered
   • The shipping charge and tax
   • Total cost of the invoice

   Let's first add the billing address.  A typical address contains the street, city, country, and postal code.  The components are usually concatenated to form the full address.  Care must be taken to skip the missing components and trim off excess blanks.  In JReport this can be done using Formula.
    
   The Tutorial catalog provides two predefined formulas called Customeraddress12 and city_state_postcode (see the table below) to format the address.  To access the formula editor from the Catalog Browser, click the Data Tab, expand Formula and right click on either Customeraddress12 or city_state_postcode.  Then select edit formula to bring up the formula editor.  The examples are self-explanatory.  A precise Syntax definition is available for your reference.
   

   Customeraddress12	city_state_postcode
   if ( IsNull( @"Address1" ) )     @"Address2" else if ( IsNull( @"Address2" ) )     @"Address1" else     @"Address2" + ", " + @"Address1"	if ( IsNull( @"City" ) ) { trim(@"Region") + ", " + trim(@"Postal Code") } else if ( IsNull( @"Region" ) ) { trim(@"City") + ", " + trim(@"Postal Code") } else { trim(@"City") + ", " + trim(@"Region") + ", " + trim(@"Postal Code") }

   
   If you double click a formula, you will enter into the insert mode which allows you to place the formula anywhere in the report.  Try to double click formula Customeraddress12 and put it under the Customer Name section.  Next, go back to Catalog Browser, double click on city_state_postcode.  Place this formula under the Customeraddress12 formula in the design window.  Delete the labels that come with these formulas.
   

2. The next step is to figure out the total unit cost for each product.  Select the menu View, and choose the Catalog Browser item to bring up the Catalog Browser, double click Formulas, and right click total.  Select Edit formula.   You should see a menu appear as the example below.  Enter the formula as shown below.
   
   
   
   
   This formula calculates the total by multiplying the Unit Price by Quantity and subtracting the Discount.  Click on the Check button to make sure this formula has no syntax errors.  If all is well, click on OK.
   
   Next, double click on total.  Place this formula next to the Quantity field as seen below.  Because the total will be computed once for each Detail item, make sure that the field is located within the Details section and the label is placed within the Group Header.  Add colors like you did in the third section.
   
      
3. Summary functions are used to compute aggregated detail values.  Select the VIew/Catalog Brwser to bring up the Catalog Browser and double click on the Summary to reveal the existing summary functions.  Right click on Subtotal and select Edit Summary.  Notice how the summary works.  The function Sum takes the values from the formula total over a group of detail records defined by identical Order_Orders ID.  You can edit the summary function by selecting from the Resource and pressing the  button.  Note that the break field you choose must be the same exact field you grouped your report at the very beginning of the Report Wizard. 
   
   
              
   
   
   
   
4. Now close the Summary Editor dialog.  Go back to Catalog Browser, Summary, and double click on Subtotal.   Place this summary function in the bottom Group Footer as in the example.
   
    
   
   
   
5. The next formula we would like to place is tax.  Bring up Catalog Browser and double click on formula.  Select tax, right click, and select edit formula to see the formula: (@Subtotal+@"Shipping cost") * 0.05.  Click OK.  Double click on tax and place the formula under the shipping formula in the group footer section.
   
   
   
6. The last formula to be placed is the total of the subtotal, shipping cost and tax.  Select the View menu and choose the Catalog Browser item to bring up the Catalog Browser.   In the Catalog browser, double click the Formulas and select formula TotalAmount (if there is no such one then create one).  Right click and select Edit Formula to see the formula @Subtotal + @"Shipping cost" + @Tax.  Click the Check box to verify the formula.  Click OK.  Double click on the formula and place it in the group footer section under the tax formula.

   
   

Part VI: Formatting Fields

JReport permits you to define display format for various field types including currency and dates. 

1. First, highlight the field Unit Price and click Text Format and then click format.  In the drop down menu, select $##,##0.00 which specifies a leading $ sign and the required comma and decimal places commonly used for currency .  
   
    
   
   
2. Continue this formatting for all other currency data such as total, Subtotal, shipping cost, tax and TotalAmount.
   
3. Similar format changes can be done on dates.   Select any date field, and select the format mm-dd-yy in Report Inspector. 
   

Part VII: Final Formats and Edits

There is still some editing and formatting to complete before this lesson is over.   Begin by changing the names of the labels so that they suit your need.  Next, move data and titles to areas on the report where you feel are appropriate.   Unnecessary headers and footers may be deleted by selecting the area and right clicking the mouse to reveal a menu.  Click on Hide to rid yourself of unwanted section.

Usually each invoice page shows only one order.  Since the report is grouped on Order_ID, we can simply make each group occupy a separate page.

1. Click the background area of the Group Footer to select the section. 
   
2. Bring up the Report Inspector.  Scroll down till you see a section called Others. 
   
3. Under that section, find fillWholepage and select true in the menu.  This function will "fill" the entire page with the section you highlight.
   

Our report looked like this in its final stage. 

This is what it looks like if you view your report on the viewer.

Part VIII: Printing and Exporting to HTML

Once your report is finished, you may want to be able to print it or place it on the Internet.  JReport is ready to suit your needs. 

Printing Calibration:

In order to over come current limitation of JDK1.1.x, JReport needs to be calibrated on the very first print, so that lines and text are printed correctly.  Under File, select print.  A screen should appear like the example below.




Please follow the instructions that are given to you on the screen.  You only need to calibrate once for each printer that uses JReport.  After you complete this task, you may print the report by clicking the OK button.

Exporting to HTML

1. Placing JReport on the Internet is fairly simple.  Under File, go to Export and select To HTML... 
   
2. On the Report Tab, under Web Page Name, type in a name.  Otherwise JReport will use the report name as the default HTML file name.  Since JReport gives the extension *.html automatically, do not type an extension into the name of your report.
    
   JReport designates a serial number for each HTML page.  For example, if you named your 3-page report Sales, JReport will create three files called Sales_1.html, Sales_2.html, and Sales_3.html. 
   
3. Under the Directory Tab, you can specify the directory where you want the HTML files to be saved.   Otherwise JReport will use a default directory.
   
4. Under the Version Tab, please select a HTML version and a web browser version.
   
5. In the ChartApplet Tab, you have the option of displaying the charts you have placed in your report in the HTML format. By default, JReport will not show charts in HTML, so if you want such images to be seen, please select Embed Chart Applet. 
   
6. It is recommended that you read the contents under the Caution Tab before you convert your report to HTML format. 
   
7. After all steps have been taken, click on the OK button to convert your report to HTML.