Core Fundraising Views

Script Deployment

Extract the zip file into a local folder and run CSI.Script.Deployment.App.exe

This should pop up:

The server name is the name of the SQL server

The login is your SQL login username (usually sa)

Enter your password and your iMIS database name

Then this screen will show up:

Select Deploy

The scripts will then be installed on your iMIS database and the way you will know is because the “Last Updated On” entry will update

CSI Core Fundraising Views

There are currently 18 custom views:

1. vCSI_Core_DonationsDetail

2. vCSI_Core_Gift

3. vCSI_Core_Gift_WithSoftCredits

4. vCSI_Core_Pledge

5. vCSI_Core_PledgeWithSoftCredits

6. vCSI_Core_PledgeData

7. vCSI_Core_GiftDistribution

8. vCSI_Core_GiftDistribution_ByFund

9. vCSI_Core_GiftDistribution_Single

10. vCSI_Core_GiftSoftCredit

11. vCSI_Core_GiftSoftCredit_ByFund

12. vCSI_Core_GiftSoftCredit_Single

13. vCSI_Core_DonationsDetail_Limited

14. vCSI_Core_Gift_Limited

15. vCSI_Core_Gift_WithSoftCredits_Limited

16. vCSI_Core_Pledge_Limited

17. vCSI_Core_Pledge_WithSoftCredits_Limited

18. vCSI_Core_PledgeData_Limited

and 1 custom function:

1. csi_fn_Core_GiftFiscalYears

They are all part of the CSI Core Fundraising Views package. All views should be deployed because some of them are dependent of each other (while some are standalone).
The major useful custom views are:

1. vCSI_Core_Gift

2. vCSI_Core_Gift_WithSoftCredits

3. vCSI_Core_Pledge

4. vCSI_Core_PledgeWithSoftCredits

The other custom views are helper views.

vCSI_Core_DonationsDetail

To be used for legacy purposes.
Gets all donations information (GIFT and paid PLEDGE).
Pulls data from views:
• vGift (*)
• vPledgeData (*)
• Invoice, Trans, and Activity

Very similar with vDonationsDetail and the major differences are:
• Returns Gift and Gift Adjustments (from vGift)
• Returns Pledge Payments instead of Pledge Installments (from vPledgeData)
• Does not returns data from DUES source system

vCSI_Core_Gift

Gets all donations information (GIFT and paid PLEDGE).
Pulls data from views:
• vGift (*)
• vCSI_Core_GiftDistribution_Single

(*) New fields (not in vGift) are:
• Distribution
• FiscalPeriod

vCSI_Core_Gift_WithSoftCredits

Gets all donations information (GIFT and paid PLEDGE) with all soft credit information.
Pulls data from views:
• vCSI_Core_Gift (based off vGift) (*)
• vCSI_Core_GiftSoftCredit
• vCSI_Core_GiftSoftCredit_Single

(*) New fields (not in vGift) are:
• Distribution
• FiscalPeriod
• SoftCredit (boolean/bit type)

vCSI_Core_Pledge

Gets all Pledge installments information (similar to vPledge)
Pulls data from views:
• vPledge (*)
• vCSI_Core_GiftDistribution_ByFund

(*) New fields (not in vPledge) are:
• Distribution
• FiscalPeriod

vCSI_Core_Pledge_WithSoftCredits

Gets all Pledge installments information (similar to vPledge) and all related soft credit information.
Pulls data from views:
• vCSI_Core_Pledge (based off vPledge) (*)
• vCSI_Core_GiftSoftCredit_ByFund

(*) New fields (not in vPledge) are:
• Distribution
• FiscalPeriod
• SoftCredit (boolean/bit type)

vCSI_Core_PledgeData

To be used for legacy purposes.
Get pledges information including adjustments and payments.
Pulls data from tables:
• vPledgeData (*)
• Trans
• Invoice

(*) New fields (not in vPledgeData) are:
• OriginalTransaction (same as TransactionNumber for Pledge transaction type otherwise different)
Altered field (compared to vPledgeData) are:
• TransactionDate (latest transaction date for Adjustment transaction type)
• InstallmentDate (latest installment date for Adjustment and Payment transaction types)
• LastPaymentDate (latest payment date date for Payment transaction type)
vCSI_Core_PledgeData returns more row(s) than vPledgeData for Payment transaction type records because of an additional grouping by Trans.TRANS_NUMBER (“PAY” line) field (from legacy code vCSI_PledgeData).
vCSI_Core_PledgeData does not return “Asterisk Tab Adjustment” transaction type records.
vCSI_Core_PledgeData does not return “Asterisk Tab Adjustment” and “Adjustment” transaction types records when Invoice.SOURCE_SYSTEM='DUES'.

vCSI_Core_GiftDistribution

Gets all distribution information from GIFT and PLEDGE activities.
Pulls data from tables:
• Activity

vCSI_Core_GiftDistribution_ByFund

Gets distribution information from GIFT and PLEDGE activities.
Pulls data from tables:
• Activity (*)

(*) Distribution can be blank when more than one value found for the same OriginalTransaction value.

vCSI_Core_GiftDistribution_Single

Gets distribution information from GIFT and PLEDGE activities (excluding split gift – that is one donation with multiple distributions).
Pulls data from tables:
• Activity (*)

(*) Only returns rows having one line (instance) for ID and ORIGINATING_TRANS_NUM.

vCSI_Core_GiftSoftCredit

Gets all soft credit information.
Pulls data from tables:
• Trans_SoftCredit
• Activity

vCSI_Core_GiftSoftCredit_ByFund

Get soft credit and related fund information.
Pulls data from tables:
• Trans_SoftCredit (*)
• Activity

(*) Distribution can be blank when more than one value found for the same OriginalTransaction value.

vCSI_Core_GiftSoftCredit_Single

Get soft credit information (excluding split gift – that is one donation with multiple distributions).
Pulls data from tables:
• Trans_SoftCredit (*)
• Activity

(*) Only returns rows having one line (instance) for SOFT_CREDIT_ID and TRANS_NUMBER.

vCSI_Core_GiftPayments

Gets all donations information (GIFT and paid PLEDGE) similar to vCSI_Core_Gift.
This new view has been created to replace the deprecated custom view called vCSI_GiftPayments.
Pulls data from view and table:
• vCSI_Core_Gift (*)
• Trans
(*) New fields (not in vCSI_Core_Gift) are:
• TransactionType
• BatchNumber
Notice PledgeID (from vCSI_Core_Gift) information is used when available for the ID field.

vCSI_Core_GiftAndPledge, 
vCSI_Core_GiftAndPledge_WithSoftCredits

Combine both vCSI_Core_Gift and vCSI_Core_Pledge.
Fields pertaining only to gifts:

• OriginalTransaction

• Amount

• FairMktValue

• CheckNumber

• PaymentType

• GiftType

• ReceiptID

• PledgeID

• AmountAsMoney

Field pertaining only to pledges:

• LastPaymentDate

• PledgeAmount

• AdjustmentsAmount

• NetPledgeAmount

• PaymentsAmount

• PledgeBalance

• PledgeFairMarketValue

Possible values for GiftType field are:

1. GIFT

2. PLEDGE

vCSI_Core_Reversed_Gifts

Shows Invoice Reference Numbers whose donations would have been reversed. That is more than one Transaction record (i.e. different transaction number) and a total balance of $0.
Pulls data from views:
• vGift

vCSI_Core_Donations

View to use DonationDetail data.

DonationDetail data is mirrored in vDonations and has actual donations made - both Pledges and Gifts. Most of CSI's core views report on dollars given - gifts and paid pledges. The donation report however has actual donations.

However, the donation report does not break things down into Distribution/Fund/Campaign/Appeal. Therefore, a split gift will be ONE line in the donation report, therefore, any fields that differ across the split gift (e.g., fund) will show as blank.

The view does not have Soft Credit info at this time.

Limited Views

The following views are named after their parent view with the suffix "_Limited".  The designation "_Limited" is intended to indicate that these views run approximately 50% slower than the parent view.  The limited versions all contain the same additional features which are responsible for the performance slowdown.  

• vCSI_Core_DonationsDetail_Limited

• vCSI_Core_Gift_Limited

• vCSI_Core_Gift_WithSoftCredits_Limited

• vCSI_Core_Pledge_Limited

• vCSI_Core_Pledge_WithSoftCredits_Limited

• vCSI_Core_PledgeData_Limited

The additional features of the limited views are the inclusion of three columns:  

• CurrentFiscalYear

• LastFiscalYear

• FiscalYearsAgo

These three columns are calculated by the user defined function csi_fn_Core_GiftFiscalYears.

Release Notes