home

MinistryPlatform

RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailMinistryPlatform

View Examples

Top 50 Donors

This View displays the 50 Donors who have given the most.

Difficulty: Advanced

Purpose: To Identify Donors who have given the most.

Page: Donors

To create a View that lists all the top Donors, the following SQL may be used for the View Clause (filter criteria). This does not account for which Program or time-frame (we'll expand on this for later examples). It uses the Configuration Settings to omit anonymous Donors:

Donors.Donor_ID IN (  
SELECT TOP 50 D.Donor_ID FROM Donations D   
WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value)     
FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1     
AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID')     
AND CS.Application_Code = 'COMMON'     
AND CS.Domain_ID = D.Domain_ID)   
GROUP BY D.Donor_ID  
ORDER BY SUM(D.Donation_Amount) DESC)
Tip: For immediate use, open Advanced Search on the DOnors page and paste the SQL in the criteria. Leave fields empty.

Variations

To change the number of Donors displayed, you can change the integer in the TOP statement:

SELECT TOP 100 D.Donor_ID FROM Donations D

Additional Fields

This View initially uses the fields from the Donors page. To add fields, you may want to copy the fields from the page and add the following fields:

Total Donations

For a total donations field, you can add this field. Be sure to add a comma to separate this from existing fields:

(SELECT SUM(D.Donation_Amount) FROM Donations D WHERE D.Donor_ID = Donors.Donor_ID) AS Donation_Total

If you include the total field, adding a sort order is a nice touch:

Donation_Total DESC

Filtered by Date Range

To alter this View to use Donations for a particular time period, you can compare the Donation_Date. If you include the "Total Donations" field, it should include the same calculation for date as the View Clause.

View Clause - You can use various date filters. For our example, we are using Donations in the prior calendar year:

Donors.Donor_ID IN (  SELECT TOP 50 D.Donor_ID FROM Donations D   WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value)       FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1       AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID')       AND CS.Application_Code = 'COMMON'       AND CS.Domain_ID = D.Domain_ID)    AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1   GROUP BY D.Donor_ID   ORDER BY SUM(D.Donation_Amount) DESC)

Total Field - The filter for date must match the View filter for date.

(SELECT SUM(D.Donation_Amount) FROM Donations D   
WHERE D.Donor_ID = Donors.Donor_ID    
AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) 
AS Donation_Total

Filtered by Program

To alter this View to show giving for a Program, a join must be added for Donation Distribution and the amount calculated from the Distribution rather than the Donation:

View Clause - You can include the Distribution that determines the Program:

Donors.Donor_ID IN (  SELECT TOP 50 D.Donor_ID FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value)       FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1       AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID')       AND CS.Application_Code = 'COMMON'       AND CS.Domain_ID = D.Domain_ID)    AND DD.Program_ID = 3     AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1   GROUP BY D.Donor_ID   ORDER BY SUM(DD.Amount) DESC)

Total Field - The Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   WHERE D.Donor_ID = Donors.Donor_ID     AND DD.Program_ID = 3     AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total

Filtered Statement Type

To alter this View to show giving for a Statement Type (such as Tithes), a join must be added for Program.

View Clause - You can use a join to include the Program that determines the Statement Type, and filter for the Statement Type. Program ID is omitted:

Donors.Donor_ID IN (  SELECT TOP 50 D.Donor_ID FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID   WHERE D.Donor_ID NOT IN (SELECT CONVERT(int,CS.Value)       FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1       AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID')       AND CS.Application_Code = 'COMMON'       AND CS.Domain_ID = D.Domain_ID)    AND Prog.Statement_Header_ID = 1      AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1   GROUP BY D.Donor_ID   ORDER BY SUM(DD.Amount) DESC)

Total Field - The Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID   WHERE D.Donor_ID = Donors.Donor_ID    AND Prog.Statement_Header_ID = 1     AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total

Filter for Soft Credit

You can change the code to consider Soft Credit Donations in your calculations:

Donors.Donor_ID IN (  SELECT TOP 50 ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID   WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID)  NOT IN (SELECT CONVERT(int,CS.Value)       FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1       AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID')       AND CS.Application_Code = 'COMMON'       AND CS.Domain_ID = D.Domain_ID)    AND Prog.Statement_Header_ID = 1     AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1   GROUP BY ISNULL(DD.Soft_Credit_Donor,D.Donor_ID)   ORDER BY SUM(DD.Amount) DESC)

Total Field - The Field filter must match the View filter:

(SELECT SUM(DD.Amount) FROM Donations D   JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID   JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID   WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) = Donors.Donor_ID   AND Prog.Statement_Header_ID = 1     AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total

Techniques

The following SQL Functions were used in this example:

  • Configuration Settings
  • ID IN SELECT
  • SELECT TOP N
  • SUM
  • GETDATE
  • EXISTS
  • ISNULL
MinistryPlatform

Last updated: August 1, 2024