Step 2: The following window will appear:
Assuming you have completed Lesson 2, please perform the following steps for each tab to create a form letter.
Lesson 4: Creating Form Letters, Charts, Cross Tabs and Subreports
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 form letter.
___________________________________________________________
Jinfonet Gourmet Java
August 18, 1998
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 you 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.
How to use the Form Letter
Step 1: Assuming you
have connected your database as in Lesson 1, start JReport and select the Form
Letter icon under the New tab in the Choose Report
window. Click on the Create button.
Step 2: The following window will appear:
Assuming you have completed Lesson 2, please perform the following steps for each tab to create a form letter.
Data: Select the Customer table located under Connection/JDBC:ODBC: Jinfonet/Tables and place it in the Working Set.
Query: In the grid indicating conditions, 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.
Display: Under Tables, select Customer Name and under Formulas, select Customer_Address12 and city_state_postcode. Note that we are using Formula here to combine the address components from several fields into a two-line address.
Group/Sort: Under the Group tab, add the Customers_Country and Customers_Region fields from the Working Set. Under the Sort tab, add the Customers_City field.
Step 3: Skip the Summary tab.
Under the Form Letter tab, you will see the following window.
Under Sections select Detail. This will place your
form letter in the Detail section. Next, in the blank area at the bottom of the
dialog type in the following:
The current statement displays your account at our company. Your bill comes out to be
After the word "be", select the Annual Sales field from the menu above and click on the Add button. Continue the rest of the dialog in the Form Letter menu with this statement.
Thank you for making you 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.###. Thank you for making your coffee purchases with Jinfonet Gourmet Java.
Note that the Annual Sales field you inserted is represented by a format "picture".
Step 4: In Lesson 3, 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 the Report Inspector does. You can change the properties of the field in this area. Text can also be highlighted as well to bring up its properties. Refer to the help file for further details on the properties. Click on the Finish button to continue.
Step 5: This is what your rough letter will
look like
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 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 the
Report Inspector.
Step 6: When our letter
was completed it should look like this.
Design Area
Viewer
If you would like another example of a form letter, select and explore the
report formletter.cls in the Tutorial.cat catalog file. You may also look at
Lesson4a.cls for more details on the form letter we created.
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.
Step 1: Select the Standard Report under the New
tab in the Choose Report window.
Step 2: Assuming
you have completed Lesson 2, please
place the following in each tab so that we build the basic structure for our form letter.
Data: Select the Customer table located under Connection/JDBC:ODBC: Jinfonet/Tables and place it in the Working Set.
Query: In the condition rows, 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/Sort: Under the Group tab, add the Customers_Country and Customers_Region fields from the Working Set. Under the Sort tab, add the Customers_City field.
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.
Step 3: Click on
the Graph tab. The following window should appear.
Select any of the graph formats. Read the help file for more information on the types of graphs. For this tutorial we chose 3D Bars.
Step 4: Next, click on
the Data tab. The following window will appear.
The Graph Position will place the graph in the specific location you chose (Details, Group headers/footers, etc). Clicked the Footer button to place our graph in the Group Footer. We want to view the results within the country, USA, so we selected Customer_Country as the first data set.
The Group Field for 2nd Data Set menu is dependent on what you select in the Group/Sort tab and the Graph Position. This menu selection allows you to select which summary field you would like to see displayed. Since we only chose one summary field, the Sum_Annual Sales will be our only choice.
Step 5: Click on the Options tab. The following window will appear:
The Title field allows you to enter a title above the graph while the Left Title field places a title on the left of the graph. The Footnote field places text on the bottom of the graph. Check the Show legend box to display the legend for the graph. Type in the titles for this graph and click Next.
Step 6: For the Styles
tab, enter a report title and select Left Alignment. Click on the Finish
button when you are done. This is what your rough report looks like:
Step 7: 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 possible for 3D graphs such
as the 3D Bars and the Pie Chart.
*Note: The graph you see in the Design area is not the exactly the same
as the graph you see in the View area. 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.
This is what your final graph should look like:
Design
View (First Page of the report)
Last Page
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.
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. The following is an
example of a Cross Tab object.
How to create a Cross Tab object
Step 1: Assuming you have connected your database as in Lesson 1, start JReport and select the Cross Tab icon under the New tab in the Choose Report window. Click on the Create button.
Step 2: The following window should appear
Assuming you have completed Lesson 2, please perform the following steps for each tab to create a form letter.
Data: Select the Customer and Product tables located under Connection/JDBC:ODBC: Jinfonet/Tables and place it in the Working Set.
Query: In the grid indicating conditions, under the Country column, type "USA", and under the Region, type "CA".
Step 3: Click on the Cross Tab tab. The
following window should appear:
On the bottom left corner is the Working Set you created. You can select any of the fields or formulas given to you in this menu. In the Working Set menu select Customer Name under the Customer table. Click on the Add To Row. This will place your field into the row section of the Cross Tab. Next, select Category under the Product table. Click on the Add To Column button. This places your field into the column section.
Step 4: Highlight the Customer Name field under the Rows
menu. Select the Background drop down menu and select Custom.
The following menu will pop up.
You can select any color within this
palette for your background. After you select your color, repeat this step for the Category
field under the Column menu if you choose.
Step 5: Select Price under the Product table and click on the Add To Summary button. Next, highlight the Price field under the menu Summarized Fields. Click on the Function drop down menu. This will list all the functions you can use for your fields in the Summarized Fields menu. Select the Sum function for the Price field.
Next, select the Annual Sales
field under the Customer table. Place this field into the Summarized
Fields menu. Highlight the Annual Sales field and click on
the Formula drop down menu to select the Sum function
for this field.
If you wish, you may also change the background color of the summarized
fields as we did in Step 4.
Step 6: After all this is done, click on the Finish button. Your report should look like the following
Start editting 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 editting.
Design
View
JReport allows a report (referred to as the Subreport) to be inserted into another report (refered to as the primary report). There is no difference between the primary report and the Subreport. A Subreport can be standalone, i.e. it can be run separately like a normal report or it can also include another Subreport.
You can create subreport object by two ways:
Use Subreport report wzard.
Click Insert/Subreport or insert Subreport button.
How to create a report with a Subreport object
In this lesson you will learn to how to create a report with subreport object by report wizard. We will build a report that print all employees that their salaries are lower than the group average salary in groups step by step. You can create it by the following step.
Step 1: New a report witch referred to as a subreport.
Assuming you have completed Lesson 2, please perform the following steps for each tab to create a standard report as the subreport that you want to. such as create a report file named report SalaryList.cls.
Data: Select the Employee table located under Connection/JDBC:ODBC: Jinfonet/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 report.
Step 2: Create a Subreport by report wizard. start JReport Workbench and select the Subreport icon under the New tab in the Choose Report dialog. This time, you can see the following window.
Step 3: Click on the Create button , The following window will appear:
Assuming you have completed Lesson 2, please perform the following steps for each tab to create a Subreport.
Data: Select the Employee table located under Connection/JDBC:ODBC: Jinfonet/Tables and add it into the Working Set, set query Name is Lesson7.
Group/Sort: 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 .
Style: Under the Style tab, select indent style, and input report title Lesson4d.
Step 4: Click on the Subreport tab. you will see the following panel:
Postition Subreport at: select GroupHeader:Reports to item in choice.
Choose a report: Click Browser button to select a report SalaryList.cls as Subreport.
Links: create two links: "Reports to = Reports to", "Average_Salary2 >= Salary"
Step 5. After finishing the report, edit it :
Hide detail section in primary report.
Delete all column titles in page header such as: EMPLOYEE_EMPLOYEE ID.
Change format of field Salary and summar field Average_Salary2 .such as: #,##0.### change to $#,##0.0#.
Set backgroud to light gray for subreport.
View the report, you can see the report looked like this:
