Creating Custom Reports

You can use Reports à Custom Report to create your own relatively simple reports. This is actually one of two ways to create custom reports. This way uses a fairly simple window, where you select fields (also called columns) from lists, and select the range of years to report on. When you click OK, it determines the SQL (Structured Query Language) statement that will be used to select the report data, and displays that SQL in the same window that Database à SQL Select displays.

The other way to create custom reports is to use the SQL Select utility directly. You should read the section on that to see what it can do. With it, you can adapt the SQL from existing reports, or write your own entirely new reports, but you do have to have at least minimal knowledge of SQL to do so. With the Reports à Custom Report option, you can create reports without knowing any SQL.

When you use the Reports à Custom Report utility, a window comes up titled "Create Custom Report". You will see that in section 1 of the window, there are three lists of columns that you can select, from the Donor, Donation and Receipt tables. A table is what SQL calls the collection of data of one type, e.g. the Donor table contains all of the Donor information that you type onto the main screen. A column or field is one type of information within a table, such as the Last Name in the Donor table.

The first thing you need to do is select the columns that you want to be included in the report. Just click on each column that you want included, which selects it. If you change your mind about including a column, click on it again to deselect it. To see all of the columns in the Donor table, you will have to scroll down the list.

Most of the column names shown in the three lists should be very obvious, because they match a piece of data that is entered or displayed on the main window of the program. The Receipt table columns are mostly not displayed on the main window, but I hope their meanings will also be obvious. Here are a few that aren't quite as obvious:

-	In the Donor Table, "Name" is the Business Name if it exists, and otherwise the same as "First Last" (see below for details on that).

-	In the Donor Table, "Reversed Name" is the Business Name if it exists, and otherwise the same as "Last, First" (see below for details on that).

In the Donor table, "First Last" joins the names in that format, e.g. "Dan Cooperstock", but it will never display the Business Name. The advantage of using this over using First Name and Last Name separately is that you don't have the First Name and Last Name displayed in separate columns, taking up a lot of width in the report.

-	In the Donor table, "Last, First" would similarly display my name as "Cooperstock, Dan", but will never display the Business Name.

-	In the Donation table, the Comments column is obvious, but you should be aware that it won't always print well on reports, and generally only the first line or so will be displayed. However, you will be able to see more data if you export it using the Save As button on the reports window.

-	In the Donation table, the Sum(Total Amount) and Sum(Eligible Amount) columns allow you to do summary reports, e.g. total givings by donor, rather than the detailed reports of donations that you will get if you select other columns from the Donation table but not this one.

-	In the Donation table, Paid By is the same as whatever you have entered in the Cheque # / Paid By column for the Donation, except that if you have entered a cheque number, it will just show as "Cheque" (or "Check" for US users).

-	In the Receipt table, Type is "Original", "Duplicate", or "Corrected".

Next, in section 2 of this window, you select a range of years to report on. This defaults to reporting on just the current year, which in most cases will be what you want.

Section 3, for the type of totalling to do, is usually greyed out (inactive) because it does not apply. The only time it applies is if you have selected the Sum(Total Amount) and/or Sum(Eligible Amount) columns in the Donation table, and have selected a range of years (not just one year) in section 2. In that case, as soon as you change the years in section 2 it becomes active, and allows you to select whether the Total Amount should be a Grand Total for all years (e.g. just one column showing the total giving during all of those years), or Separate Totals (one column for each year's total).

Once you have made all of your selections, click OK. Certain selections of columns don't actually make sense (or aren't usable for some tricky technical reasons), so it is possible that you will get an error message at this point. If so, read it carefully, and adjust your selections accordingly. If you don't get an error message, you will then be put onto the SQL Select window (titled "Run SQL Select Statement"), with the generated SQL that will create this report displayed. At that point you can use all of the features of that window, including further editing the generated SQL and specifying a Report Title, and then click the Run button to generate the report.

Some of you who have used other report builders may notice that there are several things that are missing from this utility. Among other things, I don't allow you to specify the order of the columns, or conditions (e.g. "only if the Category is General"), or sort orders, or totals (other than Total Amount) or subtotals. (It does automatically total any columns containing dollar figures.) I decided that this window should have those limitations in order to keep it simple enough for everyone to use. I was also taking into account the old 80 / 20 rule, which in this case I would state as "if you provide 80% of the possible features, only 20% of your users will wish you included more".

Also, those who do know a bit of SQL can get some of these effects (changing the column order, conditions and sorts) by editing the SQL in the SQL Select window. It is easy to change the column order – just cut and paste in the list of selected columns, making sure that each column in the SQL ends with a comma, except for the last one. Those who don't know any SQL can use the Sort and Filter buttons on the Reports window after generating the report to impose sort orders or conditions. You actually can't impose all of the conditions with the Filter button that you could by editing the SQL, but you can cover most of the cases. You can also change the column order in the Reports window, if you select the Grid Style for the report on the SQL Select window. (You can drag the column headers around to change their order.)

If you need to enhance the report further, I suggest that you use the Save As button on the Reports window to save the report to a spreadsheet format (e.g. Excel with Headers), and use your spreadsheet program to enhance it.

If you have created a custom report using this utility that you might wish to re-use, you can't save it on the window for this utility. However, once you get to the SQL Select window, you can use the "Save to SQL File" button to save the SQL for this report. Then, by returning to Database à SQL Select, you can use the "Open SQL File" button to retrieve that saved SQL, and run the same report again. This will give exactly the same results as if you re-selected the same options on the Create Custom Report window.