[ARice]

Hi,

I'm hoping someone can shoot me their ideas on some particular information I'm trying to come across. Basically, I want to see gifts that came in from donors in a particular state within this fiscal year (10/1/07 - 9/30/08) credited to one particular solicitor. I've tried a few ways and haven't been able to get a consistent set of data. This is how I initially set up my constituent query for export:

Gift Date greater than or equal to 10/1/07
Preferred State equals x
Assigned Solicitor Name equals y

I initially thought that a constituent query would be best because it will show me the solicitor's assigned constituents and their gifts (including soft credits) versus just showing all gifts credited to that solicitor with a gift query.

Will this show me the information I'm looking for? Any help is greatly appreciated!

Thanks!

[stancliff]

If you want to see all gifts, then I think you should run a gift query.

And if the gift is credited to a particular solicitor, would not the solicitor be listed on the gift?

Thus, my gifts by solicitor query prompts for Solicitor Specific Record, found under the Solicitors tree.

Does this help?

[ARice]

Thanks for your input Brian.

Thats what I was thinking as well, I'm just fairly new to RE and would like to make sure I'm doing what I need to get the correct information! I was using the constituent query because I wanted to see summary information, I'll give the gift query a shot and see what comes up.

One question - why would you want the query to prompt for Solicitor Specific Record? Would any of the criteria I listed not show me gifts that I may want included?

Thanks!

[Melissa Siobhan]

if you want to see summary information then the assigned solicitor should be a filter in the summary information - total amount field.

Use the field Total Amount (under summary information) and add to the criteria that it has to be greater than or equal to $.01. Then go to filters and add the date range you want and the solicitor assigned (this will be the solicitor added to the gift). This should give you everyone who has any gifts totaling at least a penny in the date range credited to that solicitor.

[stancliff]

Quote:

Originally Posted by Melissa Siobhan

if you want to see summary information then the assigned solicitor should be a filter in the summary information - total amount field.

Use the field Total Amount (under summary information) and add to the criteria that it has to be greater than or equal to $.01. Then go to filters and add the date range you want and the solicitor assigned (this will be the solicitor added to the gift). This should give you everyone who has any gifts totaling at least a penny in the date range credited to that solicitor.

Yes, you could do that too, but you can't have parameter prompts for gift summary fields.

Quote:

Originally Posted by ARice

One question - why would you want the query to prompt for Solicitor Specific Record? Would any of the criteria I listed not show me gifts that I may want included?

To answer this question, you stated that

Quote:

Originally Posted by ARice

I want to see gifts that came in from donors in a particular state within this fiscal year (10/1/07 - 9/30/08) credited to one particular solicitor.

Well, you could hard code the solicitors name in the query, or for flexability, you could set the parameter to <ask> and then run it for any solicitor.

I suggested a gift query since you said you wanted to see gifts. If you want to see summary information, yes, you could use a constituent query and use the summary information tree near the bottom of the query/available fields window.

[ARice]

Thanks to both of you for your input!

My ultimate goal is to set up a query (or queries, then merge them) and export that will allow me to see the performance of a solicitor among donors in a given state over a multi year period (say, the past 3 fiscal years). I want to set it up so that I can see the total giving for each donor each fiscal year in a column (similar to the Comparison canned report).

So how about this - in setting up the query, would it be best to use a constituent query and use Summary Information-Total Gift Amount to set the dates and solicitor, then just use Preferred Address-State? Theoretically, that should give me all donors who gave a gift in a particular state, are assigned to a particular solicitor, with any gift in the specified date range?

[stancliff]

In my orgnization, a constituent can be solicited by multiple persons. For example, donor Jon Smith could have been solicited by Phyllis Fundraiser for a gift in fy 2007, and could have been solicited by Frank Frontliner in fy 2006. These solicitors are listed on the gift level, so I think I might be do a gift query and then slice and dice my Excel spreadsheet using pivot tables or something. If I look up a constituent today, it is not unreasonable for five, six, or seven solicitors to be listed.

In my organization, I couldn't get what you are looking to analyze in a constituent query, because I am limited by the number of gifts I can return by constituent row. I have one row per constituent, with x number of gifts running across in the columns. That may not make sense unless you experiment with exports.

Quote:

Originally Posted by ARice

My ultimate goal is to set up a query (or queries, then merge them) and export that will allow me to see the performance of a solicitor among donors in a given state over a multi year period (say, the past 3 fiscal years).

Wouldn't this only be true if the same solicitor was assigned to this constituent for the past three years?

As an aside, there is a terrific function in RE that lets you export total giving by date range. So, you could opt just to get all constituent that have given over $x amount (and that amount could be one penny if you want all donors.) Then, what I would do in the export is set up three different summary giving columns, each with a different date range. If you open up a constituent export, expand the summary information tree, expand the Gifts tree, and select Total number of Gifts. You'll see that you are presented with a Gift Criteria box that let's you limit that export field by gift date, and other gift criteria. Very useful.