Crystal Reports with ODBC: Hard and Soft Credits by Constituent

Steven Cianciarulo · February 2020

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 2 · February 2020

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 · February 2020

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 · February 2020

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 · February 2020

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 · February 2020

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

Patrick Manning 2 · February 2020

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 · February 2020

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 · February 2020

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.

Crystal Reports with ODBC: Hard and Soft Credits by Constituent

Comments

Categories