SSRS Reporting from RE

Stephanie Osborne · October 2016

Looking to build a pledge report out of RE in SSRS. We currently have the RE schema for the table mappings but I'm having a hard time finding where RE stores the information that links the gift split payments to the corresponding split gift pledge. RE links the payment to the pledge through the batch but I'm having a hard time finding where that link lives on the backend. Is there an ID or table somewhere that stores this information?

James Andrews · October 2016

stephanie osborne:

Looking to build a pledge report out of RE in SSRS. We currently have the RE schema for the table mappings but I'm having a hard time finding where RE stores the information that links the gift split payments to the corresponding split gift pledge. RE links the payment to the pledge through the batch but I'm having a hard time finding where that link lives on the backend. Is there an ID or table somewhere that stores this information?

The pledges are in GIFT.

Installments are in Installment (Installment.pledgeid is a FK to GIFT.ID).

Payment info is stored in InstallmentPayment (InstallmentID FK to Installment.InstallmentID, PaymentID FK to GIFT.ID for the payment gift, PledgeID FK to GIFT.ID for the pledge).

Note that split gifts are handled entirely separately via the GiftSplit table (which links up GIFT with FUND, CAMPAIGN and APPEAL). There are no links between the Installment table and CAMPAIGN, FUND or APPEAL.

Stephanie Osborne · October 2016

James Andrews:

stephanie osborne:

Looking to build a pledge report out of RE in SSRS. We currently have the RE schema for the table mappings but I'm having a hard time finding where RE stores the information that links the gift split payments to the corresponding split gift pledge. RE links the payment to the pledge through the batch but I'm having a hard time finding where that link lives on the backend. Is there an ID or table somewhere that stores this information?

The pledges are in GIFT.

Installments are in Installment (Installment.pledgeid is a FK to GIFT.ID).

Payment info is stored in InstallmentPayment (InstallmentID FK to Installment.InstallmentID, PaymentID FK to GIFT.ID for the payment gift, PledgeID FK to GIFT.ID for the pledge).

Note that split gifts are handled entirely separately via the GiftSplit table (which links up GIFT with FUND, CAMPAIGN and APPEAL). There are no links between the Installment table and CAMPAIGN, FUND or APPEAL.

Thanks! If I have a split pledge with corresponding split payments can that still be pulled from the installment table or is that what you're saying has no link? I'm having trouble linking up the split payment within the particular split pledge.

James Andrews · October 2016

stephanie osborne:

James Andrews:

stephanie osborne:

Looking to build a pledge report out of RE in SSRS. We currently have the RE schema for the table mappings but I'm having a hard time finding where RE stores the information that links the gift split payments to the corresponding split gift pledge. RE links the payment to the pledge through the batch but I'm having a hard time finding where that link lives on the backend. Is there an ID or table somewhere that stores this information?

The pledges are in GIFT.

Installments are in Installment (Installment.pledgeid is a FK to GIFT.ID).

Payment info is stored in InstallmentPayment (InstallmentID FK to Installment.InstallmentID, PaymentID FK to GIFT.ID for the payment gift, PledgeID FK to GIFT.ID for the pledge).

Note that split gifts are handled entirely separately via the GiftSplit table (which links up GIFT with FUND, CAMPAIGN and APPEAL). There are no links between the Installment table and CAMPAIGN, FUND or APPEAL.

Thanks! If I have a split pledge with corresponding split payments can that still be pulled from the installment table or is that what you're saying has no link? I'm having trouble linking up the split payment within the particular split pledge.

Yeah that's what I'm saying has no link. You can split the pledge, and you can split the payments (because they both live in the GIFT table, which links to GiftSplit) but there are no splitting options on the installments.

Stephanie Osborne · October 2016

Ok thanks that's what I've come to find. Right now we're just pulling all splits within the one pledge and matching them based on the same campaign, fund, and appeal. Is there a better way to do that or another work around?

James Andrews · October 2016

stephanie osborne:

Ok thanks that's what I've come to find. Right now we're just pulling all splits within the one pledge and matching them based on the same campaign, fund, and appeal. Is there a better way to do that or another work around?

I think that barring creating an external table and updating it somehow, that's about the best you can do.

Stephanie Osborne · October 2016

James Andrews:

stephanie osborne:

Ok thanks that's what I've come to find. Right now we're just pulling all splits within the one pledge and matching them based on the same campaign, fund, and appeal. Is there a better way to do that or another work around?

I think that barring creating an external table and updating it somehow, that's about the best you can do.

Thanks for your help!

John Heizer · November 2016

stephanie osborne:

James Andrews:

stephanie osborne:

Ok thanks that's what I've come to find. Right now we're just pulling all splits within the one pledge and matching them based on the same campaign, fund, and appeal. Is there a better way to do that or another work around?

I think that barring creating an external table and updating it somehow, that's about the best you can do.

Thanks for your help!

Split gifts, particularly for a pledge, can be a nightmare. As you've probably already found out if you're using SSRS for reporting instead of in-app Exports and custom Crystal Reports, you can't Export split gifts the same way they are stored and tracked in the database (a split gift record for each split detail). So trying to create a custom report for splits pretty much requires direct access to the database and the split gifts table.

I've got an odd situation where a number of Individual pledges (multiple people, multiple CFA splits) were paid with one Gift and there were some Write-offs also involved. It's resulted in two of the pledges still showing a $0.01 balance in some reports, but not all (and shows a $0 balance on the gift record).

Many orgs avoid split gifts entirely because of these quirks. I'll still split a pledge payment (since we don't count the payment as "revenue," only the pledge, it can't screw up things too much for me), but I avoid them when entering a Cash gift or new Pledge.

James Andrews · November 2016

John Heizer:

Split gifts, particularly for a pledge, can be a nightmare. As you've probably already found out if you're using SSRS for reporting instead of in-app Exports and custom Crystal Reports, you can't Export split gifts the same way they are stored and tracked in the database (a split gift record for each split detail). So trying to create a custom report for splits pretty much requires direct access to the database and the split gifts table.

This is really a frustrating aspect of RE for me. The way the split is stored in the database is basically GiftID, Amount, CampaignID, FundID, AppealID. I don't get why they can't have it export the way it's stored.

Stephanie Osborne · November 2016

James Andrews:

John Heizer:

Split gifts, particularly for a pledge, can be a nightmare. As you've probably already found out if you're using SSRS for reporting instead of in-app Exports and custom Crystal Reports, you can't Export split gifts the same way they are stored and tracked in the database (a split gift record for each split detail). So trying to create a custom report for splits pretty much requires direct access to the database and the split gifts table.

This is really a frustrating aspect of RE for me. The way the split is stored in the database is basically GiftID, Amount, CampaignID, FundID, AppealID. I don't get why they can't have it export the way it's stored.

Thanks all for the input! I've come to find that split gifts cause too many reporting issues as well but we've been using them for years so for consistency purposes we still book them this way. Maybe we need to revisit this topic.

So just to clarify, although the split is linked to the split pledge when you batch it, RE still only stores the split with the GiftID, Amount, Campaign, Fund, and Appeal? When you apply the split to the pledge through the link, does that not live anywhere?

James Andrews · November 2016

stephanie osborne:
So just to clarify, although the split is linked to the split pledge when you batch it, RE still only stores the split with the GiftID, Amount, Campaign, Fund, and Appeal? When you apply the split to the pledge through the link, does that not live anywhere?

yep.

Pledge--> GiftSplit (campaign, fund, appeal, amount)

|

\\|/

Installment (date, amount)

|

\\|/

Payment--> GiftSplit (campaign, fund, appeal, amount)

SSRS Reporting from RE

Comments

Categories