How to - Reports for the Following

Kristie David

I am looking for an effective way to report on the following.

Every two weeks, I commit a batch as a result of payroll deduction monies that have come through our foundation. Not all of the constituents in the batch give to the same fund, however, when the money is transferred over to us, all of the money goes into one general bank account. Then, we need to take that money and transfer it into the correct bank accounts to which people designated their gifts. Therefore, I need that shows what funds these batches contain and how much money from the batch goes into each fund. For example:

Employee 1 gives $5 to the Annual Fund

Employee 2 gives $10 to the Annual Fund

Employee 3 gives $5 to the Building Fund

Employee 4 gives $5 to the Scholarship Fund

Employee 5 gives $10 to the Scholarship Fund

The report should list:

$15 to the Annual Fund

$5 to the Building Fund

$15 to the Scholarship Fund

Is there a way to do this using a canned report in RE or by any other means? Help!

Karen Stuhlfeier

I use fund queries for things like this and put a summary field in the output. This is one of those rare instances that I export directly from the query.

Kristie David

@Karen - Thanks for the tip. I am not sure this would work for what I am looking for exactly, but if you have time to ellaborate maybe I am missing something.

Alan French

You could create a query that prompts for the batch number, which you then feed into a pivot report and summarise the gift split amounts by fund.

Joy park

Yes, there is a Fund Performance Analysis report - under Campaigns, Funds and Appeals Reports.


If you need the actual donor names on the report, I am pretty sure you can do that under one of the Format tabs...

Fund Comparison report is a YOY report so you can report on "how much $ did we recieve LY for said Project vs this year?" That may also help you!

Christine Cooke 2

Kristie David:

I am looking for an effective way to report on the following.

Every two weeks, I commit a batch as a result of payroll deduction monies that have come through our foundation. Not all of the constituents in the batch give to the same fund, however, when the money is transferred over to us, all of the money goes into one general bank account. Then, we need to take that money and transfer it into the correct bank accounts to which people designated their gifts. Therefore, I need that shows what funds these batches contain and how much money from the batch goes into each fund. For example:

Employee 1 gives $5 to the Annual Fund

Employee 2 gives $10 to the Annual Fund

Employee 3 gives $5 to the Building Fund

Employee 4 gives $5 to the Scholarship Fund

Employee 5 gives $10 to the Scholarship Fund

The report should list:

$15 to the Annual Fund

$5 to the Building Fund

$15 to the Scholarship Fund

Is there a way to do this using a canned report in RE or by any other means? Help!

 

You can use the Gift Detail and Summary Report under Financial Reports.  If you just need to know who much goes into each fund, use the Summary, if you need to report who on each fund, then use the Detail page instead.  You can pull queries into the canned report by batch as you need.  or you can just pull for the month by date(s) and include the batch number on the detail of the report.

Gracie Schild

Hi - I tried to put a screen shot of Query Properties here in the text but I'm not clever enough, so I attached it. Our bookkeeper runs this query every month, and she exports it from Query and does a pivot table, then records the breakdown in the one deposit listing in QuickBooks - so much per account/fund. It's really quite easy.  And when I prepare the transfer from the Foundation to the college, that's based on the fund number, not on the gift-subtype or pay method, so they are mixed in by date within the fund, not by batch.


I've seen in another thread a great deal of discussion about exporting from Query or Export - and I have to say I'm surprised how many people are against using Query for exporting.  I recently HAD to use query, because I couldn't find the consecutive years of giving in Export (well-hidden, or just not there?).  I export from Query all the time because I seldom need anything that only Export can offer.  Of course, most of my lists are relatively short (fewer than 2000 constituents, sometimes with multiple lines per), so I can't speak for the monster databases of others.


My two cents!

Christine Cooke 2

Gracie Schild:

Hi - I tried to put a screen shot of Query Properties here in the text but I'm not clever enough, so I attached it. Our bookkeeper runs this query every month, and she exports it from Query and does a pivot table, then records the breakdown in the one deposit listing in QuickBooks - so much per account/fund. It's really quite easy.  And when I prepare the transfer from the Foundation to the college, that's based on the fund number, not on the gift-subtype or pay method, so they are mixed in by date within the fund, not by batch.


I've seen in another thread a great deal of discussion about exporting from Query or Export - and I have to say I'm surprised how many people are against using Query for exporting.  I recently HAD to use query, because I couldn't find the consecutive years of giving in Export (well-hidden, or just not there?).  I export from Query all the time because I seldom need anything that only Export can offer.  Of course, most of my lists are relatively short (fewer than 2000 constituents, sometimes with multiple lines per), so I can't speak for the monster databases of others.


My two cents!

Gracie -- Query is a grouping tool not an export tool.  That is why those of us that have been working in RE for a long time will repeat that mantra over and over again.


Because Query is a grouping tool -- if you export from it you will have multiple lines of the same constituent, you will have over counted and over totalled all of your numbers.  And you risk lots of human error trying to clean up a spreadsheet with thousands of dupes. 

Kristie David

Thanks everyone. The pivot table was the best option. I am so grateful for your time and answers. It saved me a good amount of time.