Lesson 6: Creating a Report Use a Query File

JReport supports users to create reports using tables, views, or queries.  Users can build most queries using the interactive query designer.   For those who wish to write their own SQL statements, they can put the SQL statements in files and load these SQL statements from the files.  In this lesson you will learn how to create a report use an existing file that contains an SQL statement.

1.  Editing a file containing an SQL statement

In this lesson, we create a file called sqlfile.txt that contains the following SQL statement.  Please note that at present each SQL file can contain only one SQL statement.  The following example SQL statement is based on the Jinfonet data source that we used throughout this tutorial.

SELECT Catalog."Product Type Name", Catalog."Product Type ID", Catalog.Description, Products.Category, Products."Product ID", Products.Price, Products."Product Name", Customers.Region, Customers."Contact Position", Customers.Country, Customers."Customer ID", Customers.Address2, Customers.Address1, Customers."Contact Title", Customers.Phone, Customers."Contact Last Name", Customers.City, Customers.Fax, Customers."Contact First Name", Customers."Annual Sales", Customers."Customer Name", Customers."Postal Code", Orders."Order ID", Orders."Required Date", Orders."Customer ID", Orders."Shipping Cost", Orders."Ship Date", Orders."Order Date", Orders."Employee ID", Orders.Shipped, Orders."Payment Received", Orders."Ship Via"
FROM Catalog, Products, Customers, "Orders Detail", Orders
WHERE (Products."Product Type ID"=Catalog."Product Type ID") AND ("Orders Detail"."Product ID"=Products."Product ID") AND (Orders."Customer ID"=Customers."Customer ID") AND (Orders."Order ID"="Orders Detail"."Order ID") AND ( ( Customers.Country='USA' ))

2.  Linking the SQL file with the Catalog Browser

Start JReport Designer, open the Catalog Browser, expand Connection until you see the SQL icon, which you should click to select. Now on the tool bar of the Catalog Browser, you will find a "+SQL" button.

When you click on the new query button, the following dialog will be shown.  You can select an existing query file.  In this lesson, we select sqlfile.txt then click on the Open button.

Now the following dialog appears, you can enter any name as you like to create a query in the Catalog Browser.  In this lesson, we will call it SQL1.

3. Create a report using the query file

After the above steps, the newly added query can work like other query.  However, there is a difference between them.  The queries that are built using the interactive query designer can works with others tables, views when users create or modify a working set.  That is, when users design a report using interactively created queries, they can add additional objects from queries, tables, or views into the working set.  But when users design a report using a query that is loaded from a query file, they can only used the contents already included in the query.   In this lesson, we made sure that we have included all required fields in the query file.

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

• In the Data panel, choose the SQL1 (that you just created) as your working set.
• You can skip the Query panel, since you have already used a query file to create the report.
• In the Display tab panel, add Product ID, SQL1_Product Name, SQL1_Price
• In the Group/Sort panel, set Group by Customer ID and Order ID
• In the Summary panel, add aggregate fields: define the count function on SQL1_Price, break by customer id, etc.
• Skip the Graph Tab.
• In the Style panel, select Block style and enter report title "Lesson6".

When this report is completed, press the View tab and it should look like this.