Generating annual total giving by giving range

@Patrick Craine:

Hi all,

I'm new to Raiser's Edge. I want to figure out how to determine the total dollars raised in a particular giving range during a particular year. Such as “We received XX dollars from those in giving range $500-999 in FY 2022-2023.”

Any suggestions on how to run this?

Thanks!

@Patrick Craine while there is a standard report (the Donor Category report) that you could possibly use to generate this, I prefer query and export. The reason I don't like the donor category report is that the output is PDF and I have always needed data in a different format for publication so it does not save me any time at all to use this report.

Set up a Constituent Query for anyone who gave at least $1 during the fiscal year using the Summary Information | Summary for Gift criteria. Be sure to select the gift types you want included in that summary, because if you do not, Raiser's Edge will apply a default set which might not be what you want. Also include / exclude gifts to specific Campaigns, Funds or Appeals as needed, and determine whether or not you will need to include soft credits.

Once you feel your query logic is sound, set up an Export to generate a list with the final details you need - probably include Head of Household only, and then include / exclude Deceased, No Valid Address, and Inactive as needed. Include constituent ID, whatever name fields you need, and Summary Information | Summary Gift Amount.

Once this is exported to Excel, sort by the total giving and apply levels like your “$500-999” donors. I always use a letter in front of these, like “A: $1m+”, “B: $500,000 - $999,999”, etc. After you have assigned a giving level to everyone you can do a pivot report to show how many donors in each level, and how much they gave.

It feels like a lot of steps but if this is something you will do regularly, you can re-use the queries and exports by updating dates. More than anything though, if you are publicizing a list of donors for any public purpose, this allows you ample opportunity to carefully scrutinize data, which is a critical component to generating this kind of list. In my opinion, this should NOT be a push-button type of output that does not get careful review.

@Karen Diener @Patrick Craine For those who have the Standard Reports in web view, Donor Category Report is available there and easily exports to .csv.

@Patrick Craine You're been told the right way to handle this issue by @Karen Diener and @Alex Wong. However, those methods are a bit more advanced for someone who considers themselves a newbie. I think in this case, it might be worth looking at the Donor Category Report and just reporting on the numbers iff you think that would be enough. I would run it in webview like @Christine Robertson stated if you have access to them, but they are also available in DBV as a .PDF.

@Dariel Dixon - The WV Standard Reports section can export both CSV, PDF or both. No need to run this in DBV just to get a PDF output.

@Darlene LeVielle
When “Standard Reports” (canned report) becomes available, (it is in preview for some customer, which I am one of them), it can better export to csv and would likely be your best option.

before that, a constituent query with 10 summary of gift amount field/column for each of the 10 FY will be best option in my opinion in excel. (sample 3 FY below)

you can setup the Summary Table with the 14 gift amount bins in the rows, and then calculate the count and amount for each year. Once it is setup, updating simply means re-export and replace the above raw data file.

@Darlene LeVielle
maybe i'm not understanding your reporting purpose, and where “fund split amount” comes into play here. I thought you were trying to get donor category (how much donor give in a given period of time and put their giving into “bucket” of giving threshold).

If somehow what fund is given to matters to your report, then you will be better off with a gift query where you output fund and fund split amount and this amount is used within your formula.

THough depending on how many gift records are in your database for the last 10 fiscal years, Excel may not be your option (1M records row is the max) and even if you don't hit the max, few hundred thousands records row is going to need a very powerful CPU on your computer.

You will have better luck using Power Query/Power BI

@Darlene LeVielle
I'm still not sure why you need to use “fund split amount”.

if aggregating by constituent (you are putting how much a constituent give within a period of time into gift size bin), then there is no need to aggregate by fund split amount, you only need to aggregate by gift amount.

Reason why you want to avoid “fund split amount” in the data table and for aggregation is due to complexity that can be avoided. Since “fund split amount” will make the data table to possbiliy have dup rows for the same gift but different fund split, trying to get a distinct count will require you to do more advanced excel formula: dynamic array.

this post will not go into how you can do dynamic array formula, if you interested, there are planty of tutorial that can teach you.

Instead my suggestion will change (if fund split amount is needed for purpose of reporting also on fund split related info) to using Power Query, Power BI or Tableau directly at this point, instead of using excel.