New to Queries and reports - can this be done?

Bahaiyyih Pride

Hello - I have never run a query before and need to generate a report for someone in my office containing the following:


"Attached are documents with names of board, committee members and docents and guides (in excel and word). Could you create an excel document with their names, history of giving shown by year, date of last gift amount and total giving? Please include their spouse’s giving as well. For instance, a husband's giving will also be reflected in the giving of his wife.  Also if there is a corporation associated with their name- please add that giving as well."


So my questions are:

1. Can I generate a report in RE to do this?
2. Can anyone walk me through this process or show me where to find the pertinent guides to do so?

I have every confidence that the information I have been provided in her documents already exists in our RE database so I just need to know how  go about gathering it there for her purposes.


Thanks for any help you can offer!

Robin Richardson

Oh yes! RE can definitely do this. Their support staff is wonderful and will be able to guide you every step of the way. I'd reach out to them, if I were you (which I was years ago and they were a great help to me, too)!!

Faith Murray

Bahaiyyih, the short answer is yes, it's very possible to do that in RE. I'm more familiar with using Export for this type of complicated information, but it should conceivably be possible to create a report to pull the same information as long as you are familiar with Custom Crystal reports and Pivot Reports.


That being said, it would be hard for any of us (on the forums) to give you step-by-step instructions without knowing how your database is set up at your organization. For instance, as far as including the spouse's giving and corporate giving, much will depend on whether your organization uses soft credits; links organizations to the individual through a Relationship record, Attribute, or other mechanism; or whether you add spouses as unique Constituent records. Robin's advice of asking support staff to assist would enable someone to actually look at your data, see how it is set up, and guide you through the process that way more adeptly.


That being said, at a rough guess, I would suggest you 1) create a Query to idenify your Board Members, spouses, and affiliated corporations; 2) create an Export to output total giving (Summary information), Last Gift Date, and Relationship info. Year-by-year giving would require using a Report, or else if doing it all in Export, re-exporting the Export summary field multiple times with different year criteria. You may have to do some manual editing if your data is not entered in your database in an efficient manner for this type of relationship linking.

shani traum

Bahaiyyih Pride:

Hello - I have never run a query before and need to generate a report for someone in my office containing the following:


"Attached are documents with names of board, committee members and docents and guides (in excel and word). Could you create an excel document with their names, history of giving shown by year, date of last gift amount and total giving? Please include their spouse’s giving as well. For instance, a husband's giving will also be reflected in the giving of his wife.  Also if there is a corporation associated with their name- please add that giving as well."


So my questions are:

1. Can I generate a report in RE to do this?
2. Can anyone walk me through this process or show me where to find the pertinent guides to do so?

I have every confidence that the information I have been provided in her documents already exists in our RE database so I just need to know how  go about gathering it there for her purposes.


Thanks for any help you can offer!

 

Certainly doable, to a point.


First query those you want as your group (members of the assorted list you mentioned).


On the export, include summary giving for the individual and for the spouse for each category you want (total giving, FY15, FY16, etc).  You can set up multiple summaries, each with different filters (date ranges, funds, etc).  And Summary is available for spouse as well.  In the EXCEL sheet exported, you can then add constit summary to the  corresponding spouse summary to come up with household totals.  You can export last gift for the constit, but not for the spouse, very annoying, but alas true.


The only way I have figured out around this is to create an initial export of your group members listed their and their spouses' Constit IDs, then repulling a query of all the IDs listed.  Now that you're pulling constit info, you can get that "last gift" piece.  You can then sort by name/address to be sure spouse are adjacent, see who has the most recent gift, and have that be what appears on the group member's row.  Once this has been done, you can delete out the spouses' rows.


   A                      B                   C               D                    E                    F                     G                 H                      I

Name               FY16             FY15        Lifetime        Last Date      Last Gift         Sp FY16        SP FY15        SP Lifetime        

Smith, Jon         $200             $100        $700              12/15/14         $50                $150            $250                $850

Smith, Jane       $150             $250        $850               1/1/16            $225              $200            $100                $700


In Column J, create a formula adding B and G.  In Column K, add C and H.  In Column L, add D and I.  You now have household totals.


Le's say Jon is your "Leader".  You can see has the most recent gift, so copy her last gift info onto his line and delete her row.  You now have household last gift on listed for the Leader's houshold.


I'm not saying this is an elegant way of doing it, but this is how we end up pulling this data.


As for Corp giving, the only way I could see doing this is to pull the org information separately, and tacking it on after the fact.


Hopefully this made some sense!


Shani