DEVILISH PROBLEM: Evaluating whether the Gift Solicitors Field contains a solicitor who has a particular constituent code

Patrick Hunt

I'm working on a fundraising report in which I need to determine for each gift whether of not it was solicited by a fundraiser in central development or not. Here's are the pertinent details:

• The Gift Solicitor filed may contain any number gift solicitors which are concatenated into a single semi-colon delimited output string (example: "Bob Smith; Sally Jones; Steve Jobs").
• Our organization may add board members, staff and/or faculty as gift solicitors on a given gift
• In addition to having been marked as a solicitor with the "Is a solicitor" checkbox, Central Development fundraisers also have a constituent code of "Central Development Fundraiser"

Additional Details About the report:

• I do not want to use a cross tab report - I want all gift categories listed for all months regardless of whether there are gifts to total or not
• The report includes 12 monthly running totals (using the Crystal's Running Total Fields) for each category of gift, sample output here (for only 2 months)"

Gift Category July August GRAND TOTAL

Outright Gifts by Central Development $1,000 $500 $1,500

All Other Outright Gifts $2,500 $1,000 $3,500

Pledges by Central Development $10,000 $25,000 $35,000

All Other Pledges $0 $0 $0
Totals $13,500 $26,500 $40,000

​​​​​​

My current attempt at getting to this data is through the use of some unconventional table joins (TABLE JOINS) which very nearly have me there but I can't seem to get around the duplicate gifts issue when there is more that one solicitor associated with a gift. When I've tried doing the evaluation in a sub report via a shared variable I find that I can't perform the running totals due to evaluation time issues.

Joshua Bekerman bCLO bCRE

Is it possible for a single gift to have both Central Development and Non-Central Development solicitors? If so, which group should the gift fall under on the report?

Patrick Hunt

If a gift has both Central Development solicitor(s) and non-Central Development solicitor(s) it should count under Central Development.

Joshua Bekerman bCLO bCRE

Thanks. Sounds like you are linking back to the solicitor constituent record's attribute to determine if they are Central Development, ya? Are there a lot of Central Development solicitors? If there aren't too many and they don't change very often I would do the check for Central Development using an InStr formula. Then you could base your grouping on that formula's result of True or False. Also make sure your table links are left outer joins.

Patrick Hunt

You're solution would work but I think would require hardcoding Central Development Fundraiser names in the report, something like this?

If instr({Gift Solicitor},"Bob Smith") then true
Else If instr({Gift Solicitor},"Manual Hernandez") then true
...
(repeat for all Central Development Fundraisers)
...
else false

Joshua Bekerman bCLO bCRE

Yup, exactly what I was thinking. Let us know if it works.

Lucien Piekut 2

It's doable. I think you have two sections going on here, both can be solved with subreports & global variables.

A) Gift by multiple solicitors: The gift solicitor field, treat the gift ID on the main page as its own entity, then in the sub report you can concatenate all the solictors by gift ID and/or consitutuent code. They can then be referenced against the gift ID via join in Crystal Reports.

Constituent/Department by month: For the second, make 12 columns for the months (hard code them, just text fields) and do the same for the constituent groups you want on the left. Then create a subreport that normalizes the data into the pairings you want, then back on the main report you can "sum if" the totals from the subreport.


If you're running into duplicate gifts its the subreports that will solve that. You mentioned evaluation time issues but those should be handled by resetting the variables on new group OR by simply moving your chart to the footer so it is evaluating AFTER the subreport. Crystal Reports traversal is pretty logical once you get used to it. Good luck!