Organization Giving¶

This script can be added to the Main Reports Menu. The report itself is a list of the orgs by name, with the Division in a separate column. There are other columns for Member Count, Giver Count, and Percent (of member who are givers).

You will specify in the script which Divisions to include in the report and which Status Flag search to use to find the Givers.

The report will sort first by Division, then by Organization.

Note

Because this has contribution information it is protected by the Finance role.

https://i.tpsdb.com/2017-03-27_13-14-27.png — Sample Report¶

Status Flag¶

Create a Status Flag that will find those you want to consider to be givers. One suggestion for the search would be to use the Condition RecentContributionAmountBothJoint specifying GreaterEqual 1000 dollars for 365 Days. You can use whatever criteria you want in this search. Just be sure to save it as a Status Flag.

Create OrgGiving SQL Report¶

Step 1

Create Status Flag as described above and note the name of the flag. Example: F83

Step 2

Make a note of the Division ID #s for the Divisions you want included in the report. You can find the Division ID#s by going to Administration > Organizations > Divisions.

Step 3

Go to Administration > Setup > Special Content > SQL Scripts and click the green +New Sql Script File and enter OrgGiving as the name of the file and click Submit.

Step 4

Copy the code below and paste it into the new script.

Edit the following lines of code:

Line 22. Enter the name of the appropriate Status Flag between the single quotes.
Line 27. Enter the ID#s for all Divisions to include in the report between the parentheses. Separate with commas, no spaces.

Click the blue Save Sql Script button.

Step 5

Now that you have created the script you need to click the Run Script button at the top of the form.

Then you can select Add Report to Menu which is at the bottom of the report.

Code¶

--Roles=Finance
SELECT
    *,
    FORMAT((tt.GiverCount * 1.0) / NULLIF(tt.MemberCount, 0) * 100, 'N1') Pct
FROM
(
    SELECT
        d.Name Division,
        OrganizationName,
        (SELECT COUNT(*)
            FROM dbo.OrganizationMembers om
            WHERE om.MemberTypeId NOT IN (230,311) -- Not Inactive, Not Prospect
            AND om.OrganizationId = o.OrganizationId
            AND ISNULL(om.Pending, 0) = 0
        ) MemberCount,
        (SELECT COUNT(*)
            FROM dbo.OrganizationMembers om
            JOIN dbo.AllStatusFlags sf ON sf.PeopleId = om.PeopleId
            WHERE om.MemberTypeId NOT IN (230,311)
            AND om.OrganizationId = o.OrganizationId
            AND ISNULL(om.Pending, 0) = 0
            AND sf.Flag = '' -- StatusFlag for Giver
        ) GiverCount
    FROM dbo.Organizations o
    JOIN dbo.Division d ON d.Id = o.DivisionId
    WHERE OrganizationStatusId = 30 -- Active Organization
    AND o.DivisionId IN () -- List of Divisions to include
) tt
ORDER BY tt.Division, tt.OrganizationName

Latest Update

11/13/2020

Modify image link with secure protocol.