I am building a custom report using SQL to better report on the finances around our contributions. Is there an easy identifier between T_CONTRIBUTION and gl_account_no other than using the T_FUND and then using a CASE statement to select the correct GL Code from there based on other factors?
Hi Robert,
As others have pointed out, contributions and their associated payments (as well as all other money moving around Tessitura) draw their GLs through the T_TRANSACTION table during the batch posting process. The actual GL activity is aggregated into T_GL_POSTING_HISTORY. It is one of the most "advanced" areas of the database to explore and report on. So, I caution that custom GL reporting can be fairly complex and comes with some risk, primarily because a single contribution can go through many transactions in its lifetime. The help topics on GL Account Tracking for Contributions, GL Accounts and Funds, GL Accounts for Standard Payment Methods, and others in that section explain these movements that you would need to account for.
We at the Network would strongly recommend creating custom financial reporting in the Finance Cube of the Analytics business intelligence tool. That data cube is based on the T_TRANSACTION table and handles the logic to connect transactions to their GLs and contributions. You can isolate contributions from the other types of transactions if desired. Many aspects of the contributions, constituents, batches and postings are brought into the cube, so detailed reporting is definitely possible.
Best,
-Michael Flaherty-Wilcox
Director of Practice Management, Tessitura Network
Hi Michael,
You know I would have sworn that I tried Analytics early on--it was the first place I went--and it didn't work. But now that I go back, it appears to be working for the most part. At least for transactions that are contributions. However, for tickets purchased, it does not display the GL Code (though it does show the transaction amount, performance name, etc. Do you know why that is?
Glad to hear! GL codes are derived during the posting process, so it's most likely that you are seeing transactions that haven't been posted in Tessitura yet. You can confirm this by looking at the Post ID or Post Date fields.
-Michael
I see, so indeed they are definitely more recent transactions that are having this issue. Some of them don't have post numbers or dates which aligns with what you're saying, but some of them do. It is however exclusive to transactions processed in the last three or four days.
Ah OK. We have released a good number of defect fixes for the Finance cube in the last few service packs, so it's possible you are seeing an issue that would be resolved by applying those, but there could be something else at play here. I'm going to open a ticket on your behalf to see how we can resolve this.