Accessing Reports¶

From an individual Involvement

The reports are located under the gear icon of the Blue Toolbar when viewing the Involvement. Reports will contain data for just that one trip.

From the Involvements Search Page

The reports are also available under the cloud icon of the Blue Toolbar on the Involvements > Search page. When accessed this way, the reports contain information for all Mission Trip Involvements included in your filter, with each Involvement having its own section.

Mission Trip Funding Report¶

This online report run from an individual Involvement or the Involvement Search blue toolbar contains the following information:

• Participant’s Name (linked to the person’s record)

• Trip Cost

• Amount Raised (includes the deposit, any payments made by the participant, and any donations made for the participant’s benefit)

• Balance

• If there have been contributions made to the trip that are not designated for a specific participant, there will be a row named Undesignated. See below for a SQL recipe to report on undesignated funds

• A total for each column at the bottom of the grid for each trip.

Mission Trip Sender Export¶

This report run from an individual Involvement or the Involvment Search page opens as an Excel spreadsheet and contains the following information:

• Inv ID

• Inv Name

• Sender (Supporter) ID

• Sender Name

• Goer (Participant) ID

• Goer Name

• Date Given

• Amount

• Notice Sent

The deposits and other amounts paid by the Goer will be included in this export, so they will appear in both the Goer and Sender columns.

Every header has a filter, so you can view data for just one Sender, one Goer, or one Involvement (if generated from Involvements > Search).

Mission Trip Funding Export¶

Along with the two reports from above, the Funding Export is an Excel export available from from the Involvement Search page and contains basically the same information as the Funding Report from above, just in Excel format instead of an online view. The names will not be links to people records. This export is only available from Involvements > Search.

Mission Trip Tracking on the Transactions Log¶

When you click the link to View Goer’s Transactions from the Member Dialog, this opens the Transactions Log showing just the transactions for that individual. The section labeled Trip Balances is the place to find the correct calculated balance for the Goer.

The Transactions Log displays three sections:

1. Goer Transactions — Transactions made by the Goer or any adjustments made to their balance.

2. Supporter Payments — Payments made by any Supporters.

3. Self-Support — Self-support payments by the Goer.

Beneath those sections you will see Trip Balances. This lists any Mission Trips that are still open. The Trip Balance will match the Balance on the Goer’s Member Dialog and is calculated from Goer Transactions, Self-Support, and Supporter Transactions.

Reporting on Undesignated Payments¶

The Funding Report shows undesignated donations as a single Undesignated row, but does not break out the individual transactions. To see the detail of each undesignated payment, you can create a custom SQL report using the query below.

To add this report to your database, go to Admin > Special Content > SQL Scripts and create a new script. Paste the SQL below and replace 6643 with your Mission Trip Involvement ID.

-- General mission trip donations (GoerId IS NULL) with total row
-- Replace 6643 with your mission trip Involvement ID

SELECT
    linkfornext1 AS linkfornext,
    Involvement,
    linkfornext2 AS linkfornext,
    Sender,
    DateGiven,
    Amt,
    NoticeSent
FROM (
    SELECT
        '/Org/' + CAST(gs.OrgId AS VARCHAR) AS linkfornext1,
        o.OrganizationName AS Involvement,
        '/Person2/' + CAST(gs.SupporterId AS VARCHAR) AS linkfornext2,
        s.Name2 AS Sender,
        gs.Created AS DateGiven,
        gs.Amount AS Amt,
        CASE
            WHEN gs.NoNoticeToGoer = 1 THEN 'no notice'
            WHEN gs.NoNoticeToGoer = 0 THEN 'sent'
            ELSE ''
        END AS NoticeSent,
        0 AS IsTotal
    FROM dbo.GoerSenderAmounts gs
    JOIN dbo.Organizations o ON o.OrganizationId = gs.OrgId
    JOIN dbo.People s ON s.PeopleId = gs.SupporterId
    WHERE gs.GoerId IS NULL
    AND gs.OrgId = 6643

    UNION ALL

    SELECT
        NULL AS linkfornext1,
        'TOTAL' AS Involvement,
        NULL AS linkfornext2,
        '' AS Sender,
        NULL AS DateGiven,
        SUM(gs.Amount) AS Amt,
        '' AS NoticeSent,
        1 AS IsTotal
    FROM dbo.GoerSenderAmounts gs
    WHERE gs.GoerId IS NULL
    AND gs.OrgId = 6643
) t
ORDER BY IsTotal, DateGiven DESC;

The report will display the Involvement name (linked), Sender name (linked to their record), date given, amount, and whether the notice was sent. A total row is included at the bottom.

Added Reporting on Undesignated Payments section with custom SQL report for detailed undesignated donation transactions