Predefined SQL files

For users who wish to write their own SQL statement, JReport enables them to put the SQL statement into a file and then load them from this file. Note that at present each SQL file can only contain one SQL statement.

Usually, you can save the file containing the SQL statement as a plain text file (.txt). The SQL statement here supports the SQL 92 standard. Although for different databases, it may vary. The basic statement is:

SELECT...FROM...WHERE

Nested query is also supported.

The following is an example of an SQL file:

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

The SQL files can work like queries in JReport, but there are two basic differences:

• A query is based on the tables/views added from a raw database, which means, if you have only set up a connection, but have not added any tables or views into the catalog, you will not be able to build a query. However, after you have set up a connection, even if you have not added any tables or views, you can still import an SQL file into a catalog, and then build reports on it.
• Queries built using the interactive query designer will work with other tables/views when you create or modify a working set. That is, if you design a report using interactively created queries, you can add additional objects from tables/views into the working set afterwards. However, when you design a report using an SQL file, you will only be able to use the contents already included in the SQL file. Due to this, you should make sure that you have included all the required fields in your SQL file.

In addition, JReport Designer provides you with the Data Manager which allows you to control the data retrieval of your SQL files that function as queries, including the number of rows to be displayed and the duration required for the retrieval. It can also keep access information from previous runs of an SQL file.

Adding SQL files to a catalog

After you have set up the JDBC connection and created some SQL files, you can then add them to a catalog. To do this:

1. In the Data tab of the Catalog Browser, right-click the JDBC connection, then click Add SQL on the shortcut menu to display the Select an SQL File dialog.
2. Browse to the SQL file and click Open.
3. In the Input SQL Name dialog, enter a name for the SQL file in the SQL Name text box, then click OK to add the SQL file to the connection.

Updating an SQL file

If you make any changes to SQL files in the database, you will need to update them in the connection so that reports built on them can work properly. To do this:

1. Select any SQL file, right-click it and select Update from the shortcut menu.
2. In the Select SQL File dialog, select the SQL files you want to update, and then click Open.
3. In the Select Catalog dialog, select a catalog from the From DB Catalog drop-down list.
4. Click the OK button to close the dialog.

Developing reports from SQL files

When you have added some SQL files to a catalog, you can then use them to develop reports as required.

1. Make sure the catalog where the SQL file locates is opened, then click the New Page Report button on the Standard toolbar.
2. In the New Page Report dialog, specify the title and layout of the first report tab in the report as required, then click OK.
3. In the Data screen of the report wizard, choose the SQL file with which you want to build the report tab, then follow the report wizard to create the report tab.

Creating cached query results for SQL files

By default, when you run a report, JReport Engine fetches data from the database using the JDBC driver. For reports that are built on imported SQL files which function as queries, JReport enables you to create cached result files and save them somewhere in your machine. Then, when you view these reports, you can choose to use the data from the cached query result file as opposed to the database.

For details about cached query results, see Cached query results.