| Author |
Message |
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
   Topic: Soft Credit Giving
Posted: 15 Aug 2011 at 6:30am |
|
Is anyone familiar with running Crystal Reports out of Raiser's Edge? If so, I have a question that continually plagues those of us who do.
I am having a serious issue with running accurate reports given the way that Raiser's Edge exports the information to MS Access, to be used in Crystal Reports. We have donations, some of which are through foundations that are soft credited with differing amounts to several donors. I.E. you will have a gift of $1,000 and soft credit of $200 to one person, $800 to another. This is rare but I need to account for it. Our people want to know how much people are giving even if it is a "soft credit" gift. To complicate matters exceedingly, we have spouses who are soft credited also (meaning duplicates if the gift is through a foundation, etc.), and of course single people who sometimes have a gift that is only a soft credit.
I can export gifts either (1) to donor only, (2) to SC recipients only, or (3) to both. In the above case, the problem is that no matter how it is done, the field "Gift Amount" always shows up as $1,000 for both people. You only get $200 or $800 in field "Soft Credit Amount".
Here is what I want to add together:
(1) Hard credit gifts meeting certain conditions
(2) Soft credit gifts to single persons and head of household persons only (when there is a spouse), meeting the same conditions
This problem exists with every giving report I will run out of Raiser's Edge to Crystal Reports. But here is the specific report I have which makes it even more difficult:
A Trustee giving report, with the specific issue being including giving from their Guests at Events.
I can export their Guest information, including giving and soft credit information. However, just as above I need (1) and (2). If I could get all gifts in one list I could arrange by Gift ID and do a running total, but as far as I know that's impossible. With two separate lists (i.e. hard credit gifts and "soft credit amount") I get duplicates and either exclude more people than I want (i.e. the single people) or include too many (i.e. spouses).
I realize this is a very specified question to this situation with Raiser's Edge, and thus may be confusing, but I would greatly appreciate any insight.
|
 IP Logged |
|
|
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
Posted: 15 Aug 2011 at 6:44am |
|
I am working on a solution that is probably a roundabout way, but I am hoping it will work.
In Raiser's Edge, I can make a query that pulls only Soft Credit Gifts from Head of Household and Singles, then run two separate gift exports, one for hard credit, one for soft credit, run two subreports in Crystal, and add them together. I am hoping this may work, but it would mean a lot of work for any report which would be nice to avoid if there were an easier way.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Aug 2011 at 7:22am |
|
maybe if you post sample data rows and how you want it to appear in the end. You can use dummy values as lo9ng as they are structurally correct.
|
|
IP Logged |
|
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
Posted: 15 Aug 2011 at 8:41am |
|
DBlank:
I think my problem is I can't get the data rows I want. If I could get to appear only the data I want, I think I could make it appear as I want.
I am only really looking for totals, like so:
John Doe (Trustee)
Guest Giving - (Subreport 1: hard credit gifts: $1,405.00)
- (Subreport 2: soft credit gifts: $756.00)
Total: $2,161.00
My problem is that in "Soft credit gifts" I am either getting gifts duplicated from subreport 1, or I am not catching all of the gifts.
The above proposal that I tried, by the way, did not work again because RE messed it up. I am going to now try exporting all gifts separately and linking the "Soft Credit Recipient ID" with guest IDs from the main report, sort by gift ID, then running total. This is what my tables would then look like:
Main Report:
Trustee
Events
Guests
Constituent ID
Subreport:
Gifts
Event ID (how I find which gifts I want)
Soft Credits
Amount
Constituent ID
I want to try to link the Constituent IDs from subreport to main report. Hopefully that will give me what I want, which would show something like this in the subreport:
Amt. Gift ID
$500.00 85769
$500.00 85769
$250.00 75863
RTotal: $750.00 (shared variable to main report)
There would be duplicates I think, because of spouses, but I could eliminate this with Gift ID.
This question is rather unique to RE. If it doesn't make sense as too much related to RE, don't worry about it. I appreciate the help much though.
Edited by JFinzel - 15 Aug 2011 at 8:42am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Aug 2011 at 3:35am |
Your immediate problem is specific to using RE but the overall problem is pretty universal. How to get only the data you want out of a DB without extra data or omitting data.
I would suggest that you try and throw the wider net to get too much dat and then se if you can conditionally suppress the unwanted rows and use Running Totals or shared variable formuals for using similar conditional formulas to avoid summing the suppressed rows.
|
|
IP Logged |
|
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
Posted: 16 Aug 2011 at 6:50am |
|
On one of my reports (not the one above), I was able to get the correct amount using a formula:
IF {CnGf_1SfCrdt_1.CnGf_1SfCrdt_1_Constit_ID}={CnBio.CnBio_ID} THEN {CnGf_1SfCrdt_1.CnGf_1SfCrdt_1_Amount} ELSE IF {CnGf_1.CnGf_1_SCMGFlag}="Regular Gift" THEN {CnGf_1Apls_1.CnGf_1Apls_1_Amount} ELSE 0
So if the soft credit person's ID = Gift Constituent ID, it outputs the soft credit amount. Or, if it is a non-soft credit gift, it puts out the regular gift amount. If neither of these, it puts out 0.
This gets me the correct numbers but it also shows every soft credit, meaning duplicates, meaning I can't show a report of individual gifts, only a summary.
Another problem is this takes several minutes to run (summarizing several thousand gifts). I am a newb and don't really know much about indexing tables and if this might speed things up. However, as this is a report I will need to update frequently, that may not be the best option.
Thank you much for your help. Let me know if you have any other suggestions.
Edited by JFinzel - 16 Aug 2011 at 6:51am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Aug 2011 at 7:59am |
there are other ways to count /sum fields using conditional formulas or Running Totals (not just f-then statements like you used for your other report).
YOu can hide rows using next() or preevious() functtions to avoid showing all gifts. It is hard to tell you what will work exaclty, just trying to give you ideas here. If you post raw sample data rows (including your primary keys) and then show how you want it displayed and summed. usually someone can give you the process on how to hide unwanted rows as well as sum only the desired rows.
|
|
IP Logged |
|
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
Posted: 16 Aug 2011 at 9:01am |
|
Okay, let me try this. In answering your question, I hope that I am already finding I am not doing this the quickest way. I don't think any of my info is grouped by primary key. Like I said, I am inexperienced.
The report is grouped first by appeal (or source), and then by constituent.
(Group 1: Appeal ID) - this is not primary key, primary key is Appeal IDKEY
(Group 2: CnBio_Name) - not primary key, which is CnBio_IDKEY
Gift Date Appeal Amount Formula result (listed above)
(key: Gf_IDKEY) (key: Apls_IDKEY)
12/23/10 $356.23 $0.00
12/23/10 $356.23 $0.00
12/23/10 $356.23 $25.00
3/2/11 $56.73 $12.50
3/2/11 $56.73 $0.00
3/2/11 $56.73 $0.00
3/2/11 $56.73 $0.00
6/12/11 $867.25 $0.00
6/12/11 $867.25 $278.26
Then simple sum field on formula: $315.76
So I would want to hide all rows that show "0.00" in the formula field.
Also, I have a grand total at the bottom that sums all formula fields in report (not surprisingly, that takes awhile).
Can you tell me: Would it be much quicker for me to group 1 on Appeal IDKEY and group 2 on Const. IDKEY?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Aug 2011 at 10:19am |
so you want this:
12/23/10 $356.23 $0.00
12/23/10 $356.23 $0.00
12/23/10 $356.23 $25.00
Can't really tell you about the grouping as I am not fully grasping the architecture of the data. I think the way you have it is fine though if it gets you to the desired data:
YOu want your sample aboe to lok like
12/23/10 $356.23 $25.00
3/2/11 $56.73 $12.50
6/12/11 $867.25 $278.26
YOu can write a suppression as in the section expert as
{@ formulafield}=0
not sure why the calcuklation takes long on the
SUM({formulafield},groupfield)
|
|
IP Logged |
|
JFinzel
Groupie
Joined: 20 Jul 2011
Location: United States
Online Status: Offline
Posts: 49
|
Posted: 17 Aug 2011 at 6:42am |
|
Thank you, that suppression worked easily. I didn't know about it.
I am having trouble now getting my running total to accurately run in my other report, the original one listed above. Based on our discussion, I was also able to get the values I want using record selection, but in this case, I have spouse duplicates, so I have to use a running total that resets on Gift ID. In one particular case, it is not working.
Here is the record selection formula I am using for this sub-report:
{Cn.Cn_IDKEY} = {?Pm-Cn.Cn_IDKEY} and
{CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Appeal_ID} in [{CnPrt_1Ev.CnPrt_1Ev_Event_ID}] and
not ({CnPrt_1Gst_1CnBio.CnPrt_1Gst_1CnBio_ID} in [{CnBio.CnBio_ID},{CnSpSpBio.CnSpSpBio_ID}])
And, here is the formula I am using for my "SC" field, based on appeal and soft credit like above:
IF {CnPrt_1Gst_1CnGf_1SfCrdt_1.CnPrt_1Gst_1CnGf_1SfCrdt_1_Constit_ID}={CnPrt_1Gst_1CnBio.CnPrt_1Gst_1CnBio_ID} THEN IF {CnPrt_1Gst_1CnGf_1SfCrdt_1.CnPrt_1Gst_1CnGf_1SfCrdt_1_Amount}>{CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Amount} THEN {CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Amount} ELSE {CnPrt_1Gst_1CnGf_1SfCrdt_1.CnPrt_1Gst_1CnGf_1SfCrdt_1_Amount} ELSE IF {CnPrt_1Gst_1CnGf_1.CnPrt_1Gst_1CnGf_1_SCMGFlag}="Regular Gift" THEN {CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Amount} ELSE 0
Here is what I get in one case where it does not work:
Appeal Amt Soft Credit Amt SC(formula) Gift ID
150 150 150 84759
150 150 150 84759
300 300 300 87484
489 987 489 89984
Rtotal: 600
Explanation: In certain cases we have a split gift that is also a soft credit gift. With RE, there is no split soft credit option. In this case, I want to read the "appeal amount", not the "soft credit amount" (the part of the formula that makes this happen is IF
{CnPrt_1Gst_1CnGf_1SfCrdt_1.CnPrt_1Gst_1CnGf_1SfCrdt_1_Amount}>{CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Amount}
THEN {CnPrt_1Gst_1CnGf_1Apls_1.CnPrt_1Gst_1CnGf_1Apls_1_Amount}).
This bolded gift above is exactly this case, and my running total for some reason is not adding this gift. All other cases add up correctly.
As far as I know, I also have "default values for nulls" selected, if that could play a part.
Thoughts? Thank you much for the help.
|
|
IP Logged |
|
|
|
