Ad hoc Reports

Document path Reference Contents > General Program Functions > Ad hoc Reports

The Ad hoc reports feature is a standard part of the Omnis Studio environment and is not written by Caliach. The following is the help provided by Omnis.

The ad hoc report plug-in offers you a quick way to generate reports and query your data. You can create ad hoc reports that access your server or Omnis database. You can also use SQL reports with the Omnis SQL DAM to access an Omnis data file. The term column is used in this section to mean both server table columns and Omnis file class fields.

Ad hoc reports are based on standard Omnis report classes, hence modifying an ad hoc report is very similar to modifying a standard report.

Creating Ad hoc Reports

You access the ad hoc report tool from the Reports menu. You need to install this menu to create or modify ad hoc reports. You also need to open a session using the SQL Browser, or an Omnis data file using the Data File Browser.

To create an ad hoc report

Template selection

If you select the Labels template the next pane in the wizard prompts you to enter details about your labels. You can set the paper size, label format, margins, and so on, or you can select a label format from the list of Ready Labels.

Label layout selection

For all templates, you next select the columns you want to include on your report.

You can either select a table or file name to include all the columns, or you can open individual tables or files and select individual columns. For Omnis data access you may need to select the Main file as well.

Table selection

Query and field list

The Query and field list displays all the columns you included on your report. For each column you can specify a Title or label, whether or not the column is sorted, totalled, or visible, a calculation, and a formatting string. You can select a formatting string from the dropdown list next to the entry field. You can drag and drop the field names in the field list to re-sort the columns on your report

At this stage you can print or view the output of your report on screen, but since you haven’t added any query or filtering yet you will get back all the data.

To Join tables in your report

If your field selection includes fields from more than one table, where you wish to join a "child" (subsidiary) table to the "parent" (main) table in your report, you must use the DML Joins Wizard.

Click on the icon in the toolbar.

DML Join Tool

The DML Joins Wizard window will display two lists of fields for selecting the parent table and related child. Click on one field in each list to join the tables - your selection will now be shown below the field lists under Key #1.

DML Join Wizard

You may add other joins by clicking "Next", or remove them by clicking "Clear All". When your joins are showing correctly click "Finish" and the wizard will close and return you to the main query window.

Warning WARNING: When selecting a child table, you must ensure that the field being sought is going to be a unique entry - otherwise you will return multiple records from your parent table for each instance of the matching child data.

To view the output of your report


The Output screen displays all your data using the layout you selected in the wizard. You can select data from the output screen by dragging the mouse and selecting Copy from the Edit menu, or you can use Select All to select all the data in your report and Copy. This copies your data in tab-delimited format into the clipboard. From the Output screen you can also print directly to the current printer or export the data to a file using the appropriate buttons on the toolbar.

To get back to the Query and field list

If you attempt to close an ad hoc report without saving, you will be prompted to save before closing.

To save an ad hoc report

Adding Columns or Fields

When you create an ad hoc report from scratch you select the columns to include on your report in the wizard, but you can add further columns to your report at any time from the field list context menu.

To add columns or fields

Context menu

Add fields

For ad hoc reports based on Omnis data, you can also change the main file from the Add Fields dialog.

Adding and Editing Sort Fields

Assuming you have included all the columns you need in your report, you may want to sort the report in a particular order.

To specify a sort field

The order in which you check the Sort check boxes determines the sort level, that is, the first column you check is the first sort field, the second column you check is the second sort field, and so on. You can specify up to nine sort fields for a single report.

To edit the current sort fields

Sort fields

Note that you must add sort fields in the Query and field list by checking the appropriate box, not from the Sort fields dialog. For each sort field, you can select the following options.

When you rebuild the report, your data is sorted using the specified sort fields in their correct order. For example, you could sort a Customer file on COUNTRY which is sort field 1, on STATE/COUNTY which is sort field 2, and TOWN which is sort field 3.

Adding a Query

For each column or field in your report you can specify a query that must be fulfilled if a particular row of data is to be included in the output.

To add a query to your report


Query options

The column or field is added to the query list. The Equal to comparison is selected by default, but you can select a different operator and enter the value or calculation for the comparison. The following operators are available

For example if you want to select all the rows or records from a customer database where the NAME is equal to ‘Bob’ you can enter the following query.

Query example

Strings must always be quoted, and numeric columns or fields can include numbers or calculated values including other variable names. Boolean fields take values of YES, 1, NO, 0, '' (empty), or Null. You can enter date and times in the comparison field but its interpretation depends on the data format of the column or field.

Multi-line Queries and Logic

You can add multiple columns or fields to a query. A multi-line query always assumes the connecting logic is AND, but you can insert your own logic into the query.

To add logic to your query

You can drag and drop lines in a multi-line query to reorder the query, including the AND and OR operators. You can also delete a single line or all lines using the query list context menu. You can group particular lines in a multi-line query by selecting the AND and OR lines and clicking on the promote and demote buttons marked < and >.

Adding Calculated Fields

You can add calculated fields to the ad hoc report field list, for example, you can concatenate two values into one report line. The calculations are based on the current values in the columns taken from the current data row or record.

To add a calculated field

Add calculated field

For example, in a label report you can enter a calculated field called FullName and use the con() function to concatenate the First name and Last name fields.

Calculation example

At this stage you may want to change the layout of your report, or add some graphics. Furthermore if you have added calculated fields to your report you may need to rearrange the fields on the report.

Modifying your Report

You can change the layout and positioning of the fields on your report at any time by switching the ad hoc window to modify report mode.

Warning WARNING: Before changing the layout of the report, ensure that you have completed all changes to your query that may cause the report to auto-format, for example adding fields, modifying the visible status or titles etc. Auto-formatting will cause some of the changes you make in this window to be lost.

Warning WARNING: Although this view will show landscape format on screen if selected in Page Setup, before printing it is necessary to go to File -- Page Setup and select landscape here also. You must also remember to restore this to portrait after printing your ad-hoc report.

Tip TIP: To ensure that all your fields display in this view, it may be necessary to turn off "Show Paper" from the toolbar.

To modify your report

Layout design

You can add text labels and graphics, and move fields around and change their colours. If you delete columns or fields from the report they will not appear in the output, but they will still be included in the field list and are still part of your query.

Ad hoc Report Templates

When you create a new ad hoc report you base it on one of the templates stored in the ad hoc library. However you can change the templates via the Template Browser.

To change a report template

Template Browser

You can add graphics and change the font and colour properties of any object in a template, and you can examine the methods behind the report. However you should avoid changing the structure of the sections and programming in the templates provided. The Templates menu in the Template Browser lets you create your own templates based on standard report classes. Your own templates will appear in the Ad hoc Report Wizard when you next create a new report.

Re-using Ad hoc Reports

Ad hoc reports are saved to disk as small library files with the .ahr file extension. Each file stores a copy of the report layout and query. You can open an existing ad hoc report file from the Reports -- Open menu option.

Ad hoc report Notation (for advanced users)

The Ad hoc tool contains a number of public methods that you can call using the $dotoolmethod() method, available under the $root.$modes group.

Creating new ad hoc reports

The $x_newadhoc() method prompts the user to create a new ad hoc report by launching the ad hoc report wizard. Note no parameters are required.

Do $root.$modes.$dotoolmethod(kEnvToolAdhoc,'$x_newadhoc')

Opening existing ad hoc reports

The $x_openadhoc() method prompts the user to open an existing ad hoc report. Note no parameters are required.

Do $root.$modes.$dotoolmethod(kEnvToolAdhoc,'$x_openadhoc')

Printing ad hoc reports

The $x_printadhoc() lets you print an ad hoc report. For example:

Do $root.$modes.$dotoolmethod( kEnvToolAdhoc, '$x_printadhoc', 'c:\mypath\myreport.ahr')

If no path is specified, the user is prompted to select an ad hoc report.

Table Selection

The table selection window by default has a checkbox option to display tables for all users if the database supports this feature. You can hide and show this option using the $x_allowanyuseroption() method as follows:

Do $root.$modes.$dotoolmethod(kEnvToolAdhoc, "$x_allowanyuseroption", [kTrue|kFalse])

Compiled in Program Version 3.10. Help data last modified 22 MAY 2008 07:48. No class.

Document path Reference Contents > General Program Functions > Ad hoc Reports