Report Designer Tables join

  • 7 March 2021
  • 9 replies
  • 540 views

Badge

hello
i tried to join batch and account which is not possible so i have to pass by gltran to get one field to filter with it branch.status
 

 

my problem now is that each line is duplicated many times 

i know the problem is with the join between LegalGneralBalance and GlTran

I tried to join with Account and i tried to group the result with AccountID Or AccountCD but i got the same results 


9 replies

Userlevel 7
Badge +17

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


 

Badge

hello

thank you for responding 

i tried so many combinations  with gltran , account and batch

i always get duplicated lines even i tried to group with accountcd in sql it works but in report designer no

Userlevel 7
Badge +17

Hi,

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

Badge

the only solution i got is to group again with account CD but its not working

 

i tried to get it done with SQL

select AccountCD from Account inner join GLTran on account.accountid=gltran.AccountID and account.companyid='3' 
left join Batch on GLTran.BatchNbr=batch.BatchNbr and GLTran.Module=Batch.BatchNbr and account.accountid=gltran.AccountID and batch.companyid='3'
 group by AccountCD order by AccountCD

 

the group by is working but i don’t know  report designer is not geting it

 

Userlevel 7
Badge +10

@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?

Badge

thank you Gabriel for you response 

but the client request is to put a filter with status in GL63CG00 

i found out that status is in batch 

the one to many  join is the problem and grouping is not working 

Userlevel 7
Badge +10

@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
Badge

@Gabriel Michaud 

thank you 

and i found the solution for that problem i only need to fix the total in the Footer cause it gives me the result of all the duplicated lines 

Userlevel 7
Badge +10

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.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved