How do I create a Custom Orders report?

In addition to viewing reports for each service (Donations, Event Registrar, Pledge-a-thon, Shopping, Membership), you can now create your own Custom Orders reports.

The Custom Orders report provides a break down by date of each transaction processed across one or more GiftTool services. You can also include filters to limit the report to one or more services, activities, items purchased, etc.

This report is more detailed than the Journal report providing additional information about the order (items purchased, tax receipt numbers, member IDs, etc) and can accommodate filters to restrict the data to specific parameters.

Create as many Custom Orders reports as required, useful for data migration into other systems. Alternatively, generate specific Raiser's Edge import files to migrate data into Raiser's Edge.

To create a Custom Orders report:

1. Click the Account link located in left navigation of your GiftTool BackOffice account.
2. Click Custom Report.
3. To create a new report, click Add Custom Report.
4. On page 1:
   • Select a date range for your report by clicking the Date Picker icons.
     • Set an end date of 3000-01-01 so that the report will always include the most recent data, without having to update the end data each time the report is downloaded.
   • Enter the Report Name.
   • Select the currency of the transactions to be displayed.
   • Net Orders: Check the box to have your report provide the summary of all order modifications or order cancellations to the original order. Net Orders will show a single line for the registrant with a final summary of their order. Selecting Net Orders is useful to view the final Balance Due owed by your registrants. Alternatively, unchecking Net Orders, will provide a list of each individual order processed for this participant, including the original order and any subsequent order modifications or cancellations.
   • One row per order: Check the box to have all information for an order displayed on a single line, with Registration Options, Session Options, Additional Items, or answers to questions grouped in a single cell for each item type. Unchecking this box will place any additional Registration Options, Session Options, Additional Items, or answers to questions in a new row.
   • For GiftTool Services: Check the services to be included in the report.
   • For Also Include: Check the optional headers and columns to be displayed (e.g. Report header, Items purchased, Taxes, Balance due, Tax Receipt numbers, UK GiftAid, In Tribute fields, and Profile fields). You can always disable these once you have seen the report.
   • Click Next
5. On page 2, determine which fields should be displayed in each column:
   • Drag & Drop columns from the "Don't show columns" list to the "Show columns" list or back to control what columns are shown.
   • Drag & Drop columns up and down within the "Show columns" list to control the order in which they appear in the report.
   • Tip: Try moving all the fields into the “Show Columns”. Once you have seen the report, remove unnecessary columns as required by dragging them back to the "Don't show columns" list.
   • Click Next
6. [Optional] On page 3, you can specify optional filters to eliminate unwanted records. Having filters refines what information is included in your report, and can be as simple or as complex as you need them to be.
   • Choose a Field from the first drop-down list.
   • Choose an Operator from the second drop-down list.
   • In the third field, enter the value to match. For additional tips on entering filter values, see examples below.
   • Repeat to add more filters.
7. Example 1. Suppose you would like to see a report for all the orders from California. Here is how you enter this into the system below...
   

   	Condition	Field	Operator	Value
   	1	State	equals	CA

8. Example 2. Now suppose you would like to see a report for all the orders from California that are attending a specific event. Just add a second condition.
   

   	Condition	Field	Operator	Value	Logic
   	1	State	equals	CA	AND
   	2	Activity	equals	29th Fundraising Gala

   By default, the word “AND” appears in the Logic column on the right hand side. This means the report has been narrowed, so that each line has to comply with all of the filters. So in Example 2, only the orders that both come from California and have registered to attend the 29th Fundraising Gala show up on the report.
   You can override the “AND” logic by specifying your own logic. Here's how...
9. Example 3. If you would like to see a report for all orders from California, New York and Canada combined. Enter in your conditions...

   	Condition	Field	Operator	Value
   	1	State	equals	CA
   	2	State	equals	NY
   	3	Country	equals	Canada

   Then on the Advanced Logic line below, link the conditions with the word “OR”. This means that an order has to comply with only one of the conditions to show up on the report. Entering information into this line automatically overrides the AND logic in the right hand column.
   

   1 OR 2 OR 3

   If you have a complex set of conditions and you want to combine “AND” and “OR” logic, see Advanced Filter Logic for more examples.
10. Example 4. One-Time Donations only
11. Example 5. Recurring Donations only
12. Example 6. Event Registrar - Registrants with Balance Due
13. Example 7. Event Registrar - Single Registration or Session Option
14. Example 8. Event Registrar - List of Donors across multiple events
15. Example 9. Pledge-a-thon - Offline Donations
16. Example 10. Pledge-a-thon - Participants only
17. Each filter line must contain a field, operator, and value.
18. When entering values, please note:
    • Values are case sensitive.
    • Be careful about adding unwanted extra spaces before or after the value.
    • When typing the value for an Activity, Item Name or Sponsorship Details, you must type the exact name as spelled in your setup. Tip: Run the report without this filter, to see which name should be filter for. Then copy/paste the exact value into your filter.
    • Examples of some field values:
      • Currency: CAD, USD, AUD, HKD, GBP, EUR
      • Service: Registrar, Pledge-a-thon, Donation, Membership, Shop
      • Customer: Registrant, Participant, Sponsor, Pledger, Donor, Member, Shopper
      • Order Status: Paid, Ordered, Reversed, Cancelled, Imported
    • For Order ID fields, the prefix letter indicating the service used (R, S, D, etc) before the ID cannot be filtered on. Instead filter on the Service field (Registrar, Shop, Donations, etc).
    • Date values must be in the form YYYY-MM-DD.
19. See advanced filter logic for additional rules & examples.
20. Click Save to save your report, or Save & Runto download the saved report.
    • The Custom Orders Report will list each order separately.
      • If Net Orders is checked on the first page of the report setup, each order will be the summary of the original and any subsequent order modifications or order cancellations. Viewing Net Orders is useful to view the final Balance Due owed by your registrants.
      • If Net Orders is unchecked on the first page of the report setup,  any modification/cancellation orders will be listed on a separate row.
    • When viewing your report, some of the cell may have additional content that is hidden. To display any hidden text, use the Format Cell functions wrap text and auto fit row height within Excel.
    • Use your computer functions, to print or save a copy of the report.
    • Having difficulty viewing Excel reports?
    • How do I hide, cut or move columns in an Excel spreadsheet?
    • How do I remove the questions preceding the answers in Excel?
    • How do I split the content of a cell in my Excel spreadsheet into multiple cells?

Note: A Report API (Application Programming Interface) is automatically generated for each Custom Orders report, allowing you to automatically pull data into your own application or other database.