Grouping without data workaround

Karen Diener 2

I'm working on a gift table and have a problem I've experienced before. I saw another thread about it here with a "you won't be able to do that" answer, and I'm just convinced that we can find a way.

Typical gift table - shows how many donors we need and in what range. The next two columns show how many donors we have at a given level, and how much we have received from those donors. Blackbaud helped me with a solution before, but it doesn't work for all situations. Like i said though, maybe we can riff on this and figure it out. We created "page header B" containing zeroes for dollars to date and donors to date for the highest level, and suppressed that if there WERE donors at that level.

Works well, and I tried to apply to other levels. So that if I have a donor at the highest level A, and no one at the second or third levels (B and C), my A-level donor info shows up, but B and C levels show the 0s. And so on down the ranges where E through Z (or whatever) have their donor counts and amounts. This does not work. It shows my 0 levels, and then repeats those same levels with the actual donor and dollar counts.

Does that make sense?

For now, I've been exporting the report to Excel and removing the duplicate rows, but obviously not a real solution. And now I have a new gift table to create that includes counts and totals of attributes that are not linked to the Summary Giving groups. I.e., someone might appear in the $25K - $50K level, but have an attribute for their ask amount that is $10K, or $100K, or doesn't even have the attribute.

This HAS to be possible, right? For anyone who has come this far in my post, THANK YOU for reading through it! Thank you even more if you have a suggestion! We do have Crystal Support through BB, so I can call them. Just hoping we can figure it out here. Any ideas??

Joshua Bekerman bCLO bCRE

Karen Diener:
I'm working on a gift table and have a problem I've experienced before. I saw another thread about it here with a "you won't be able to do that" answer, and I'm just convinced that we can find a way.

Typical gift table - shows how many donors we need and in what range. The next two columns show how many donors we have at a given level, and how much we have received from those donors. Blackbaud helped me with a solution before, but it doesn't work for all situations. Like i said though, maybe we can riff on this and figure it out. We created "page header B" containing zeroes for dollars to date and donors to date for the highest level, and suppressed that if there WERE donors at that level.

Works well, and I tried to apply to other levels. So that if I have a donor at the highest level A, and no one at the second or third levels (B and C), my A-level donor info shows up, but B and C levels show the 0s. And so on down the ranges where E through Z (or whatever) have their donor counts and amounts. This does not work. It shows my 0 levels, and then repeats those same levels with the actual donor and dollar counts.

Does that make sense?

For now, I've been exporting the report to Excel and removing the duplicate rows, but obviously not a real solution. And now I have a new gift table to create that includes counts and totals of attributes that are not linked to the Summary Giving groups. I.e., someone might appear in the $25K - $50K level, but have an attribute for their ask amount that is $10K, or $100K, or doesn't even have the attribute.

This HAS to be possible, right? For anyone who has come this far in my post, THANK YOU for reading through it! Thank you even more if you have a suggestion! We do have Crystal Support through BB, so I can call them. Just hoping we can figure it out here. Any ideas??

Hi Karen, not sure I understand completely but I think I have a similar report.  I have what is basically an appeal summary report that uses information from Appeal records (i.e. number solicited, expenses, etc).  The issue I had was since I was using a Gift export the report would not list Appeals that we have not received any gifts for that appeal yet.  The report needed to basically have all appeals that have or will be going out for the year, so I added dummy gifts to my RE constituent record (amount = $0, do not post).  I realize this is a workaround but it seemed nearly impossible to achieve the same results on the Crystal Reports side.  Every time we send another appeal and receive a gift from it I delete the dummy gift from my record. 

I'm also not sure about the second part of your question.  Do you need the Summary Giving groups on the same report as the Gift Table report?

Karen Diener 2

Josh Bekerman:

Hi Karen, not sure I understand completely but I think I have a similar report.  I have what is basically an appeal summary report that uses information from Appeal records (i.e. number solicited, expenses, etc).  The issue I had was since I was using a Gift export the report would not list Appeals that we have not received any gifts for that appeal yet.  The report needed to basically have all appeals that have or will be going out for the year, so I added dummy gifts to my RE constituent record (amount = $0, do not post).  I realize this is a workaround but it seemed nearly impossible to achieve the same results on the Crystal Reports side.  Every time we send another appeal and receive a gift from it I delete the dummy gift from my record. 

I'm also not sure about the second part of your question.  Do you need the Summary Giving groups on the same report as the Gift Table report?

Your appeal report sounds very similar. I guess I could do the same with dummy gifts if it comes down to it. That would help the first part, anyway.

There are two similar problems. The report is grouped in ranges by the constituent's total giving. But occasionally, people don't happen to have fallen into one of the ranges, and so that level doesn't appear. That is the first 3/4 of the report - how many do we need, how many do we have, etc.

The rest is based on attributes. There is one attribute with a "readiness code" that says the prospect is already considering an ask, or is going to be asked this year. (The attribute is set up as a table with other possibilities, but we are only concerned about those two for this report.) Then there is another attribute (text) containing the ask amount. So I need to report how many people are currently considering a gift in the $1m - $2.5m range, and how much we asked for. Then the same thing for the people who are being asked this year. Like the main part of the report, there might not be anyone with an ask amount in for the $1m - $2.5m range, which causes that row to not appear. Does that help?

Frustrating because it is relatively simple to do in Excel - just a lot of steps through Pivot tables. But maddeningly difficult in Crystal.

Marie Stark

Karen Diener:
Your appeal report sounds very similar. I guess I could do the same with dummy gifts if it comes down to it. That would help the first part, anyway.

There are two similar problems. The report is grouped in ranges by the constituent's total giving. But occasionally, people don't happen to have fallen into one of the ranges, and so that level doesn't appear. That is the first 3/4 of the report - how many do we need, how many do we have, etc.

The rest is based on attributes. There is one attribute with a "readiness code" that says the prospect is already considering an ask, or is going to be asked this year. (The attribute is set up as a table with other possibilities, but we are only concerned about those two for this report.) Then there is another attribute (text) containing the ask amount. So I need to report how many people are currently considering a gift in the $1m - $2.5m range, and how much we asked for. Then the same thing for the people who are being asked this year. Like the main part of the report, there might not be anyone with an ask amount in for the $1m - $2.5m range, which causes that row to not appear. Does that help?

Frustrating because it is relatively simple to do in Excel - just a lot of steps through Pivot tables. But maddeningly difficult in Crystal.

We have a very similar report.  I have all the  ranges for each level hard coded.  Each is a separate line on the report. Then I have a series of counters for each gift range. The gifts with the attribute are counted in a subreport using a similar series of counters. Then each count from the subreport is shared to the main report and added to the first series. You are right,it is maddeningly difficult!

Karen Diener 2

Marie Stark:

We have a very similar report.  I have all the  ranges for each level hard coded.  Each is a separate line on the report. Then I have a series of counters for each gift range. The gifts with the attribute are counted in a subreport using a similar series of counters. Then each count from the subreport is shared to the main report and added to the first series. You are right,it is maddeningly difficult!

Wow Marie! So is there any grouping on the report? I was trying something along those lines as a test yesterday, and the report only got worse! But I still had it grouped, so maybe that was part of the problem.

So are you basically hard coding ranges into different rows in the detail section, and then using subreports and formulas to calculate everything? I see how that could work, but lots of manual stuff, and I hate passing variables back and forth. I have the code to pass variables, but I don't fully understand it which is why I don't like it. Of course, I also don't want to manually create the report in Excel each month!

Marie Stark

Karen Diener:
Wow Marie! So is there any grouping on the report? I was trying something along those lines as a test yesterday, and the report only got worse! But I still had it grouped, so maybe that was part of the problem.

So are you basically hard coding ranges into different rows in the detail section, and then using subreports and formulas to calculate everything? I see how that could work, but lots of manual stuff, and I hate passing variables back and forth. I have the code to pass variables, but I don't fully understand it which is why I don't like it. Of course, I also don't want to manually create the report in Excel each month!

Hi Karen,

Yes, that's how I'm doing it. There are no groups in the report.

It took a whole  lot of work to set it up, but I think it was worth it in the long run.

Danielle Repic

Hi,

Do you need individual gift information?  Is the attribute a constituent attribute or gift?  I'm assuming the attribute is on the constituent and the gift summaries are also per constituent.  I would use running totals with formulas and put your summaries in the report footer in a columned format.  I would export totals for the constituent based on fiscal year.  If you need to count based on hard/soft credit and constituency code, you can export a separate column for each.  Hope that helps.

Danielle