Modifying Reports Using SQL

This is an advanced topic, and generally it will not be of interest to anyone that is not already familiar with SQL, or Structured Query Language, which is the language used by DONATION to retrieve information (including the information for reports) from the program's database. If you don't know SQL, or don't feel a need to modify the behaviour of DONATION's built-in reports, there is probably no need to read the rest of this page.

When you are viewing reports, the Filtering Reports option accessed via the Filter button allows you to restrict the currently-viewed report, to show only some of the detail lines that it started out showing. However, in some cases you may want to alter the data that is selected and displayed by the report in other ways.

An example was a user who contacted me and said that he wanted to display a report of all donors who had not received a receipt in the current year. (Presumably he wanted to write to them and ask them to renew their support.) What he wanted was a report such as Reports à Donor à Addresses, which he would use for his own mail merging, but with the restriction I mentioned.

The first step to do that is to run that report, then click the Save SQL button on the report. That prompts you for the name of a file into which it will save the SQL instructions used to generate the report. Enter a filename, such as select.sql. The default is that it is saved in your program's data directory.

Next, open that file (select.sql) in a word processor such as Word, or even better a text editor such as Notepad. You will see the following, in the case of this particular report:

SELECT  reversed_name, donor.addr1, donor.addr2, donor.addr3,

  donor.phone, donor.postal_code

FROM constants

JOIN donor ON donor.year_number = constants.year_number

ORDER BY reversed_name

The next part is the hard part. You have to figure out how you want to change the SQL. One resource is the file tables.sql, found in the program's main directory, which is generally C:\Program Files\Donation or C:\Program Files (x86)\Donation. That file gives the definitions of all of the tables and fields in the DONATION database. In this case, the change that the user ended up making was to change it to the following:

SELECT  reversed_name, donor.addr1, donor.addr2, donor.addr3,

  donor.phone, donor.postal_code

FROM constants

JOIN donor ON donor.year_number = constants.year_number

WHERE NOT EXISTS (SELECT * from receipt

                WHERE receipt.year_number = donor.year_number

                  AND receipt.donornum = donor.donornum)

ORDER BY reversed_name

You cannot change the SELECT list - it has to retrieve exactly the same columns as it originally retrieved, or the report will not work. (Though see below for a way around this.) But you can change the tables it is selecting from and the WHERE clause.

Having made the change, save it back to the same or a different file. If you are using Word or another word processor, make sure you save it as plain text, not in the Word Processor's format. And make sure you leave the file extension as .sql.

Next, still viewing the report, click the Load SQL button. That prompts you for a file to load it from - select the desired filename containing your modified SQL, e.g. select.sql. If all has gone well, your report will be redisplayed, based on the changes you made. If not, you will get an error message, which (if you are lucky) will help you diagnose what is wrong with your changed SQL.

Of course, the heading of the report will not have changed, even though you may have changed its meaning in some ways. If you print off the report to keep, you may want to write on it, to explain how you have changed it. In this case, I might write "Donors with no receipts only".

This feature does not work correctly on some reports. In particular, if a report has multiple sections, like Reports à Donation à Details, One Page per Donor, your results will be completely unreliable. That report has two sections per page, the first showing the detailed donations for each donor, the second showing the category totals of donations for each donor. If you look at the SQL saved by Save SQL, it isn't selecting donations at all, but rather just donors who have donations between a specified range of dates. Modifying that and then using Load SQL will probably lead to unexpected results. Most reports do work, however.

If you want to make larger changes to the SQL, e.g. adding or removing columns, you can instead use the saved and modified SQL in the Database à SQL Select window. You can read about that option under Viewing and Exporting Data with SQL Selects. That page also has more information about SQL, which may be helpful if you are considering using this option.