Using out-of-the-box queries: Accounting and Finance
The following are out-of-the-box queries related to accounting and finance, organized by category. Use these predefined queries to easily retrieve financial data.
| Query | Purpose | Location in Document System | Location in iMIS |
|---|---|---|---|
| BATCHES | |||
| Batch detail | Displays transaction details for a selected batch, such as transaction number, bill to ID, customer, category, description, and amount. | $/Accounting/DefaultSystem/ Queries/Batches/Batch Details |
|
| Batch detail Search | Displays transaction details for a selected batch, such as transaction number, bill to ID, customer, category, description, and amount. Allow user to filter by summary columns. | ||
| By payment method | Displays transaction summaries for a selected batch, summarized by payment method. | ||
| BatchInfo | Displays selected batch information, such as batch number, description, date, control amount, actual amount, created by, and status. | $/Accounting/DefaultSystem/ Queries/Batches/Batch Info |
|
| Open batches | Displays a list of open batches from which you can select batch details. | $/Accounting/DefaultSystem/ Queries/Batches/Batch Search |
Batches search |
| Open recurring donation batches | Displays a list of open batches from which you can select batch details. | ||
| Ready batches | Displays a list of ready batches from which you can select batch details. | ||
| Search all batches | Displays search filters that allow users to search all batches. You can select from the retrieved batches to view batch details. | ||
| Search transactions (past 12 months) | Allow user to find a batch by searching the transactions within the past 12 months for a transaction number, bill to ID, customer, or payment method. | ||
| Membership billing batch detail | Displays renewal transaction details for a selected batch, such as bill to customer, bill to ID, ship to customer, product, term dates, and amount. | $/Accounting/DefaultSystem/ Queries/Batches/Renewal Batch Details |
Batch reports window |
| Transaction detail | Displays transaction details for a selected batch, such as transaction number, bill to ID, customer, category, description, and amount. | ||
| Transaction detail search | Displays transaction details for a selected batch, such as transaction number, bill to ID, customer, category, description, and amount. Allow user to filter by summary columns. | ||
| INVOICES | |||
| OpenDuesInvoices | Returns all open dues (membership fee) invoices. | $/Accounting/DefaultSystem/ Queries/OpenDuesInvoices |
Reversing open invoices |
| OpenDuesInvoicesByBillingRun | Returns all open dues (membership fee) invoices by most recent billing run. | ||
| OrganizationProfile - View invoice | Returns the open company invoices for company administrators. | $/ContactManagement/ DefaultSystem/Queries |
Company administrator view of the organization account page |
| Invoice orders | Returns all orders that have been invoiced. | $/Common/Communications/Queries | Used as the recipient query for the Order Invoice communication template. |
| Progress Tracker - Open invoices | Count of all open invoices, displayed on the finance dashboard a Progress Tracker content item. | $/Samples/Dashboards/Finance | Finance dashboard |
| Open invoices | Returns all open invoices. | ||
| Open invoices | Returns all invoices for a specific event that have been generated. | $/Samples/Dashboards/Events/ Specific Events/Invoices |
Specific event dashboard |
| Membership pending invoices | Returns all AutoPay membership invoices that are pending. | $/Samples/Dashboards/ AutoPay/Membership |
AutoPay Membership dashboard |
| Pending fundraising invoices | Returns all AutoPay fundraising invoices that are pending. | $/Samples/Dashboards/ AutoPay/Fundraising |
AutoPay Fundraising dashboard |
| InvoiceLog | Returns a log of all order invoices that have been generated. | $/OrderManagement/DefaultSystem/ Queries/OrderStageProcessingNotifications |
Generating order invoices (Logs tab) |
| Find renewal invoices due for automatic payment TODAY | Returns all AutoPay membership invoices due for payment on or before today's date. | $/Membership/DefaultSystem /Queries/AutoPay |
|
| Find renewal invoices due for automatic payment ALL | Returns all AutoPay membership invoices with an existing balance. | Processing AutoPay membership invoices (Review invoices tab) | |
| Find recurring donation invoices TODAY | Returns all AutoPay fundraising invoices due for payment on or before today's date. | $/Fundraising/DefaultSystem/Queries/AutoPay | |
| Find recurring donation invoices ALL | Returns all AutoPay fundraising invoices with an existing balance. | Processing recurring donations (Review invoices tab) | |
| OpenPledgeInvoices | Returns all open pledge invoices. | $/Accounting/DefaultSystem/Queries | Reversing open pledges |
| Renewal invoices | Returns all renewal invoices, including those with or without a balance. | $/Common/Query Sources/Membership | Common membership query sources |
| Find payments by invoice | Locate a payment using the invoice number. | $/Accounting/DefaultSystem/ Queries/Payments/Payment Search |
Searching for a payment |
| Organization open renewal invoices | Returns results to organization administrators if the organization they administer has open membership renewal invoices. | $/Common/Tasks/Queries | Used to power the Organization administrator renewal due alert |
| Paid Invoices | Displays a list of all paid invoices for the selected contact. | $/ContactManagement/DefaultSystem/ Queries/Recent Activities/Transactions |
Account page Transactions tab under the Invoices section |
| OPEN CREDITS | |||
| CreditInvoices | Returns all open credit invoices. | $/Accounting/DefaultSystem/ Queries/OpenCreditInvoices/CreditInvoices |
|
| Progress Tracker - Open credits | Returns total amount of open credits. | $/Samples/Dashboards/Finance | Finance dashboard |
| Credit balances | Returns credit balances, including the invoice number, due to contact, full address, invoice date, and balance. | ||
| All open credits | Returns all open invoices, including the invoice number, A/R age, due from contact, full address, invoice date, and balance. | ||
| Open credits | Returns open credits per contact. Displays the contact name and open credit amount. | $/Samples/Dashboards/Commerce | Commerce dashboard |
| REVENUE | |||
| ProgressTracker - Revenue from donors | Returns the revenue from moves management donors this year. | $/Samples/Dashboards/ MovesManagement/OverallDashboard/ Progress Trackers |
Moves management dashboard |
| Revenue last 12 months | Returns the membership revenue within the last 12 months. | $/Samples/Dashboards/Membership | Membership dashboard |
| Progress Tracker - Revenue this year | Returns total revenue for this year. | $/Samples/Dashboards/Finance | Finance dashboard |
| Revenue per month | Returns revenue earned per month. | ||
| Progress Tracker - Total Revenue | Returns total revenue generated by a specific event. | $/Samples/Dashboards/ Events/Specific Events |
Event-specific dashboard |
| Revenue by Event This Year | Allows you to search for a specific event to review total revenue for that event. | $/Samples/Dashboards/Events | Event dashboard |
| Progress Tracker - Event Revenue This Year | Returns revenue for all events for this year. | ||
| Revenue by source code | Returns revenue per source code. | $/Samples/Dashboards/Commerce | Commerce dashboard |
| Progress Tracker - Sales Revenue This Year | Returns all product sales revenue for this year. | $/Samples/Dashboards/ Commerce/Product Sales |
|
| Progress Tracker - Sales Revenue Last Year | Returns all product sales revenue for last year. | ||