<< Click to Display Table of Contents >> Navigation:  USING REPORTS > Automated Report Running Scripts

Some users may have requirements to run a set of reports regularly, and do various things with them, such as print them, save them to specific formats, and/or email them. The Reports ⇒ Run Reports Script menu option gives you a way to do that.

 

The basic idea is that you write a fairly simple script in a text file, that you can create with a program like Notepad, with instructions for which reports you want to run, and what actions you want to do with them. The list of what we call "commands" that you can include in a script, which will be explained in detail below, are:

 

•RunReport

•RunMemorizedReport

•FilterReport

•SortReport

•PrintReport

•SaveReport

•EmailReport

 

A script could even be used just to save a few manual steps, for instance running one report, saving it to a desired format, and emailing it.

 

Note: For users entering the program with a Donors Only password or login, they can only run reports that don't show any donation data, so only those same reports will be available for them to successfully run in reports scripts.

 

Creating Your Script

 

One easy way to create a script is to use the program's Tools ⇒ Explore Data Directory menu option, to open a File Explorer window in the program's data directory. Once you are in that window, right-click in any blank area on the right-hand side of the window, and pick New from the popup menu, and then Text Document. That will create the file, with an initial name highlighted (up to ".txt") - just overtype that with what you want to name the file (such as ReportScript) and press Enter to save it. Be sure to leave in the required ".txt" extension in the name. Then you can right-click on your new file, and pick Edit from the popup menu to open it in your computer's default text file editor - usually the Notepad program.

 

For future edits of the same file, use Tools ⇒ Explore Data Directory again, find the file in the list there, right-click on it, and pick Edit.

 

Basic File Format

 

Each command must be on one line. In Notepad, either lines will scroll to the right if you type beyond the end of the screen, or they will wrap and be displayed on the next line, depending on whether there is a checkmark beside the sub-menu option Format ⇒ Word Wrap. Either way is fine, but be sure not to put an actual line break within one command's line, by pressing Enter, or your script won't work.

 

Each line is composed of one of the commands listed above (and explained in detail below), followed by parentheses (round brackets) and zero or more arguments within those parentheses, separated by commas, giving details of what to do. Arguments consist of a name of the argument, an equals sign, and a value. Here's an example:

 

RunMemorizedReport(Name=My Saved Report,DateRange=This Year)

 

Note that depending on the width of the window you are viewing this Help in, that command may show as being on one line, or wrapped onto two lines. That is just like in Notepad, if Word Wrap is turned on. But you have to make sure that each command is really only on one line!

 

You can include spaces around the various "punctuation" in the command line - the parentheses, equals signs, and commas - it is ignored. So the previous line could equally have been:

 

RunMemorizedReport ( Name = My Saved Report , DateRange = This Year )

 

One critical thing, however, is that if arguments themselves include commas, either the entire argument including its name, or just the part after the equals sign, must be in double-quotes. That will prevent the program from thinking that the comma indicates the start of the next argument! So if you have a memorized report named "Saved Report, Special Sort" the command to run it could be either of the following:

 

RunMemorizedReport(Name="Saved Report, Special Sort", DateRange=This Year)

 

or

 

RunMemorizedReport("Name=Saved Report, Special Sort", DateRange=This Year)

 

In the very unlikely case where the report name also has a double quote within it, and you are enclosing the entire name, equals sign and value in one set of double quotes, that double quote in the middle must be changed to be two double quotes.

 

Almost nothing in a reports script is case-sensitive, so you can use upper case, lower case, or mixed case (as we will use in most examples). Virtually the only exception to that is Filter and Sort argument values - which you will be able to copy out of specified places in the program, with the desired case.

 

For commands with multiple named arguments within the parentheses, the order that those arguments appear in is not important.

 

You can put "comments" on lines by having the first characters in the line be "//". You could use that either to explain what you are doing in a long complex script, or to eliminate a line that currently isn't working. Two examples (with the 2nd being a line you have temporarily turned into a comment because it's not finished yet):

 

// this line is a comment that will be ignored
// RunReport(Name=not sure what the name is, DateRange=not sure when I want to run this for yet!)

 

Entirely blank lines are also ignored.

 

There's one more special line you can use. When the program sees this line, it just stops reading your script file, so all further lines are ignored. You might use it while you were working on a long script, if only the initial part of your script so far was working, and you wanted to run the working lines at the top, and not bother adding "//" at the start of all of the following lines to make them be ignored. The special line is:

 

STOP

 

Individual Command Formats

 

RunReport

 

The Name argument that you will use for any report in the RunReport command must be taken from what you see in Reports ⇒ Report Browser. Just separate each element that you see as you drill down in that window to select a report with a sort of fake arrow, "->" (a minus sign followed immediately by a greater than sign). After the Name argument, you can include arguments for whatever that report prompts for, such as a range of dates, a donation category, etc. So here's an example:

 

RunReport(Name=Donor Reports -> Donation Info -> Sort by Amount, DateRange=This Year, Category=General)

 

The names of the specific arguments required for each report in the program are in the various Help topics for the Reports menu's sub-menus, starting here. We have tried to keep them as similar as possible to what you see on the windows prompting for the options for each report, within limits.

 

When you are running reports through scripts, cases where a report does not display any rows of data, such as because there are no donations during a date range specified for the report, are not considered to be an error and do not stop the running of your script. In most cases, they will generate a report with just the headings, though for some reports, they could generate a completely empty page.

 

RunMemorizedReport

 

This is very similar to RunReport, except its Name argument is exactly what you see in the drop-down list in Reports ⇒ Memorized Reports.

 

There are two types of memorized reports:

 

1.Ones created by running a standard report, adding a sort or filter, then memorizing it, and

2.Ones created with Reports ⇒ Custom Report (or Database ⇒ SQL Select) and then memorized.

 

Memorized reports can also prompt for arguments, either the same arguments as the standard report they were memorized from, or the arguments prompted for by the custom report that were memorized, which will be based on in the Selection Criteria radio buttons that were chosen in the Custom Reports window. See here for more details on the arguments for Custom Reports.

 

When you are running memorized reports through scripts, cases where a report does not display any rows of data, such as because there are no donations during a date range specified for the report, are not considered to be an error and do not stop the running of your script. In most cases, they will generate a report with just the headings, though for some reports, they could generate a completely empty page.

 

Date Ranges for Reports

 

Date ranges that many reports prompt for can be specified in one of two ways, with the DateRange argument, with various possible named values, like "This Year" as shown in the RunReport example above, or with StartDate and EndDate arguments, with specific dates for their values. The specific dates should be entered in your normal data-entry date format, as seen in donations on the main window. For instance, if your computer's dates are in the format MM/DD/YYYY, if you wanted to run that same report above for all of 2021, you could also use:

 

RunReport(Name=Donor Reports -> Donation Info -> Sort by Amount, StartDate=01/01/2021, EndDate=12/31/2021, Category=General)

 

The date format YYYY-MM-DD is also always accepted.

 

In general, though, as long as there is a named DateRange argument that works for what you need, using that is preferable, because you won't have to keep editing the script to set the desired specific StartDate and EndDate, for instance when you are in the next year.

 

For reports that prompt for just a single date, the Date argument is used instead. It can be a named date (like "Today") or a specific date entered in your standard data entry format.

 

The following table lists and explains the available named DateRange arguments:

 

 

See below for details to help you understand the ones with "Fiscal" in their name.

 

The following table lists the available Date arguments for individual dates. They can also be used as the argument values for DateRange, if you want a report that prompts for a range of dates to include data from just a single date:

 

 

Fiscal Years

 

There are actually two cases for fiscal years in DONATION.

 

1.Users in Canada or the U.S.A. always have their working year in the program being a calendar year, but can set a fiscal year in Maintenance ⇒ Organization Info. For those users, there is a FISCAL YEAR REPORTS section in the Report Browser (and Fiscal Year sub-menu in the main Reports menu). It would only be for those Fiscal Year reports that the DateRange and Date arguments that include the word "Fiscal" would normally be appropriate.
 
It's important to note that "This Fiscal Year" means the fiscal year which ends on a day in the current year, which may not include today's date! See Fiscal Years in DONATION for more details. Because of this, some of the named date ranges and named dates may not work if you are scripting Fiscal Year reports, and today's date is after the end of what the program considers to be the current fiscal year, because they will calculate out to date ranges that are not allowed.
 

2.Users in some other countries (we are specifically aware of Australia, New Zealand and England) have data-entry and receipting years different from the calendar years. In those countries, regular reports work on their fiscal years, and there are no special Fiscal Year reports listed. In those cases, the regular named DateRange and Date arguments should be used, not the ones including the word "Fiscal", and they will automatically calculate the correct dates. See Using Fiscal Year Data Entry and Receipting for more details.

 

Categories

 

Many reports also prompt for a donation category, and thus would have a Category= argument in their RunReport or RunMemorizedReport script line. That can be whatever is on the Category drop-down list seen when you run those reports, namely "All", or any of your donation categories.

 

PrintReport

 

This command is simple, just:

 

PrintReport()

 

It always prints to your default printer, with no prompting. You must have done a RunReport or RunMemorizedReport before you can do a PrintReport.

 

FilterReport

 

This allows you to add a Filter to the previous report that has been run with RunReport or RunMemorizedReport. In many cases this would not be necessary, because you could have instead memorized a report that already had your desired filter added, and used that.

 

Some cases where you would use this command would be when you don't want to bother memorizing a standard report, that you want to use in a script with a filter added, or cases when you want to run one report, then filter it in several different ways and then do things with those multiple filtered reports (like print them, save them, or email them).

 
The format is:

 

FilterReport(Criteria=...)

 

What you fill in to replace the "..." is the technical filter expression that you can see in the Simple Filter window's Output Filter Criteria area. You can also click the Advanced button in the Simple Filter window to get to the Advanced Filter window. In that window, the criteria are shown in the Filter Criteria editing area, and you can copy and paste out of there into your script.

 

Here's a simple example, for use following RunReport(Name=Donor Reports -> Addresses). That report actually has a number of hidden fields that you can filter on, including the two donor Category fields, and the 6 Other Info fields. Suppose your Donor Category 1 has values including "Member" and you want to restrict that report to only the members. Use:

 

FilterReport(Criteria=(category1 = 'Member'))

 

In this example, although the Advanced Filter window that we copied the Criteria value out of put parentheses around the criteria that were pasted into this script line, they aren't necessary, so the following would also work:

 

FilterReport(Criteria=category1 = 'Member')

 

You will note that it's OK to have another equals sign in the value follow "Criteria=".

 

Here's a more complicated example, for use following RunReport(Name=Donor Reports -> Addresses):

 

FilterReport(Criteria=(addr2 like '%Toronto%') Or (addr3 like '%Toronto%'))

 

That restricts the report to only people whose Address Line 2 or Address Line 3 contains "Toronto" within them.

 

SortReport

 

Like FilterReport, this will often not be necessary, because you could have a memorized report that already had your desired sort added, and used that.

 

If you do need to use this for some reason, you will need to determine a technical sort expression to use in the script command.

 

To do that, first run the report normally, and use the Sort button and sort window to specify your desired sort order. Then click the Details ... button in that window. That will display the internal technical sort expression used in the program, which is also used in the script command. It also copies it into the Windows clipboard, so you can then paste it into a script that you are editing, with Ctrl+V.

 

To repeat the example from the sort window Help topic, suppose we are running the Reports ⇒ Donation ⇒ All Donations ⇒ Sort by Name report, using RunReport(Name=Donation Reports -> All Donations -> Sort by Name, DateRange=... Category=...) and we want to change it to be sorted by Amount. Make that change in the sort widow as described in that Help topic, then in that window click Details ..., which will put the desired sort order into the clipboard, allowing you to complete this script line:

 

SortReport(Order="amount A, reversed_name A, date_received A, category A")

 

SaveReport

 

This command allows you to save the report to one of several file formats. The general form of it is:

 

SaveReport(Type=..., Filename=...)

 

The Type can be:

 

•PDF: probably the most common option, creates a formatted PDF file, just like the reports window's Save PDF button. Filenames used with this must end in ".pdf".

•Excel: saves to formatted Excel, as can be done with the reports window's Save As button. Filenames used with this must end in ".xls".

•Text: the report's unformatted data, in a tab-separated text file, as can be done with the reports window's Save As button. Filenames used with this must end in ".txt".

•CSV: the report's unformatted data, in a comma-separated value text file, as can be done with the reports window's Save As button. Filenames used with this must end in ".csv".

 

The files are always saved into the Export subdirectory of your program's Data Directory. As a result, the Filename argument must be just that: only a file name, with no drive or path (directory) component. So here's an example
 

SaveReport(Type=PDF, Filename=MyReport.pdf)

 

A SaveReport command with Type=PDF has three additional optional arguments:

 

1.Password: If you provide this, users trying to open the saved file will have to provide exactly that password (case-sensitive) in order to successfully open it. Here's an example:
 
SaveReport(Type=PDF, Filename=MyReport.pdf, Password=OpenSesame)
 

2.Open: If you include Open=Y, the PDF will be opened in your usual PDF-viewing application (likely Adobe Acrobat Reader). That program will actually cover DONATION until you close it. Example:
 
SaveReport(Type=PDF, Filename=MyReport.pdf, Open=Y)
 
Our thought is that this argument would usually only be used when you were testing a script, to see the results of running the report you are working on. You would likely remove it before you start using the script regularly, since you would just want the actions in the commands to happen, with no need to see any PDFs yourself.
 
Warning: Don't include a 2nd SaveReport commands that saves to the same Filename when a report has already been saved to that same file with Open=Y in the same script. The PDF-viewing application will "lock" the file once the first one has been opened and displayed in it, and the 2nd save will then not succeed. The program can't necessarily detect this well, so the messaging when this happens may be confusing.
 

3.Append: If you include Append=Y, and the PDF file in the Data Directory's Export subdirectory named by the value of the Filename argument already exists, the PDF from the last-saved report will be appended to (added to the end of) that existing PDF.
 
Warning: It only makes sense to use this in a script that has multiple SaveReport command with Type=PDF to the same Filename value. Be sure to omit Append, or use Append=N, in the first SaveReport command for that filename, or it might be appending to an existing file from a previous run of the same reports script! Also be sure not to also use Open=Y with this, or only use it on the very last SaveReport with that filename, or the previous ones will fail, as mentioned above in the section for Open.
 
Warning about Passwords: If you are using the Password argument, both the earlier file with this Filename value and this file with the same Filename value and Append=Y must have the same password, or you will get errors, and the appending will not work.

 

EmailReport

 

This command allows you to email a report saved with SaveReport to anyone you wish. You have to have first successfully configured emailing in the program, with the Maintenance ⇒ Email Sending Configuration window, before you can use this command in a script.

 

If you have not included a SaveReport command in your script, following the last RunReport or RunMemorizedReport, before an EmailReport command appears, it will not be allowed. That's because there is nothing saved from that last report you ran that can be emailed! The last file saved with SaveReport is always attached to the email.

 

The format of this command is one of two options:

 

EmailReport(toName=..., toEmail=..., Subject=..., Body=...)

 

or

 

EmailReport(toName=..., toEmail=..., Subject=..., BodyFilename=...)

 

The first 3 arguments should be quite obvious - the name and email address of the person to send the last-saved report to, and the Subject line for the email.

 

You can use the Body argument to include a short message within the script line, that will be the body of the email. You can actually include newlines within that argument, by using the HTML code "<br>" or "<BR>". To have a blank line between two parts of your message, use two of them, like this:

 
EmailReport(toName=Robbie Recipient, toEmail=robbie@recipients.com, Subject=Your Special Report, Body="Here is your special report.<br><br>Sincerely,<br>John Smith")

 

The body of the resulting email would be like this:

 

Here is your special report.
 
Sincerely,
John Smith

 

For longer email bodies, write them in a text file, with a filename ending in ".txt", which you can do with Notepad just like how you created your script file. Then instead of the Body argument, use BodyFilename to give the full drive, path and filename to find that file. (This is unlike the Filename argument to SaveReport, which is just the actual name.) Here's an example:

 
EmailReport(toName=Robbie Recipient, toEmail=robbie@recipients.com, Subject=Your Special Report,
BodyFilename="C:\users\dan\documents\MyEmailBody.txt")

 

There are two more optional arguments you can use with EmailReport: CCyourself and BCCyourself. Both have values Y or N, and if you set them to Y, the email that is sent will be Cc'd or Bcc'd to your email address as specified in the Email Sending Configuration window. (There is no need to ever include those arguments with the value N, since that is the same as not including those arguments.)

 

The emails are always sent with the From name and email address that are specified in the Email Sending Configuration window. One exception to that is if you have checked the checkbox for "Email receipts, letters and statements only to yourself for testing" in the Receipt Options window. In that case, the ToEmail is always set to be the same as the From email address from the Email Sending Configuration window, and any settings of CCyourself or BCCyourself are ignored (so you don't get multiple copies!).

 

Running your Script

 

To run the script once you have written it, use Reports ⇒ Run Reports Script. That will bring up a window such as the following:

 

 

You can type in a full path to your script file in the entry field, or use the Browse button to bring up a File Open dialog in which you can find and select it.

 

Once you have clicked OK in this window once, the name of the script file is memorized and re-displayed for you the next time you use the window.

 

The script file must have a name ending in ".txt".

 

When you click OK, first the script will be read, and if there are any errors in its formatting etc., the first such error will be displayed to you, and the processing will stop. You will want to switch back and forth between this window and your text editor program in which you are editing the script (usually Notepad), for instance by clicking on the appropriate icon in the Windows Taskbar at the bottom of your screen, to fix errors and keep trying until it works.

 

Once there are no script formatting errors, the program will attempt to follow the commands in the script, one by one. There can be additional errors found at this point, for instance missing or incorrect arguments to RunReport or RunMemorizedReport, etc. Again, the processing will stop at the first such error.

 

As mentioned above, reports that do not display any data, for instance because there is no matching information within a date range that you specify, are not considered to be errors, and will not pop up messages. In most cases, they will generate a report with just the headings, though for some reports, they could generate a completely empty page.

 

Please read any error messages that come up carefully, and try to understand them. We have tried to make them as clear as possible to help you fix things without needing to wait for help from us!

 

You will probably want to build your scripts up slowly, one line at a time, to make sure that each line works, before you go on to the next line.

 

If a whole script runs successfully, you will get a message to that effect.

 

During the running of the script, you will see the reports window displayed, with any report that has been run showing on it. When the script is finished and you have clicked OK on any messages that came up (either errors, or the message saying it finished successfully), the reports window will close.

 

The window above that prompts for your filename only closes if you click Cancel, or you click OK and everything is successful.

 

Some Complete Examples

 

For a first example, suppose after every month-end you want to quickly just print off both Reports ⇒ Donation ⇒ Summary by Category and Reports ⇒ Donation ⇒ Summary by Paid By for the preceding month. Remembering that we have to use the naming from the Report Browser (so instead of "Donation" it is "Donation Reports"), our script could be:

 

RunReport(Name=Donation Reports -> Summary by Category, DateRange=Last Month)
PrintReport()
RunReport(Name=Donation Reports -> Summary by Paid By, DateRange=Last Month)
PrintReport()

 

Now let's do one that's a bit more complicated.

 

Suppose you have a Custom Report for reporting on one day's donations, with columns for the Name, Category 1 (which is whether they are a Member etc.), donation Category and Sum(Total Amount), so you can see the totals by category for that date, broken out by the donor Category 1 values. That report would prompt for just a date. It is a memorized report named "Special Daily Totals".

 

Let's also assume that you have given the name "Membership" to the donor Category 1 field, with an option on the Donor Details tab of Maintenance ⇒ Main Window Options, so that is the name it will appear with on the report, and the name you would use for filtering.

 

It's for a church's Sunday collection, and you might run it on that same day, or maybe within the next few days.

 

You want to email versions of that report, for one Category 1 value each, to different people. Here's your script:

 

RunMemorizedReport(Name=Special Daily Totals, Date=Sunday)
FilterReport(Criteria=membership = 'Member')
SaveReport(Type=PDF, Filename="MembersDaily.pdf")
EmailReport(toName=Mary Membership, toEmail=marymembership@yourorg.com, Subject=Sunday's Membership Totals, Body="Here is your report of totals by category for only Members, Mary")
FilterReport(Criteria=membership = 'Non-Member')
SaveReport(Type=PDF, Filename="NonMembersDaily.pdf")
EmailReport(toName=Norman Nonmember, toEmail=normnon@yourorg.com, Subject=Sunday's Non-Membership Totals, Body="Here is your report of totals by category for only Non-Members, Norman")
// you could include more filters for different membership values here and more commands to email them to people

 

As always, make sure that each command is really only on one line, not broken up as the longer lines above might appear to be!

 

---

This topic was last edited on Feb 17, 2023