Split gifts in Crystal Reports

Snigdha Verma

How do you report on split gifts in Crystal Reports when the splits apply to different funds and campaigns in your details? I am getting duplicates totals there. TIA!

Marie Stark

Snigdha Verma:

How do you report on split gifts in Crystal Reports when the splits apply to different funds and campaigns in your details? I am getting duplicates totals there. TIA!

Instead of exporting the gift amount, you should expor the Fund Amount.

Snigdha Verma

Thanks Marie. I am already doing that. But that still shows me the full amount.

Lauren Schler CFRE bCREPro

Yes, you need to export split fund amount and
then use a running total to summarize your gift. You need to create
a unique gift ID for use in the Evaluate Each Record field in the
Running Total:

 

Example of a Unique Gift ID:

{Gf.Gf_Gift_ID}&{GfFnds_1.GfFnds_1_Fund_ID}

 

Best,

 

Lauren

 

Snigdha Verma

Thanks Lauren! I'll try that out. Will that also work with the Campaign Amount as I am summarizing by Campaign?

Lauren Schler CFRE bCREPro

It should work with Campaign Amount as
well.

 

Best,

 

Lauren

 

Snigdha Verma

It still is duplicating.  I added the formula with Gift ID and Campaign ID and used that to Evaluate.

The field I am summarizing is also based on a formula looking at certain gift types and Campaign. I know that is correct.



 

Aaron Rothberg

Hi Snigdha-


If you had split your gift across Campaigns ONLY or Funds ONLY, the suggestions above would work fine, but the moment you split gifts across both Campaigns and Funds you're going to have issues reporting this out in a single Crystal Report without creating custom tables with independant formulas for each area of the table. Consider the split gift below:

 

Campaign Split Amount is going to give us $800,000 and $200,000, but Fund Split Amount is going to give us $300,000 and $700,000. If you try to report on both at the same time you will see duplicates. I'm a Crystal Novice so someone with more experience may have a better response, but this is  more of a problem with Export IMO than it is with Crystal.

James Andrews

this is  more of a problem with Export IMO than it is with Crystal.

 

This is true.


Most gift information is stored in the table GIFT.  Campaign, fund, appeal and package information are stored in the table GiftSplit (even if a gift isn't split, this is where it's stored).  For some reason, that table isn't available as-is in Export -- instead they have those various amounts under campaign, fund, and appeal, all of which export separately and lead to this kind of duplicate gift counting.

Snigdha Verma

So the only option is to enter split gifts separately?

John Heizer

Aaron Rothberg:

Hi Snigdha-


If you had split your gift across Campaigns ONLY or Funds ONLY, the suggestions above would work fine, but the moment you split gifts across both Campaigns and Funds you're going to have issues reporting this out in a single Crystal Report without creating custom tables with independant formulas for each area of the table. Consider the split gift below:

 

Campaign Split Amount is going to give us $800,000 and $200,000, but Fund Split Amount is going to give us $300,000 and $700,000. If you try to report on both at the same time you will see duplicates. I'm a Crystal Novice so someone with more experience may have a better response, but this is  more of a problem with Export IMO than it is with Crystal.

 

BINGO!  And it drives me crazy!!!  Why on earth RE can't export these gifts the way they are stored in the database is beyond me.  Fixing this is another one of the many CRITICAL "Ideas" that have gone ignored.


If you're locally hosted there are ways around the problem by accessing the table directly via ODBC, but you lose that if you're hosted.

James Andrews

Snigdha Verma:

So the only option is to enter split gifts separately?

That's the general consensus, yes.


RE's implementation of split gifts is problematic for two main reasons. The first is the exporting situation you are experiencing.


The second is how pledge payments are linked to pledges -- there is a table called Installment that links the pledge gift record and the payment gift record(s).  The only fields in it are the amount and date of the installment -- RE doesn't store campaign/fund/appeal info for the installments. So pledges that are split unevenly are basically untrackable within the system -- you'd have to store your info about how to apply the payments somewhere else and reference it when they come in.

Snigdha Verma

John, we are locally hosted. Would I create a new query in Excel to access this info?

Aaron Rothberg

If you are locally hosted you would use SQL Server Management Studio and create an SQL query to retrieve your data. To attempt this you're going to have to learn a little bit SQL and also understand how the database tables relate to each other. If you have an SQL database administrator in your IT department your best bet is to try bribing him/her with beer and pizza.


You could try connecting to the SQL database from Excel using the Data tab. You'll need a username and password in the SQL database with permissions to do this, but personally I would find an SQL query in the Management Studio faster and easier. YMMV.

James Andrews

Aaron Rothberg:

If you are locally hosted you would use SQL Server Management Studio and create an SQL query to retrieve your data. To attempt this you're going to have to learn a little bit SQL and also understand how the database tables relate to each other. If you have an SQL database administrator in your IT department your best bet is to try bribing him/her with beer and pizza.


You could try connecting to the SQL database from Excel using the Data tab. You'll need a username and password in the SQL database with permissions to do this, but personally I would find an SQL query in the Management Studio faster and easier. YMMV.

Yes, definitely write it in SSMS. But since this is posted in the crystal forum, you'd then want to take that query and use it as the data source for your crystal report. So yes, the first step is getting access to your database...

Megan Girvalo 2

Hi All -


I know this is an old thread but thought perhaps someone could help me. We are having issues displaying additional gift information with Split Gifts in crystal report.


I am able to display the split gift amounts by using the "Fund Gift Amount" field which correctly shows me a row per portion of the gift (usually a max of two rows since we generally only receive gifts split between two funds). The issue is that for those rows the gift attributes don't display correctly on the second row leaving a blank field where I would expect to see the same value as in the first row.


For example, we have a single gift of $100 that is split between two funds  (50/50). I would expect to see something like this:


Donor Name, Gift Date, $50, Fund X, Gift Attribute Z

Donor Name, Gift Date, $50, Fund Y, Gift Attribute Z


But instead I see this:

Donor Name, Gift Date, $50, Fund X, Gift Attribute Z

Donor Name, Gift Date, $50, Fund Y, BLANK


Any thoughts displaying the attribute information correctly in both rows?


Thanks in advance.


Megan