Crystal Reports with ODBC: Hard and Soft Credits by Constituent

Steven Cianciarulo

I'm working in Crystal Reports via an ODBC connection through the RODBA feature. 


I have one subreport that is correctly finding hard credit gifts for a constituent, then grouped and summarized by year, and a second subreport doing the same for gifts that same constituent has been soft credited for. 


What I need is a constituent's HC and SC total grouped and summarized by year together. If I were working in RE I'd just change the gift processing setting and get the right total, but I don't see any difference in the SQL when I try this in query. 


I know I can create and pass a variable and then do math on that in the main report, but I want to avoid having to remember to go back in and create a new formula for each year.


Any ideas?


Thank you!

Patrick Manning

Assuming that you have the {CONSTIT_GIFTS} junction table, you can include both regular gifts and soft credit gifts in the same report.  A basic formula would contain something like this:  {CONSTIT_GIFTS.FLAGS} IN [0,16]

Steven Cianciarulo

Patrick Manning:

Assuming that you have the {CONSTIT_GIFTS} junction table, you can include both regular gifts and soft credit gifts in the same report.  A basic formula would contain something like this:  {CONSTIT_GIFTS.FLAGS} IN [0,16]

Fascinating! I have the RE schema in an excel document, but it doesn't mention what the flags mean - do you know of somewhere I could look that up?


Thank you!

John Heizer

If you have the VBA/RE:Open module unlocked then you can access the full data dictionary within RE from Help -> RE:VBA/API Help.

James Andrews

Steven Cianciarulo:


Fascinating! I have the RE schema in an excel document, but it doesn't mention what the flags mean - do you know of somewhere I could look that up?


Thank you!

 

It's in the column properties in the database itself, if you have a way to view that:

 

A bitmask indicating the nature of the association between this constituent and this gift. 0-regular gift, 1-pledge company, 2-pledge constituent, 4-pay company, 8-pay constituent, 16-soft credit recipient

Edited to add: All the soft credit options are available views as well, the CONSTIT_GIFTS views -- the letters on the end describe which soft credit and matching credit options are used. B for both, D for Donor, R for Recipient, C for Company.

Joshua Bekerman bCLO, bCRE

It's also in the RE7Schema help file usually found here: C:\\Program Files (x86)\\Blackbaud\\The Raisers Edge 7\\Help


 

Patrick Manning

The best way to learn the table structure and field values, I found, was to create a query in Raiser's Edge and then view the SQL statement (on the query menu bar, under 'View' and 'SQL').

James Andrews

Patrick Manning:

The best way to learn the table structure and field values, I found, was to create a query in Raiser's Edge and then view the SQL statement (on the query menu bar, under 'View' and 'SQL').

Agreed, if you can find your way past the machine-generated aliasing and temp tables and piles and piles of parentheses...


LOL MORE PARENS PLEASE: 

 

WHERE  (((RECORDS_CONSTITUENT_CODES.CODE IN (17,18,41971,71129) 

        AND     ((RECORDS_CONSTITUENT_CODES.DATE_TO IS NULL

                OR RECORDS_CONSTITUENT_CODES.DATE_TO = ''))))

        AND RECORDS.KEY_INDICATOR = 'I')

    AND (RECORDS.IS_CONSTITUENT = - 1)

Steven Cianciarulo

This is all super helpful, thank you! You probably won't be surprised to hear that Blackbaud support couldn't assist and gave me the name of some consultants instead.