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.
Because this has contribution information it is protected by the Finance role.
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:
- 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
OrgGivingas 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.
--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
Modify image link with secure protocol.