Creating Custom Reports


Navigation:  USING REPORTS >

   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.


Note: 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, for example 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.


Note: 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.)


If you enter the program with the Donors Only Password, only the Donor list of fields and the buttons at the bottom of the window are displayed - everything else is removed.


Selecting Columns


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 or Donation tables, you will have to scroll down their lists.


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 we hope their meanings will also be obvious. Here are a few that aren't quite as obvious, and other important details:


-In the Donor and Donation tables, fields that are set to not be displayed on the main window (via Maintenance ⇒ Main Window Options) are not displayed in the lists of fields here.
-In the Donor table, whether the first displayed field shows up as "Envelope #" or "Member #" depends on the setting for "Label for Member/Env. #" in the Maintenance Receipt Options window.
-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, for example "Mary Smith", 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 the name in the previous example as "Smith, Mary", but will never display the Business Name.
-In the Donor table, the "# Donors" column allows you to do summary reports, that include a count of donors on each row.
-In the Donation table, "Month # Received" is the number of the month (1 to 12) of the Date Received of the donation.
-In the Donation table, "Quarter # Received" is the number of the quarter of the calendar year (1 to 4) of the Date Received of the donation. So quarter number 1 is January 1 to March 31, quarter 2 is April 1 to June 30, etc.
-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, for example total donation amounts by donor, rather than the detailed reports of donations that you will get if you select other columns from the Donation table but no summary columns.
-In the Donation table, the "# Donations" column allows you to do summary reports, that include a count of donations on each row.
-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". (For Canadian receipts, it can be more complex options, as explained in the Help section Replacement or Correction Receipts for Canada.)
-Details about the Pledge list are in a separate section below.


Ranges of Years and Selection Criteria


In the second section of this window (as long as you have selected at least one column from the Donation table), you can select a range of years to report on, using the From and To drop-down lists of years present in your database. This defaults to reporting on just the current working 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 donation category to report on, the fiscal year to report on, or the fiscal year and optionally also a donation 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.


If you are in a country such as the United Kingdom, Australia or New Zealand, and have selected to use Fiscal Year Data Entry and Receipting, then the years listed in the From and To drop-downs will include fiscal years. If you are working on a year that is a fiscal year, the radio buttons "For the fiscal year" doesn't really do anything, because it would be reporting on the fiscal year anyways. The radio buttons for deleting a date or range of dates work within that working fiscal year.


Donors used in Multiple Year Reports


A report for two years will automatically include any donors in the earlier year which are missing in the later year. However, reports for over two years only include donors that are present in the last of those years. If you want to be sure to have all donors in all of those years included (including ones you might have deleted in later years), you could first use the Database ⇒ Copy Missing Donors menu option to copy all of the donors forward from the first of the years, and all intervening years, to the last of those years. In fact, the program will prompt you about whether you wish to do that, if there are missing donors in the last year, when you run such reports, and if you answer Yes, it will do so automatically without you having to go to that menu option.


Multiple Year Totals


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) and or # Donations 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, this section becomes active, and allows you to select whether those Sum fields or # Donation field that you selected should be a Grand Total Only for all years (for example just one column showing the total or count of donors giving during all of those years), Separate Totals Only (one column for each year's total and/or count), or both Separate and Grand Totals.


Running the Report


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, we don't allow you to specify the order of the columns, or conditions other than those in the second section (for example "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 or counts of donors or donations.) We 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, we suggest that you use the Save As button on the Reports window to save the report to a spreadsheet format (for example 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.


Pledge Fields


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:




Simple Annual Pledge

Annual Pledge, Pledge Paid, Pledge Outstanding

Annual with Start Date and Category

Current Pledge, Pledge Start Date (but only if you have not checked the "Pledges are on a fiscal year basis" checkbox on the Pledge tab of the Maintenance ⇒ Main Window Options window), Pledge Category, Pledge Paid, Pledge Outstanding


Current Pledge (this year's pledged amount), Total Pledge (total of all year's pledged amounts), Pledge Start Date, Pledge Years, Pledge End Date, Pledge Category, Pledge Frequency (payment frequency, such as Annual etc.), Pledge Paid (in the current year), Pledge Outstanding (in the current year), Total Pledge Paid (for all years within the donor's pledge period), Total Pledge Outstanding (for all years)


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!