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
Michael Flaherty-Wilcox said: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.
Yes, "advanced" as in "frustrating"., i.e. T_GL_POSTING_HISTORY contains the summary data only, and the classic "Reprint Posting Report" queries that. Our Finance department repeatedly asked "what is the detail for this line?", and much custom reporting ensued, mostly years ago.
Michael Flaherty-Wilcox said:Many aspects of the contributions, constituents, batches and postings are brought into the cube, so detailed reporting is definitely possible.
Kind of sad that we have to pull data that is in the impresario database, out of that database, in order to report on it. *sigh*
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 understand what you mean. For what it's worth to others reading this thread, our usual response to your finance department's question is that you can run the Transactions by Posting Report for a given posting, and as the name implies, it will show you the transactions grouped under each GL in the posting. But sometimes this report doesn't contain fields that everyone needs.
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.
Michael Flaherty-Wilcox I feel the need to jump in here with one important point that I think is a functionality gap in Tessitura that results from the ability to backdate postings. I submitted this as an enhancement request on ticket 76707 just over a year ago. TL;DR: We need to add a "wall clock" posting timestamp column to T_BATCH in order to be able to trace transactions to GLs.
The analysis is as follows:
---
T_GL_ACCOUNT_HIST stores a record of all GL configurations at every point in time. In theory, this could be useful for determining where a specific transaction was posted to by tracing from the transaction to the batch it occurred in, and then joining on T_GL_ACCOUNT_HIST using the batch's posted date (because the batch post date is the point at which a transaction is locked to a GL). However, if a batch posting is backdated, the posted_dt becomes an arbitrary user selection, and can no longer be used to trace the GL configuration at the time of posting. If an additional column (perhaps posted_timestamp?) was added to T_BATCH that stored a datetime value of the actual wall clock time that the batch was posted, this could be used to join on T_GL_ACCOUNT_HIST to definitively trace every transaction to the GL that it affected. For an example business case, I have previously needed to direct a batch of payments to different GLs from the standard GL for that payment method. So, I would change the GL on the payment method, do the posting (with a backdate), and then change the GL back. These GL changes appear in T_GL_ACCOUNT_HIST just seconds or minutes apart from each other. If the posting was timestamped, I could go back and reconstruct these payment GLs very easily, but since the posting was backdated, the posted_dt has nothing to do with the relevant start_dt and end_dt in T_GL_ACCOUNT_HIST.
There are currently a few canned reports in the system that attempt to do the impossible, which is to reconstruct which GL a transaction should be associated with based on the T_GL_ACCOUNT_HIST table. These are the Transactions by Posting report and the Performance Transaction Detail report.
In an environment where backdated postings are routine, it is very easy to get into a situation where these reports will NOT reconcile with something like the GL Summary Report. This can be frustrating because these reports are some of the only tools for digging into the detail behind the GL Summary report. The causing issue is that trying to match up a transaction to a GL number based on T_GL_ACCOUNT_HIST actually gives you no guarantees that the transaction was posted to the GL that was configured at the time the transaction was made.
Only the real time that the batch was posted determines when a transaction will be locked to a GL in T_GL_ACCOUNT_HIST. If it was not possible to backdate a posting, then the existing schema would work fine, but since backdating is possible, it is required to save the real time the batch was posted if the actual GL for a transaction is to be determined.
This behavior is noted in the documentation for the Transactions by Posting report as follows:
Note: If you have changed the GL on transactions in a batch and then backdate the post to a date prior to the GL change, the Transactions by Posting and the Performance Transaction Detail reports will reflect the old GL for transactions posted in the batch while the GL Summary report will reflect the changed GL for transactions posted in the batch.
And it's true that correcting for this just isn't possible without a schema change. But I suspect that it hasn't been recognized that fixing the schema would be as easy as adding a single column to T_BATCH, and that that would have such an outsized impact on our ability to do financial reporting.
Hi Nick! Thanks for this suggestion, both in your original ticket and here in the forums. I passed along the feedback on this thread to the Product and Development teams, and they are reviewing it for inclusion in a post-v16 release.