OpenRPT Product Guide

In this section we will illustrate how to use Excel's MS Query to build a query which we will then insert into an OpenRPT report definition to generate a report. Below is the final SQL generated by MS Query using our ODBC connection to the usr table in our xTuple database:

SELECT usr.usr_id, usr.usr_username, usr.usr_propername, usr.usr_passwd,
       usr.usr_locale_id, usr.usr_initials, usr.usr_agent, usr.usr_active,
       usr.usr_email
FROM public.usr usr
ORDER BY usr.usr_username

Now we will show the steps involved to generate the predefined query.

First, open the Excel application. From the Data menu select the Get External Data option. Then select the New Database Query option. You will be presented with the following screen:

Selecting ODBC data source

Select the new ODBC connection you just created against your PostgreSQL database, and then click the OK button.

Query Wizard - Choosing Columns

In the left column, scroll to the table usr and click on it. Then select the > button to select all columns in the table. Finally, select the NEXT button to be brought to the following screen:

Query Wizard - Filtering Data

Next, the Query Wizard provides you with the opportunity to filter the data. For this exercise, we will choose not apply a filter to the data. Click the NEXT button to reach the following screen:

Sorting criteria with Query Wizard

We will define one sort criteria. In the Sort by field select the column usr_username and check Ascending next to it. Click the NEXT button to reach the next screen:

Multiple output options

Since we are only using query to generate an SQL statement, we check the option View data or edit query in Microsoft Query. Click the FINISH button.

Viewing the SQL

Initially, Microsoft Query displays the results of the query we just created with the Query Wizard. To see the syntax of the SQL statement, click the SQL tool in the toolbar.

To copy this SQL statement for importing into an OpenRPT report definition, select the statement and then right click and use the Copy option. Later you can paste the statement into an OpenRPT query source.

Earlier chapters in this user guide covered the details of modifying and creating OpenRPT report definitions. The next few screens show the core elements of a report definition and the resulting output.

SQL pasted in Query Editor

Above you can see the SQL statement exactly as it was copied from Microsoft Query and pasted into an OpenRPT query source for a new report definition. We will run this report using the xTuple application—and so we will save it with the already-existing report definition name "UsersMasterList". To distinguish this version of the report from others, we will assign this version a grade of "20". In xTuple, report definitions having the highest grade are used. Assuming "20" is the highest grade for UsersMasterList, our new version will be run with users. Select PRINT from the xTuple master list of users.

Below is a look at the report definition we created using the query we built with Microsoft Query.

View of report definition

This report definition will be run when an xTuple user selects PRINT on the following xTuple screen:

xTuple Users master list

After we select PRINT, the report definition is printed just as we planned:

Final report output

The report shown above was generated using the SQL we copied from Microsoft Query. It demonstrates the speed and ease with which new reports can be created and deployed using OpenRPT.