Index

Lesson 4: Creating Form Letters, Charts, Cross Tabs and Sub-reports

Introduction

JReport not only creates a standard report, but can also build dynamic and exciting formats for you. In this lesson, we will explore some of these other report formats. We will focus on four types of reports:

Form Letters

Form letters are used so that you can create pre-defined standard letters with little difficulty. Let's take a look at an example of a form letter.

___________________________________________________________

Jinfonet Gourmet Java                                          August 18, 2000
4099 Hathaway Road
Rockville, MD 20906

Absolute Java
34826 Atwood Drive
New York City, NY 1004

Dear Mr. Smith,

The current statement displays your account at our company.
Your bill comes out to be $550.00. Thank you for making your coffee
purchases with Jinfonet Gourmet Java. 

                                            Sincerely,

                                            Jinfonet Gourmet Java

___________________________________________________________


This is a letter to only one customer. What if you want to send this same letter to a thousand different customers with their individual account balances? By using the form letter, you can use fields to place the data from your database onto your letters. The following shows you the steps to create a form letter.

  1. Assuming that you have connected your database as in lesson one, start JReport and select the Standard icon on the New tab of the Open Report dialog. Click on the Create button. The Report Form Creation Wizard appears with the Data tab selected.

    Assuming you have completed lesson two, please perform the following steps for each tab to create a form letter.

    Data: Select the Customers table located under Connection/hsqldb:.../Tables and place it in the working set.

    Conditions: Select QBE, then under the Country column, type USA, and under the Region, type CA (1st row) and NY (2nd row). In this example, we are sending statements only to customers in these two states.

    Note: When inputting conditions in the QBE format, for example, you want to filter data in USA, you can just type USA instead of ='USA' under the Country column, JReport will automatically format USA to ='USA'.

    Display: Under Tables, select Customer Name. Under Formulas, select customeraddress12 and city_state_postcode. Note that we are using formulas here to combine the address components from several fields into a two-line address.

    Group: Add the Customers_Country and Customers_Region fields from the working set.

    Sort: Add the Customers_City field.
  2. So far, you can click the Finish button to generate the report. We will insert the letter body in the design mode by inserting a text object.

    l4f2.gif (5693 bytes)
  3. In the design mode, click the Insert Text button or under the Insert menu select the Text item. Insert the text box in the Detail section. Next, double click the text box, in the blank area type in the following:

    The current statement displays your account at our company. Your bill comes out to be


    After the word "be", insert DBField by clicking the Insert DBField or the on the Insert menu, click DBField, and select the Annual Sales field from the Customer table and click on the Insert button. Then, adjust the size of the DBField so that it fits its grid. Continue the rest of the dialog in the Form Letter menu with this statement:

    Thank you for making your coffee purchases with Jinfonet Gourmet Java.


    Your overall statement in the menu should look like this:

    The current statement displays your account at our company. Your bill comes out to be $#,##0.00. Thank you for making your coffee purchases with Jinfonet Gourmet Java.


    Note that the Annual Sales field you inserted is represented by a format $#,##0.00.
  4. In lesson three, you dealt with changing formats. Highlight the field you inserted in the Form Letter menu. This will bring up the properties of this particular field just like Report Inspector does. You can change the properties of the field in this area, for example, delete the unwanted field and adjust the size and position of individual object. Text can also be highlighted as well to bring up its properties.
  5. This is what your rough letter will look like:

    l4f3.gif (3880 bytes)

    Now you are ready to give it the finishing touch. If you have completed Lessons 2 and Lesson 3, you should be very accustomed to moving fields, changing fonts, and inserting objects. However there is still one step that needs to be mentioned. If you would like to change the format of your field from #,##0,### to another format such as $#,##0.00, double click on the text box. Now highlight the field and view Report Inspector. Under the TextFormat, go to Format. From the drop-down list, select the appropriate format.
  6. When our letter was completed it should look like this:

    Design Mode

    l4f5.gif (7079 bytes)

    View Mode

    l4f6.gif (3889 bytes)

    If you would like another example of a form letter, select and explore the report TextObjectEmbedded.cls in the Tutorial.cat catalog file. You may also look at FormLetter.cls for more details on the form letter we created.

Charts and Graphs

Charts and graphs create graphical views of summarized data. They can help you visualize the data in a report. This section will show you how to create and place charts and graphs into your reports.

  1. Select the Left Alignment icon on the New tab of the Open Report dialog.
  2. Assuming that you have completed Lesson 2, place the following in each tab so that we build the basic structure for our report.

    Data: Select the Customer table under and place it in the working set.

    Conditions: Select QBE, under the Country column, type "USA", and under Region, type "CA" (1st row), "NY" (2nd row), "GA" (3rd row), "MD" (4th row) and "TX" (5th row).

    Display: Under Tables, select Customer Name and Annual Sales.

    Group: Add the Customers_Country and Customers_Region fields from the working set.

    Summary: Under Field, select Annual Sales, under Function select Sum, and under Breakfield select Customers_Region. Since we chose Annual Sales for the summary, our graph will now display graphical analysis of this data.

    So far, you can finish building your report by clicking the Finish button and insert the chart in the Design mode.
  3. In the Design mode, select Chart on the Insert menu or click the Insert Chart button on the Edit toolbar. The following dialog should appear:

    l4f7.gif (10503 bytes)

    Select any of the graph formats. Refer to the User Reference book for more information on the types of graphs. For this tutorial we chose 3D Bars (side by side).
  4. Next, click the Report Data tab. The Graph Position will place the graph in the specific location you chose (Detail, Group Header/Footer, etc). We want to view the results within the country, USA, so we selected Customer_Country from the drop-down list, and check the Footer radio button to place our graph in the Group Footer.

    Select the summary Sum_Annual Sales2 under Customers_Region and add it to the Show Data list. JReport Designer will add the DBField Customers_Region to the Category Names list automatically.

    l4f8.gif (9009 bytes)
  5. Click the OK button and make some necessary adjustments. When you are done, your report might be like this:

    l4f10.gif (13616 bytes)
  6. Once again you can do some final editing here. A new tip to learn here is that if you first double click the graph, then click on the graph and drag the mouse, you can rotate the graph.  This is only available for 3D graphs such as the 3D Bars and the Pie Chart.

    For example, you can add a title to the chart, to do this,
    1. Select the chart platform and right-click the mouse.
    2. Click the Add Label item on the pop-up menu and a label will be displayed on the top left corner.
    3. Selected the label and in the Report Inspector change the following properties:

      Text: Graphical Analysis of Annual Sales
      FontFace: Vernada
      FontStyle: Bold
      FontColor: 0x0000c0

      You can also change the properties using another way: select the label and right-click, click Format Label to bring out the Chart Label dialog and then set the corresponding options.

    Note: The graph you see in the Design mode is not exactly the same as the graph you see in the View mode. Do not worry if there are values that do not make sense in the Design area. They are only there so that you can get a sense of what your chart may look like.

    Below might be the view result of your final graph:

    View (First Page of the report)

    l4f12.gif (4188 bytes)

    Last Page

    l4f13.gif (5095 bytes)

    If you would like another example of a chart and graph report, select and explore the report salestrend.cls and regionsalesgraph.cls in the Tutorial.cat catalog file.  You may also look at Lesson4b.cls for more details on the chart and graph report we created.

Cross Tab Reports

A Cross Tab is an object that summarizes data and presents the summaries in a compact row and column format. This allows comparisons and identification of trends to be displayed. Below is an example of a Cross Tab object.

l4f15.gif (5151 bytes)

In the following section, you will learn to create a Cross Tab report.

  1. Assuming that you have connected your database as in Lesson 1, start JReport and select the Cross-Tab icon under the New tab in the Open Report dialog. Click on the Create button.
  2. The Report Form Creation Wizard appears.

    Assuming that you have completed Lesson 2, perform the following steps for each tab to create a cross-tab report.

    Data: Select the Customers, Products, Orders Detail and Orders tables under the Tables node and place it in the working set.

    Joins: Join the tables manually like this: Orders_Order ID = Orders Detail_Order ID, Orders_Customer ID = Customers_Customer ID, Orders Detail_Product ID = Products_Product ID.

    Conditions: Select QBE. Under the Country column, type USA, and under the Region, type CA.
  3. Click on the Cross Tab tab. The following dialog should appear:



    The Field menu lists the working set you created. You can select any of the fields or formulas given to you in this menu. Select Customer Name under the Customers table, then click l4icon1.gif (875 bytes) to place your field(s) into the row section of the Cross Tab. Next, select Category under the Products table, and click l4icon2.gif (880 bytes) to place your field into the column section.
  4. Select Price under the Products table and click l4icon3.gif (874 bytes) to place it into the Aggregations menu. Next, highlight the Price field under the Aggregations menu. Click the Average function beside Price. This drop down list lists all the functions you can use for your fields in the Aggregation menu. Select the Sum function for the Price field.



    Next, repeat this step to select the Annual Sales field under the Customer table and implement the Sum function for this field.
  5. Highlight the Customer Name field under the Rows menu. Select the palette box beside the field, the following window will pop up.



    You can press Customer to open the palette for more colors. In this example we simply select the dark blue color.

    Repeat this step for the Category field under the Columns menu.
  6. After all this is done, click the Finish button. Your report should look like this:

    l4f19.gif (7106 bytes)
  7. Start editing and fine tuning the Cross Tab. We suggest you create a legend for your summarized fields since JReport does not place a label by these fields. This is what our report looked like after editing.

    Design

    l4f20.gif (8559 bytes)

    View

    l4f21.gif (5927 bytes)

Sub-reports

JReport allows a report (referred to as sub-report) to be inserted into another report (referred to as the primary report). There is no difference between the primary report and the sub-report. A sub-report can be standalone, i.e. it can be run separately like a normal report or it can also include another sub-report.

You can create sub-report object in two ways:

In this lesson you will learn how to create a report with sub-report object by report wizard. We will build a report that prints all employees whose salaries are lower than the group average salary in groups step by step. You can create it by following these steps.

  1. Create a report which is referred to as a sub-report.

    Assuming you have completed Lesson 2, perform the following steps for each tab to create a standard report as the sub-report that you want to insert. We will create a report file named report SalaryList.cls.

    Data: Select the Employee table under Connection/hsqldb:.../Tables and add it into the working set.

    Display: Add Employee Name, Employee_Employee ID, Last Name, First Name, Position, Hire Date, Salary fields into the report.
  2. Create a report with sub-report by report wizard. Select the Subreport icon under the New tab on the Open Report dialog.
  3. Click on the Create button , and the Report Form Creation Wizard appears.

    Assuming you have completed Lesson 2, please perform the following steps for each tab to create the primary report.

    Data: Select the Employee table under Connection/hsqldb:.../Tables and add it into the working set, set query name is Lesson4d.

    Group: Under the Group tab, add the Reports To field from the working set.

    Summary: Under the Summary tab, add the aggregate fields: Average function on Salary, break by Reports To.
  4. Click on the Subreport tab. you will see the following panel:

    l4f23t.gif (15941 bytes)

    Style: Display the type of the report.
    Section: Display the section which the sub-report will appear.
    Choose a report: Click Browse button to select a report SalaryList.cls as sub-report.
    Links: The relationship between the primary report and the sub-report. When you build a link, JReport will match up the records with equal relationships within the sub-report and the primary report.

    Here we create two links: "Reports to = Reports to", "Average_Salary2 >= Salary". Select the field "Report to" in the Field in main report box, click to add the field into the Links box. In the Links box, choose the operator " = " from the OP drop down list. Then select the field "Report to" in the Fields (Subreport) drop down list.  Create link "Average_Salary2 >= Salary" with the same way.
  5. After finishing the report, you will get the following report in the design mode:

  6. Edit the report:

    Select the Detail section, then go to Report Inspector, set property Invisible to be true;

    Place the subreport to the GroupHeader section;

    Delete the column name "Salary" in PageHeader;

    Select field Salary, change format #,##0.### to $#,##0.0# in Report Inspector. Also change the format of summary field Average_Salary2 to $#,##0.0#;

    Select subreport "SalaryList.cls", set background to light gray in Report Inspector.

    Now the report in the design mode looks like this:

  7. View the report, you can see the report result shown as below:

Index