Post by niallc on Wed Jan 28, 2015 8:30 am
I am new to Jet and to nav, so my needs are quite basic for now, im sure that will change!
in any event, we have circa 20 companies in our group, i want to extract the year end trial balances for all of these companies onto one spreadsheet, then i intend to use sumproduct formulae to collate the information on another tab,
For the life of me i cant seem to do it, i can get it to work by using the gl acc nos and an formula to looks ul that gl in each company, but the spreadsheet takes 20 min to update when i open and if i hit the design button it all goes haywire,
i appreciate i am making silly mistakes, but any advice gratefully received.
———————————————————–
Post by JET-btr on Wed Jan 28, 2015 10:30 am
I would do something like the attached. Of course you would probably want to set up some sort of date filter but without seeing your report, I'm creating just a basic concept.
Use NL rows to list out the GL Accounts from the GL Account table.
Use NL Columns to look at the Company table to replicate each company in a new column.
Use the GL Balance to look at the account number in that row and the company in that column.
If you are getting error messages, you may want to submit those to Support. Not sure what happens when you say it goes "haywire", if that means it crashes, that it gives an error message or does something else.
If this doesn't work, perhaps you can upload your report for us to look at. There may be a design flaw.
———————————————————–
Post by niallc on Wed Jan 28, 2015 10:39 am
thank you for replying
i should have said i have jet express, it says NL columns isnt supported?
i was using the attached only with all gls and all co's across the colums, but i just manually created this, and i assume its sub optimal?
Date
Votes
4 comments
-
Jet Reports Historic Posts It is not the optimal way to do it but it is the only way to do this with Express.
How many GL Accounts are you pulling? If you do just one company, does it still take a long time to run?
One thing to note is that you cannot put anything but Jet keywords in row 1 or column A, which you currently have. That could be causing the issue. In cell A1, you should have Auto+Hide+Values. Instead, you have "BS", as well as having "Start Date" in cell B1. -
Jet Reports Historic Posts so if i had jet essentials would it be much better for me trying to do something like this?
-
Jet Reports Historic Posts "Better" in the sense that you will have access to the Replicator functions which allow you to create a function to list out all the GL account numbers, another to list out each company and then pull the GL balance for each combination rather than have to manually type out each GL account and each company.
It should make the report run faster as well. -
Darryl Schroeder The file link "2014 Jet consol - eg-V2.xlsx" is not working. Is there any other way to retrieve this sample file?
Thank you.
Please sign in to leave a comment.