Report listing campaign giving summary for several campaigns broken down into 5 subcategories

shani traum

I'm trying to create a report/export that will give me total given to a number of campaigns and then splitting that giving into sub categories based on funds - but I can't find a graceful way of doing so.



The report should be a grid listing total giving to the campaign known as 2014 Annual Fund, total giving to 2015 Annual Fund, and total giving to 2016 Annual Fund.



Under each of those totals, I want it broken into 5 subtotals, based on a grouping of funds that feed into the campaign.



The only way I have found to create this is to export straight from a campaign query (as I can't find summary giving for a campaign within an export) into EXCEL and then reformatting/moving around the info into the layout I want.



Thoughts on an easier way of doing this?



Thanks!

JoAnn Strommen

Have you looked at Reports > Campaigns,Funds & Appeals Reports > Campaign Goal Summary?  You'd have to run separately for each campaign but it would give you break out for all the selected funds and/or appeals?  Data can be exported.



Just a thought...

shani traum

I've used the canned reports to double check the info I'm getting on the query, but I'm trying to find a single report/export to pull all the info at one time.  The goal is to give our Annual Fund team a report they can run at will, so the easier, the better.  But thanks!

Alan French

Would a pivot report work, if you just need a grid? You could probably use the query you've already created, and you can export the results to Excel if you need to do anything else with it.

James Andrews

Alan French:

Would a pivot report work, if you just need a grid? You could probably use the query you've already created, and you can export the results to Excel if you need to do anything else with it.

Agreed, although personally I'd skip the pivot report and just export the data to Excel and do a pivot table there.

Rachel Rosen

I would definitely suggest a Crystal Report for this. While our canned reports will pull the information you need one by one, a Crystal Report allows you to create custom reporting so your colleagues can easily pull all the data they need in one shot. 

shani traum

Ahhh - but first I need to figure out how to use write crystal reports!  I may tackle that as a future project.  For now, I created two queries - one for Full year totals and one for YTD totals.  Each has 28 summary fields - thus why I split it into 2 reports!



Once exported, the info is pasted into a pre-existing, macro enabled EXCEL sheet.  Then they run the macro I created, and all the moving around, summing, and formatting happens as if by magic.



But I will have to experiment with Crystal report to see if I can figure out how to use that instead.

James Andrews

shani traum:

Ahhh - but first I need to figure out how to use write crystal reports!  I may tackle that as a future project.  For now, I created two queries - one for Full year totals and one for YTD totals.  Each has 28 summary fields - thus why I split it into 2 reports!



Once exported, the info is pasted into a pre-existing, macro enabled EXCEL sheet.  Then they run the macro I created, and all the moving around, summing, and formatting happens as if by magic.



But I will have to experiment with Crystal report to see if I can figure out how to use that instead.

A crystal report will run directly out of RE, probably, and therefore could be run with basically one click by your users.  I recommed, though, skipping the summary fields in the export, and rather exporting all the gifts with their pertinent information.  Then use whatever reporting software you're using, whether excel, access or crystal, to categorize and sum them.  Your exports will go infinitely faster if RE is just dumping data out rather than calculating it.



If you do stick with excel and VBA, one thing our financial analyst taught me about situations like this is that you can set up a VBA template that acts as a go-between from your exported data to the final report.  So you export to one excel workbook.  Then your template is the macro-enabled workbook, and it reads the data from the workbook you just exported, does calculations, etc, then writes the data out to a third workbook.  That way your exported data is never changed and can be overwritten as needed, and also in the final report there isn't any VBA to confuse your end users.

Christine Cooke 2

What if you used the Dashboard to make a Fund Summary and a Campaign Summary and they can compare between the two.  And if needed they can copy the table/dashboard and drop it in Excel?



Personally, I export the Funds I want to report on and then once it's exported, sort by Campaign and subtotal each of those.  That is the quickest way I know to do it wthout building something custom.