Hi!  My Outreach Coordinator wants to show our area committees giving in each state by year for the last 18 years.  Well, I started out with a simple total giving by state query but the Crystal reports database would pull the yearly data. 

So, I tried to pull it in the export only but it was going to take 12 hours.  But when I tried to pull a parameter of state and then each year's total giving in a query - it just hangs up.

It seems to be too much for RE to handle.  Any thoughts?!?  Am I over-thinking or under-thinking this?  Or just looking at the wrong way altogether?  I'd appreciate any help.

It will be a starting point when she finally gives me the state/county data for the individual committes to pull the same data for them. :o)  Luckily, most of them haven't been around that long.

Denise

There is no good way to do this, anything you do in order to make it accurate will take potentially days to process.  I don't even want to think about the complexity of scripting for all the cross referencing of old address records of people based on the gift date of every gift before summing them to assign them correctly to state where the person lived at the given time instead of just using current address. (RE does not store an address reference with the gift)

I would use a canned report under Demographical and statistical reports to run the states for each year (You can only do one year at a time - but with this kind of report you would only have to do this once a year after you have your history created) and perhaps build an excel spreadsheet with the data so you can manipulate the data for charts, graphs, etc.

 

My layout would be Year as column and State as Row - but you could set up anyway you wanted.

 

You can run the report on date range as well if you just wanted all dates per state.

I agree it is almost impossible unless your gift information is closely associated with address information, which I doubt it is.  Otherwise, if a constituent gave five times over the last 10 years, and he move twice, how do you know which gifts were made, let say, while he was in NY, then in California?  Unless you were tracking the state as a gift attribute or have a fund per state, or something like that.    So the first question is which field do you use to pull the state?

I didn't have the complexity of 50 states but I was once asked to do this for our 8 counties.  I soon realized that we would have to add something to the gift at the time it was made to track where they lived at the time of the gift. 

What we decided on was 8 campaigns - this way we could set a goal for each county and then track giving toward that campaign.  We decided to only do the current FY and we were only a few months in at the time so we were able to fairly easily change those gifts.  I would have cried if the wanted me to go back further in time to factor in moves.

You may not want to go the campaign route with 50 state but you could use gift code or an attribut on gifts that you enter when the gift is made.