Report Properties

Below is an example report.

Lets say for example you want to export all pallets in the database in a certain date range.

First of all you will need to know the names of the tables and columns in the database and to assist with this you can use the Schema Browser.

The SQL statement we can experiment with is as follows :-

SELECT * FROM APP_PALLET 
   WHERE DATE_OF_MANUFACTURE >= ? 
   AND DATE_OF_MANUFACTURE <= ? 
   AND CONFIRMED = 'Y'
   ORDER BY DATE_OF_MANUFACTURE

You will notice that in the query there are two question marks '?'. These are place holders for date paramters which you can supply at runtime.

Notice that the checkbox labelled "Date Parameters Required" has been selected.

You should be careful when creating queries which can return a great many rows.

We could amend our query to look like this if we wanted to ensure that we never return more than 1000 rows.

MS SQL Server

SELECT TOP 1000 * FROM APP_PALLET 
   WHERE DATE_OF_MANUFACTURE >= ? 
   AND DATE_OF_MANUFACTURE <= ? 
   AND CONFIRMED = 'Y'
   ORDER BY DATE_OF_MANUFACTURE

mySQL

SELECT * FROM APP_PALLET 
   WHERE DATE_OF_MANUFACTURE >= ? 
   AND DATE_OF_MANUFACTURE <= ? 
   AND CONFIRMED = 'Y'
   ORDER BY DATE_OF_MANUFACTURE LIMIT 1000

Oracle

SELECT * FROM APP_PALLET 
   WHERE DATE_OF_MANUFACTURE >= ? 
   AND DATE_OF_MANUFACTURE <= ? 
   AND CONFIRMED = 'Y'
   AND ROWNUM <= 1000
   ORDER BY DATE_OF_MANUFACTURE 

If we run this report with the date range shown on the DEMO database then we should get Excel output as shown.