Report Designer Tables join

Hi @khairi 

You can join the Batch and Account by using the GLTran table. Below SQL query may help you get the expected results.

SELECT * FROM Batch
INNER JOIN GLTran ON Batch.CompanyID=GLTran.CompanyID AND Batch.Module=GLTran.Module AND Batch.BatchNbr=GLTran.BatchNbr
INNER JOIN Account ON Batch.CompanyID=Account.CompanyID AND GLTran.AccountID = Account.AccountID
WHERE Batch.CompanyID=2

Hi,

Can you please share your SQL query which you included for all the tables?

@khairi what is the business requirement? I somehow suspect you’re trying to get a Trial Balance that can be filtered by Business Account, and if this is your goal then you’re not going to get acceptable results on the track you’re on… this is a summary report that is showing pre-aggregated balances by account and financial period, and it is not meant to be joined with GLTran and Batch the way you’re trying to.

My suggestion is to start with the business requirement. Can you draw a sample of how this report should look (in Excel) and share here?

@khairi thanks, that clarifies. I assume your client also wants to include Unposted batches in the report?

You will not be able to filter by Batch status in this report, simply because all the calculations are already done by the system -- the LedgerGeneralBalance*** tables contain the balances printed in this report, and the granularity level is not high enough.

There are two options I can suggest:

1. Use a tool like Velixo for your financial reporting -- there’s a function that lets you filter on the batch status (watch a demo here or see the IncludeUnposted argument here)
2. Create the report from scratch, using Account, GLTran and Batch tables only and doing all the aggregation and summarization yourself. Note: this report will be slow and might potentially time out if your GL has a large number of transactions

Fixing the footer will not work. It will just prevent multiplication of the total, but the numbers you’ll get from GLHistory definitely won’t be recalculated based on the batch status or ReferenceID you’ll select. Please verify the totals manually and you’ll see what I mean.