Hi again 8-)
I have this problem with one report. I have Trial balance and Detailed Trial balance reports created on jet reports but now I want a Trial balance report grouped by source no.
Several G/L accounts are used as control accounts for subsidiary ledgers (vendors, customer, banks). The entries made in these control accounts are registered under G/L entry table with a source type (vendor or customer or bank account) and a source no. indicating the card no.
The problem is I want this trial balance report to check if a G/L account has entries coming from subsidiary ledgers and split the balance to date based on the source no.
I can't do this in a trial balance report since I'm using G/L account table, where there is no source no. nor source type field to choose. I can do this in a detailed trial balance report using the G/L entry table but then I have the entries something I don't need/want.
Any help/tip will be appreciated :P
Date
Votes
11 comments
-
Jet Reports Historic Posts Finally did it. 8-)
You can use CTRL+r macro to copy only the related fields to the empty sheet.
Any feedback would be great. ;)
P.S: just remembered I have only used customer and vendor control accounts as per our client request. -
Jet Reports Historic Posts Did you try filtering the source that has the data for acct numbers and the running the TB only on those accounts the filter found ?
example
C3 NL("filter", "table", "acct", "source","Customer")
gives you all accounts where the source customer exists
Nl(rows,GLmast,, "Acct,C3) would give you only those accounts in the filter)
Whole lot easier then cut and paste -
Jet Reports Historic Posts vanjump, thanks for the tip ;)
That helped me figure a new way to create the report:
1 line: filter accounts from G/L entry with source type <>vendor and customer —> rows of G/L account filtered by the first filter
2 line: filter accounts from G/L entry with source type =vendor and customer —> rows of G/L account filtered by the first filter —> rows of source no. from G/L entry table filtered by account number from second filter
I think this will then list all G/L accounts with no source type and then list all G/L accounts with source type and their source no. as rows. The rest (LCY amount, amount etc) will be easy. ;)
Thanks!!! -
Jet Reports Historic Posts Now Ineed help to perfect the filter…. :oops:
=NL("Filter","G/L Entry","G/L Account No.","Source Type","<>Vendor|Customer") –> =NL("Rows","G/L Account",,"No.",$B7)
=NL("Filter","G/L Entry","G/L Account No.","Source Type","Vendor|Customer") –> =NL("Rows","G/L Account",,"No.",$B8)
Ideally the first line should filter and show only accounts with no source type and the second only accounts with source type. However, at least the first filter is showing all accounts…B7 and B8 are the fields used by the filtering Jfx.
Thanks! -
Jet Reports Historic Posts Just to make sure I'm following correctly
this is B7
=NL("Filter","G/L Entry","G/L Account No.","Source Type","<>Vendor|Customer") –>
This is actual cell in body Say F1 with G1 being and NF(F1, No.)
=NL("Rows","G/L Account",,"No.",$B7)
Your problem is that the same account(s) could be (and likely is ) in both sorts so at this point your going to have to get results back out of the detail by filtering again
So H1
NL("Sum","G/L/Entry", "Amnt", "Source Type","<>Vendor|Customer", GL Account, G1)
I1
NL("Sum","G/L/Entry", "Amnt", "Source Type","Vendor|Customer", GL Account, G1)
You could simply filter for all accounts used in G/L Entry and create two columns one with the above filter the second without the <> and if you wanted a third with no condition ( all)
and you can pull whatever you want out of G/L account as well
Hope this helps a little -
Jet Reports Historic Posts Restart discussion using
<>Vendor&<>Customer
instead of<>Vendor|Customer
Because (UNEQUALS Vendor) OR Customer returns all except the Vendor… -
Jet Reports Historic Posts Thank you both!
I will test these and let you know ;) -
Jet Reports Historic Posts So far…
Managed to get the same results from these two cells:
=NF($C7,"Net Change","Date Filter",Options!$D$14) -where $C7 reads the filter for G/L accounts with no source type in entries
=NL("Sum","G/L Entry","Amount",,,"G/L Account No.",$D7,"Posting Date",Options!$D$14,,,,,,,,,,,,,"company=",Options!$D$11) -where $D7 is the account number derived from an NF to the filter in $C7
It's important to know that my new formula has the same amounts like the one used for TB (with G/L account table and balances at date, net change, etc).
Now the problem is to convert the balances from LCY to Customer and Vendor Ledger transaction currency: -
Jet Reports Historic Posts OK guys…here is where I'm stuck… :(
I get some duplicate figures in both the first line and the second and some other figures differ a lot from the conversion of functional to foreign (transaction) currency.
Any help will be greatly appreciated -
Jet Reports Historic Posts Another try and this time I think I'm almost there!!!
However, I'm stuck with Excel characters/formula limitations within a single field :shock:
A few words on the latest report:
Target of 3 NL rows - first row exclude vendor and customer posting group accounts from NL G/L accounts. Next two rows only show (per line) vendor and then customer posting group accounts, axpanding them via source number (from G/L entry table). So the end result will be first row show all accounts with no vendor/customer control and next two lines the vendor and customer control accounts.
The problem is in cell D7 in Trial Balance sheet.
Please assist… :| -
Jet Reports Historic Posts New direction!
We came to a dead end so we had to rebuild the way this report would work. So instead of working it in Jet decided to see how Navision may do the job for us.
Our solution is to run a report with all Vendor and Customer posting group accounts in the database, per company. These results will then be used to add the reconciliation check mark in chart of accounts (we don't use this field :) ) and then filter:
NL(Rows,G/L account,No.,Reconciliation account,0)
NL(Rows,Vendor posting group, payables)
NL(Rows,Customer posting group,recievables)
Thanks for your patience and sorry for the repetitive posts :oops:
Please sign in to leave a comment.