Custom Contributions Search Export

This export will show up as an option on the Contributions Search page when you click the drop-down menu beside the blue Filter button. This recipe allows you to add additional columns of information to the standard Contribution Export. This specific example adds the following 3 extra values to the export.

  • Addressee

  • Casual Salutation

  • Formal Salutation

You can modify the code below to add/change other columns of information.

Add the Export

Step 1

Go to Administration > Setup > Special Content and select the SQL Scripts tab.

Step 2

Click the green +New Sql Script File button and type CustomExport (or whatever name you choose) as the name of the report.

Step 3

Type Contribution Export in the Content KeyWords field. This is what allows this export to show up as an available export on the Contributions Search page.

Step 4

Copy the code below and paste it into the new file. Click the blue Save Sql Script button.

Code

Note

The code below uses the @contributionIds variable to include only the results from the contribution search.

Lines 67-69 and 72-74 will need to be modified to match existing extra values on your database. Those rows are simply an example of the code necessary to include extra values named Addressee, Casual Salutation, and Formal Salutation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
with contrib as (
    SELECT cc.ContributionId, 
        cc.FundId, 
        cc.ContributionTypeId, 
        cc.PeopleId, 
        cc.ContributionDate, 
        cc.ContributionAmount, 
        cc.ContributionDesc, 
        cc.Notes, 
        cc.ContributionStatusId, 
        cc.PledgeFlag, 
        cc.CheckNo, 
        cc.TranId, 
        (CASE 
            WHEN (
                SELECT ht.Description
                FROM dbo.BundleDetail AS detail
                INNER JOIN dbo.BundleHeader AS h ON h.BundleHeaderId = detail.BundleHeaderId
                INNER JOIN lookup.BundleHeaderTypes AS ht ON ht.Id = h.BundleHeaderTypeId
                WHERE detail.ContributionId = cc.ContributionId
                ) LIKE '%Online%' THEN 
                (CASE 
                    WHEN cc.ContributionDesc = 'Recurring Giving' THEN cc.ContributionDesc
                    ELSE 'Online'
                END)
            ELSE ct.Description
        END) AS ContributionType
    FROM dbo.Contribution AS cc
    JOIN dbo.SplitInts(@contributionIds) ids ON ids.[Value] = cc.ContributionId
    INNER JOIN dbo.ContributionFund AS fund ON cc.FundId = fund.FundId
    INNER JOIN lookup.ContributionType AS ct ON ct.Id = cc.ContributionTypeId
    )
SELECT contrib.PeopleId, 
    (CASE 
        WHEN person.IsBusiness = 1 THEN person.LastName
        ELSE person.Name
    END) AS Name,
    contrib.ContributionDate, 
    contrib.ContributionAmount,
    b.Description AS BundleType, 
    f.FundName AS Fund, 
    camp.Code AS CampusCode, 
    contrib.ContributionDesc AS Description, 
    contrib.Notes, 
    contrib.CheckNo, 
    bh.BundleHeaderId AS BundleId, 
    t.TransactionId AS TranId,
    contrib.ContributionId,
    contrib.ContributionType,
    contrib.ContributionTypeId, 
    cs.Description AS Status, 
    contrib.ContributionStatusId AS StatusId, 
    contrib.PledgeFlag AS Pledge, 
    (CASE 
        WHEN (contrib.ContributionTypeId = 9) OR ((COALESCE(f.NonTaxDeductible,0)) = 1) THEN 1
        WHEN NOT ((contrib.ContributionTypeId = 9) OR ((COALESCE(f.NonTaxDeductible,0)) = 1)) THEN 0
        ELSE NULL
    END) AS NonTaxDed,
    person.FamilyId AS FamilyId, 
    ms.Description AS MemberStatus, 
    person.JoinDate, 
    person.PrimaryAddress AS Address, 
    person.PrimaryAddress2 AS Address2, 
    person.PrimaryCity AS City, 
    person.PrimaryState AS State, 
    person.PrimaryZip AS Zip,
    ac.Data AS Addressee,
    sa.Data AS [Casual Salutation],
    fs.Data AS [Formal Salutation]
FROM contrib
LEFT OUTER JOIN dbo.People AS person ON person.PeopleId = contrib.PeopleId
LEFT OUTER JOIN dbo.PeopleExtra ac ON ac.PeopleId = contrib.PeopleId and ac.Field = 'Addressee'
LEFT OUTER JOIN dbo.PeopleExtra sa ON sa.PeopleId = contrib.PeopleId and sa.Field = 'Casual Salutation'
LEFT OUTER JOIN dbo.PeopleExtra fs ON fs.PeopleId = contrib.PeopleId and fs.Field = 'Formal Salutation'
INNER JOIN dbo.ContributionFund AS f ON f.FundId = contrib.FundId
LEFT OUTER JOIN lookup.Campus AS camp ON camp.Id = person.CampusId
LEFT OUTER JOIN lookup.ContributionStatus AS cs ON cs.Id = contrib.ContributionStatusId
LEFT OUTER JOIN lookup.MemberStatus AS ms ON ms.Id = person.MemberStatusId
LEFT OUTER JOIN dbo.BundleDetail bd on bd.ContributionId = contrib.ContributionId
LEFT OUTER JOIN dbo.BundleHeader bh on bh.BundleHeaderId = bd.BundleHeaderId
LEFT OUTER JOIN lookup.BundleHeaderTypes b on b.Id = bh.BundleHeaderTypeId
LEFT OUTER JOIN dbo.[Transaction] t on t.Id = contrib.TranId


Latest Update

1/24/2022

Created article.