query of multiple appeals

Options
Maggie meredith
Maggie meredith
Eighth Anniversary First Comment Connector Level 1 Facilitator Level 1
in Blackbaud Raiser's Edge NXT®: General Discussion
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!
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    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
    Ryan Hyde
    Tenth Anniversary 1,000 Likes 500 Comments Name Dropper
    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
    Jen Claudy
    Tenth Anniversary 1,500 Likes 1000 Comments Name Dropper
    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
    Maggie meredith
    Eighth Anniversary First Comment Connector Level 1 Facilitator Level 1

    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
    Maggie meredith
    Eighth Anniversary First Comment Connector Level 1 Facilitator Level 1

    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
    Maggie meredith
    Eighth Anniversary First Comment Connector Level 1 Facilitator Level 1

    Thanks!

Categories