Aggregate a gift query in a report Only by FY showing 2 columns: total $'s and total # of Gifts

Question

Hi all, I’m looking for help creating a very basic and simple aggregation report based on a gift query (Report needs to be able to use a gift query as I am needing the criteria to use a fund attribute/custom field).   It should display 2 columns only: 1. total $ giving and 2. total number of gifts by Only the 2 fiscal years I enter in the report config.  Is this possible in any Report that uses a Gift Query.  Thanks in advance!

Darlene LeVielle · Answer

@Alex Wong Thanks, that’s essentially what I did. I used the Fund Query as you suggested, then brought the results into Power Pivot to aggregate by fiscal year and maintain Fund-level detail. Power Query can made it easy to generate a fiscal year dimension, keep a clean star schema, and drill from Fund aggregates to transaction-level data.

Alex Wong · Answer

You can use Fund Query like you mentioned, and in the Total Number of Gift and Total Amount of Gift, you can create mutiple output columns each filtering by gift date to give you columns by fiscal year. if you need "rows" by fiscal year, then some excel work.

Amy Dana · Answer

Hopefully the export function will be up and running soon in webview, because that way you can replicate the exact data in your query but not have all the duplicates. (and yes, I feel your pain about not having the filters you need on dashboards)