Creating Custom Reports
|Top Previous Next|
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.
N.B. These options are not available with the free Lite version of DONATION.
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, so it is a much more attractive option for most users!
When you use the Reports à Custom Report utility, the following window comes up:
You will see that in the first section of the window, there are four lists of columns that you can select, from the Donor, Donation, Receipt and Pledge 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 piece of information within a table, such as the Last Name in the Donor table.
N.B. The Pledge fields are not actually in a separate table in the database, but it seemed clearer to group them together into one list for use in Custom Reports. The Pledge list disappears from this window if you have selected not to display the Annual Pledge field on the Donors tab of the Maintenance à Main Window Options window. If the Pledge list is displayed, which columns are listed in it depends on which option you have selected on the Pledge tab of that same main window options window. (Further details are below.)
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 four 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, and other important details:
Next, in the second section of this window, you can 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. (If you select Pledge fields, you cannot change the year - it is always reporting on the current year, or for multi-year pledging, on all years included in each donors' pledge.)
If you leave the From and To years being the current year, and you have selected at least one field from the Donation table, then the five radio buttons (other than "None") at the right in this section will be enabled (otherwise, they are greyed out, so you can't use them). Selecting one of these will cause the report, when run, to prompt for one donation date to report on, a range of donation dates to report on, a range of donation dates and optionally also a category to report on, the fiscal year to report on, or the fiscal year and optionally also a category to report on.
The fiscal year selection options are only available if you have set up a fiscal year different from the calendar year in the Maintenance à Organization Info window. And you can only use them for the current fiscal year. (See Fiscal Years in DONATION for a full explanation of what that means.) If you select the "For the fiscal year" radio button, there will actually be no date selection window displayed when you run the report - it will just automatically use the entire fiscal year. If you select the "For the fiscal year and one category" radio button, the selection window displayed when you run the report will display the fiscal year start and end dates (but not allow you to change them), and allow you to optionally select a donation category to restrict the report to.
The third section of this window, 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 also selected a range of years (not just one year) in the second section. In that case, as soon as you change the years in the second section and Tab or click out of the field, this section 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 and other options 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. It is actually at that point that any prompting for a range of dates and/or categories, based on the radio buttons you selected on this Custom Reports window, will take place.
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 other than those in the second section (e.g. "only if the Total Amount is over $1,000"), or sort orders, or totals (other than Total Amount) or subtotals. (The report builder and SQL Select do 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.
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), and use your spreadsheet program to enhance it.
Re-Using Custom Reports
You can memorize a Custom Report you have created by using the Memorize Report button on the SQL Select window, or on the following window displaying a report, and then replay it with the Reports à Memorized Reports menu option. (If you wait to memorize the report until you are on the window displaying the report, that has the advantage that if you used the Sort or Filter buttons on that window, the Sort and Filter conditions you added are also part of the memorized report.)
There's one other way to memorize a report, but in general it is not as useful as the Memorize Report option. On 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. However, unlike Memorize Report, it doesn't memorize the Report Title, or your Report Style and Orientation options, and since you haven't run the report yet, it can't memorize any Sort or Filter.
Which Pledge-related fields are displayed depends on which options you select on the Pledge tab of the Maintenance à Main Window Options window, as follows:
The Outstanding fields are always the value in the relevant pledge amount field (such as Annual Pledge, Current Pledge or Total Pledge) minus the value in the relevant Pledge Paid field (either Pledge Paid or Total Pledge Paid).
If you have checked the "Pledges are on a fiscal year basis" checkbox on the Pledge tab of the Maintenance à Main Window Options window, and you create a Custom Report including Pledge fields, you may be prompted for which fiscal year you want to report on. For details, see Pledges in DONATION. One consequence of this is that the generated SQL for the report may include the specific dates of your selected fiscal year, which may make that report less appropriate to memorize, since at some point it will no longer be for the dates you want!