How to query on total number of constituents for each appeal in Raiser's Edge

Catherine Han · August 2016

Hello:

I need to have total number of constituent for each appeal. The reason I don't use appeal performance report is that it doen't meet our needs. I want to use query or export

Any ideas?

Catherine

Alan French · August 2016

If you just need numbers, you could create a query with the criterion "Assigned Import ID not blank" and then use that as the basis for a pivot report. Depending on what output fields you choose, you could then sub-divide each appeal by response or another field.

Karen Stuhlfeier · August 2016

In a query you would just have the criteria: Constituent Information - Assigned appeals - Appeal ID =

That will find everyone who has that appeal assigned to them.

Catherine Han · August 2016

Thanks for your quick response. I don't need total donor # for assigned appeal. I need total donor # who gave the appeals

If donor gives cash + gift in kind, then count it as 1 donor, not 2 donors. Goal and cost applies only to appeals, not package.

Total donor # for the appeal Total donation $ for the appeal Total donation gift in kind for the appeal Appeal Goal Apeeal cost

Appeal

Appeal1

Package1

Package2

Appeal2

Package1

Package2

because in appeal performance report, cash and gift in kind cannot be split into two columns, that's why we need to generate report by our own. I did pivot table, it looks like donor # is not completely. Sometime gift # appears in donor # area.

Jen Claudy · August 2016

Assuming you assign Appeals to the Gifts, then you could use a Constituent Query with this Criteria:

Assigned Appeal ID equals XXXXX {Assigned Appeals > Appeal ID}

AND Appeal ID equals XXXXX {Gifts > Appeals > Appeal ID}

You can add Assigned Appeal and/or Gift fields to the Output, and you can change the Operator from [equals] to [one of] if needed.

Faith Murray · August 2016

Just to clarify, if you are only wanting people who donated to the Appeal, and not all donors who received the Appeal, you can do this with a Constituent Query that pulls on the Gift Appeal. I think this is what Jen meant. Rather than pulling from the "Assigned Appeals" section in Constituent Query, you would want to pull from "Gifts" -- "Appeals" -- "Appeal ID" equals. Then in Output, select Constituent ID and a Summary field -- Summary for Gift -- Total Amount of Gifts -- Appeal equals (Appeal ID) and Gift type one of Cash/Pledge (or whatever). Then select Total Amount of Gifts a second time and apply different filters where Appeal equals (Appeal) and Gift Type equals Gift in Kind.

Provided that you only include the one Criteria and the three Output fields above, and only query on one Appeal at a time, your Query should be clean enough for you to export directly from the Query without duplication, which solves the problem in Export where identical Summary fields have to share the same criteria.

Catherine Han · August 2016

I see. I need to manually fill the form after I run query for all appeals, thanks.

I need to automatically generate most of # in the form, and can renewal the form time to time.

Beth Jarrett · August 2016

Faith Murray:

Just to clarify, if you are only wanting people who donated to the Appeal, and not all donors who received the Appeal, you can do this with a Constituent Query that pulls on the Gift Appeal. I think this is what Jen meant. Rather than pulling from the "Assigned Appeals" section in Constituent Query, you would want to pull from "Gifts" -- "Appeals" -- "Appeal ID" equals. Then in Output, select Constituent ID and a Summary field -- Summary for Gift -- Total Amount of Gifts -- Appeal equals (Appeal ID) and Gift type one of Cash/Pledge (or whatever). Then select Total Amount of Gifts a second time and apply different filters where Appeal equals (Appeal) and Gift Type equals Gift in Kind.

Provided that you only include the one Criteria and the three Output fields above, and only query on one Appeal at a time, your Query should be clean enough for you to export directly from the Query without duplication, which solves the problem in Export where identical Summary fields have to share the same criteria.

Faith--This was a great explanation of how to pull this sort of information. I tried it exactly how you said and it worked for all but my recurring gifts that go to the appeal. If someone pays regularly and it is attributed to that appeal, how would you get their info to pull only once into the Query? Or do you have to use Export if you have multiple gifts? It was easy to spot on an appeal that had less activity--I just want to make sure I don't have duplicated results in the future. Thanks!

Christine Cooke 2 · August 2016

Catherine Han:

Hello:

I need to have total number of constituent for each appeal. The reason I don't use appeal performance report is that it doen't meet our needs. I want to use query or export

Any ideas?

Catherine

Have you tried the Demographic and Statistical Report -- Comparisons and Summaries Report?

Faith Murray · August 2016

Beth Jarrett:

Faith--This was a great explanation of how to pull this sort of information. I tried it exactly how you said and it worked for all but my recurring gifts that go to the appeal. If someone pays regularly and it is attributed to that appeal, how would you get their info to pull only once into the Query? Or do you have to use Export if you have multiple gifts? It was easy to spot on an appeal that had less activity--I just want to make sure I don't have duplicated results in the future. Thanks!

You're right, Beth - I had tested this with cash and pledges but not recurring gift payments in my original post. You would need an Export to eliminate duplication if you pull a Summary field. That was a rookie mistake on my part, and a good catch on yours - I must have been tired last Friday.

In fact, now that it's no longer the end of a long week, I would suggest pulling this information through a Gift Detail and Summary Report. You can still only pull one Fund at a time (multiple Appeals are okay as long as they are tied to totally different Funds), but unlike what Catherine was complaining about the Appeal Performance Report, the Gift Detail report CAN split the gift-in-kind into a separate column from the cash, pledges, and recurring gifts. Just select the appropriate Appeal under Filters, ask for a Summary report rather than a Detail report, and under Gift Types, place the correct gift types into separate columns. The report will tell how many gifts and how many donors. The number of donors should match your Query for a double-check as long as the appeal and gift type filters are the same. We always use a Gift Detail & Summary Report for our Appeal progress reports, but we don't usually include Gift-in-Kind reporting since we credit them with a $0 gift amount.

Catherine, yes, there is no way to include the Appeal Goal information in the same query or export as your donor info. So you will need to fill in form manually, or else try Alan's pivot report idea, or a Custom report. Our org has never used Pivot reports, so I'm not sure what the limits of its capabilities are.

Beth Jarrett · August 2016

Thank you! This was very helpful! If anyone is a rookie, it's me--thank you for your help! The fine-tuning of reporting is really a challenge! Al the suggestions, including the one about the Statistical Reports have been extremely helpful. Great to have a community to work with! - Beth

Faith Murray · August 2016

Beth, try that Query again, but this time, go to Tools -- Query Options -- Record Processing, and check the little box that says "Suppress Duplicate Rows". Tell me if your duplicates are still there. Just found this button today.

Beth Jarrett · August 2016

Faith –

Thank you for the suggestion – I tried it and
unfortunately still had duplicates. Actually, I have pushed that
button many times before to no avail! I think they put that there
to make us feel like we are doing something lol!

I appreciate comments from you and other
community members. You get to a point where the chat support does
not really have the ability to get to nit-picky things. They are
helpful in many ways, but the users know the ins and outs!

Thanks and have a great day!

Beth T.
Jarrett

Administrative
Assistant

Georgia 4-H
Foundation

706-542-8914

bethjarr@uga.edu

4hfndtn@uga.edu

Brenda Heyer · August 2016

Hi Beth,

I tried a test query and did not receive any duplicates for the monthly recurring gift pay-cash towards an appeal. I'm not sure which button you refer to hitting over and over again, but if you would you like further information let me know?

Brenda

Beth Jarrett:

Faith –

Thank you for the suggestion – I tried it and unfortunately still had duplicates. Actually, I have pushed that button many times before to no avail! I think they put that there to make us feel like we are doing something lol!

I appreciate comments from you and other community members. You get to a point where the chat support does not really have the ability to get to nit-picky things. They are helpful in many ways, but the users know the ins and outs!

Thanks and have a great day!

Beth T. Jarrett

Administrative Assistant

Georgia 4-H Foundation

706-542-8914

bethjarr@uga.edu

4hfndtn@uga.edu

Beth Jarrett · August 2016

Brenda—

Faith had suggested going into the Tools-Query
Options-Record Processing and checking the “suppress duplicate
rows” box. I am pretty sure I have tried that before. I would be
interested to hear how you set up your query. All the other gifts
worked for me except the recurring ones.

Thank you!!

Beth

Brenda Heyer · August 2016

Beth Jarrett:

Brenda—

Faith had suggested going into the Tools-Query Options-Record Processing and checking the “suppress duplicate rows” box. I am pretty sure I have tried that before. I would be interested to hear how you set up your query. All the other gifts worked for me except the recurring ones.

Thank you!!

Beth

Beth, I will reach out to you tomorrow as I am leaving now for the day.

Brenda

Faith Murray · August 2016

I am surprised that it still had duplicates. I tried a test query and that eliminated all my duplicates from recurring gift payments. I did read on the Knowledgebase that the "Suppress Duplicate Rows" box only works if your Output fields are one-to-one fields.... that is, an output field such as Constituent ID or Total Giving, where each donor only has one answer, as opposed to an Address line where a donor may have mulitple addresses on file. But as long as the query only has the three fields I suggested above, all of those fields should be one-to-one fields. .... ?

How to query on total number of constituents for each appeal in Raiser's Edge

Comments

Categories