A Sample Ad Hoc Launcher

browseDB is an example of how you might use the Ad Hoc Launcher API to create your own query editor. While it illustrates the API’s basic functionality, and is a useful example for administrators and developers who plan to implement their own launcher, it isn’t intended for end users. The application supports only the most basic SQL functions; it does not support such operations as a 2-column primary key or joins from parent to child (you must join child to parent).

In order to construct queries in browseDB, you must know the primary keys of the tables in the data source and any database constraints on joins.

Installing the Sample Editor

To install the browseDB editor:

1. Determine the location of the JasperReports Server web application; for the default installation with the bundled Tomcat, it is <js_install>\apache-tomcat\webapps\jasperserver-pro\.
2. In an appropriate editor, open the file <js_install>\samples\customAdHoc\build.xml.
3. In the file, set the webAppDir property to the web application location in step 1.

Setting the webAppDir Property

4. Save and close the file.
5. On a command line, change directory to <js_install>\samples\customAdHoc.
6. Run <js-install>\apache-ant\bin\ant deploy.

The ant utility compiles the Java source, deploys the browseDB files to the appropriate folders, and displays a BUILD SUCCESSFUL message.

If there are permissions errors, make sure you have ownership or write permission in the <js-install> folder.

7. Restart JasperReports Server.

Using browseDB

To define a simple query in browseDB:

1. In a web browser, start JasperReports Server and login with administrator privileges.
2. Point your web browser to http://<host>:<port>/jasperserver-pro/browseDB/browseDB.html?action=pickDatasource.

The Pick datasource page appears.

BrowseDB Pick Data Source Page

3. Select a data source from the drop-down and click Display Tables. In this example, select the FoodmartDataSourceJNDI.

A list of the tables in the data source appears.

Tables From the Selected Data Source

4. To select the main table for the query, click that table’s name. In this example, click the employee table.

The selected table appears at the top of the list as the Main query table. It is expanded to show all the columns in the table.

Main table for the Query Selected

To join a table to the query:

1. Before you can join a table, you must designate one of its columns as a primary key. Expand the table you are joining by clicking . If you click the folder icon or name instead, that table becomes the main table of the query, replacing the one you selected. In this example, expand the store table.

The Primary Key store_id in the store Table

2. Indicate the additional table’s primary key by clicking the appropriate column name and clicking Set the selected column as primary key at the top of the page. In this example, select the store_id column of the store table as the primary key.

The additional table is added to the Join drop-down. The drop-down shows all the additional tables for which you select a primary key. In this example, the store table is added to the drop-down.

The store Table in Join Drop-down and store_id Column Selected for Join

3. In the Join drop-down, select the table to add. Since you only expanded the store table, it should already be selected.
4. In the main query table, select the column to which the additional table should be joined. In this example, select the employee table’s store_id column.
5. Click Join selected column on main table to.

The additional table (store) is added to the main query table as the join table. Expand the join table to see all of its fields. The following picture shows the join table, along with some of the corresponding fields as they are displayed in the Ad Hoc Editor.

The Joined Tables and Correspondence to Ad Hoc Field List

To open the Ad Hoc Editor with this query:

1. In the Pick Datasource window, click a type (Table, Crosstab, or Chart). In this example, click Table.

The Ad Hoc Editor opens. All the columns of the selected query tables appear in the editor’s list of available fields.

2. Define and save your view in the usual way. For more information, refer to the JasperReports Server User Guide.

To open a saved Ad Hoc Report created with the sample Ad Hoc launcher:

1. Locate the view saved from browseDB in the repository.
2. Click the view.
3. Because this view was saved, there is no longer the choice of Table, Crosstab, or Chart. The view will open to the format in which it was saved.