Microsoft defines the query builder embedded within its Excel application as follows: “Microsoft Query is a program for bringing data from external sources into other Microsoft Office programs— in particular, Microsoft Excel. By using Query to retrieve data from your corporate databases.”

Remember, we are most interested in MS Query as a means for generating SQL statements which we can then run against our xTuple Database. Again, having predefined queries will help accelerate the report building process in OpenRPT.

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.

Choosing Columns with Query Wizard

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:

Filtering Data with Query Wizard

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

Because 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, 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.