I'm working on pulling together some information for our annual audit. One of the pieces requested is what our CFO is referring to as an "Audit Report" -- effectively she wants to see every transaction that touched campaigns/funds and who in the system processed that transaction. I swear that there is a standard report that would give me this information, but I can't for the life of me remember what it is. Any thoughts/suggestions?
Transactions by posting will give you the batch number and all the line by line trans. That's about as Audity as they get IMHO
Hi Maery,
Try the New Contributions report. It should allow you to see all contributions in between a given date range. It doesn't necessarily tell you who processed the transaction, but it does tell you the Solicitor if you guys have been entering that information.
Other than that, I can't think of a canned report that tells you who processed the transaction, but if you have access to SSMS, you should be able to pull that information with a simple query by looking at the "created by" column for all contributions within a contribution date range.
Hope that helps somewhat.
David
If you have SSMS, give this code I quickly scratched together a try (some tables may be different.) This code joins the contribution tables so if Finance wants ticketing as well, you will have to switch up to include the T_TICKET_HISTORY table.
I believe this will contain all of the information that your Finance department is looking for.
select cc.customer_no ,cu.lname ,c.description as campaign ,f.description as fund ,tr.transaction_no ,tr.batch_no ,tr.created_by ,tt.description as trn_type ,convert(varchar(10),tr.trn_dt,101) ,tr.trn_amtfrom lv_contcredit as cc (nolock) --you may also use T_CONTRIBUTION join t_transaction as tr (nolock) on cc.ref_no=tr.ref_no join t_campaign as c (nolock) on cc.campaign_no=c.campaign_no join t_fund as f (nolock) on cc.fund_no=f.fund_no join T_CUSTOMER as cu (nolock) on cc.customer_no=cu.customer_no join TR_TRANSACTION_TYPE as tt (nolock) on tr.trn_type=tt.idwhere tr.trn_dt between '2018-07-01' and '2019-06-30' --whatever your fiscal year dates are.
Thanks for this, Heath! Transactions by Posting was a good start for what I needed.
Unfortunately, we weren't entering Solicitor consistently until fairly recently, so that might help in the future! SSMS is where I ended up.
Mark! Thank you so much for this -- this got me almost exactly what I needed, and making the tweaks Finance needed was easy enough. They've only asked me for Contributions, but pulling in Ticket History shouldn't be hard if they change their mind.