query of multiple appeals

Maggie meredith

Disclaimer - I am new to RE and am training myself, so forgive me if I don't phrase this well .  I need to create a query for all the appeals we've done from 2000-today, about 25 total, that would provide a total for each appeal, total # of gifts and then a breakout by amount category, like $1-100, $101-250, etc. Ultimately, the requestor wants it in an Excel spreadsheet where you could filter/check by appeal.  Any ideas how to start? We do have NXT, also, but have only had it 2 weeks, so I haven't learned all that it can do and this seems like more of a database process to me.  Thanks for any guidance anyone can provide!

JoAnn Strommen

You may need more than one approach.  You can get total $, # of gifts type info on a Reports > Campaign, Funds and Appeals Reports > Appeal Performance Analysis. Just select all your relevant appeals.


For the breakout by gift size using Reports > Analytic Reports > Donor Category Report. You will probably first need to go to Config > Tables > Donor Category and enter your ranges that you want generated stats for. While you can include more than one Appeal in the filters, it doesn't separate out each appeal in the report.  You may have to run the report for each appeal.


Maybe one of the pivot report gurus knows if you can get that info that way. I don't know enough about pivot reports.


Otherwise I think your looking at a large Export of all donors to the appeals and then 25 appeal total fields and then it would still require lots of manipulation to break out giving levels for each appeal.


Best wishes with this one. It will get you broke in very well on using RE.

Ryan Hyde

I think a pivot report might be the way to go for this. And it would be pretty straight forward - all you need is a filter for the Appeal ID, and then you will split out your rows by the predefined ranges, and then caculate the count of unique gifts and the $ total of the gifts per category.


The problem is creating those categories.


I feel like the easiest way to do this would simply be to export all of your gift data, and then create a column called something like 'tier.' Sort your whole spreadsheet by gift amount, and just start populating a tier label (e.g. "$0.1-50") for all gifts that would apply to that teir, and then create the next tier (e.g. "$50.01-100") and populate that for all applicable gifts, so on and so forth.


Or you could get tricky with formula, like =IF(AND(A:A>0,A:A<50.01,"$0.01-50,"")), and if you're good enough (or just patient enough), you could write a single formula that would populate the correct tier for every gift! 


But it's really probably just easier to manually do it. 

Jen Claudy

If this is something you will need to pull on a regular basis, I would use MS Access or a Crystal Report.  Personally, I don't know Crystal well, but it would be fairly easy to dump the data to Access and then put together a report there...which can then be refreshed whenever you need to.  But you need some level of knowledge with either Crystal or Access, and I suspect a Pivot Report is the next best option.

Maggie meredith

JoAnn Strommen:

You may need more than one approach.  You can get total $, # of gifts type info on a Reports > Campaign, Funds and Appeals Reports > Appeal Performance Analysis. Just select all your relevant appeals.


For the breakout by gift size using Reports > Analytic Reports > Donor Category Report. You will probably first need to go to Config > Tables > Donor Category and enter your ranges that you want generated stats for. While you can include more than one Appeal in the filters, it doesn't separate out each appeal in the report.  You may have to run the report for each appeal.


Maybe one of the pivot report gurus knows if you can get that info that way. I don't know enough about pivot reports.


Otherwise I think your looking at a large Export of all donors to the appeals and then 25 appeal total fields and then it would still require lots of manipulation to break out giving levels for each appeal.


Best wishes with this one. It will get you broke in very well on using RE.

Thanks for the suggestions!

Maggie meredith

Jen Claudy:

If this is something you will need to pull on a regular basis, I would use MS Access or a Crystal Report.  Personally, I don't know Crystal well, but it would be fairly easy to dump the data to Access and then put together a report there...which can then be refreshed whenever you need to.  But you need some level of knowledge with either Crystal or Access, and I suspect a Pivot Report is the next best option.

Thank you!

Maggie meredith

Thanks!