Ok, I have to be overthinking this or something because I simply can't get a report to run for a detailed trial balance. What I need is a trial balance with the following columns so that our auditors can import the information into their software:
Account Number
Fund
Cost Center (Global Dimension 1)
Debits
Credits
Balance
We want a separate row for each combination of Account Number, Fund, and Global Dimension 1, but we only want that combination to show up if there is activity for it over the specified time period. Doesn't that mean I have to return information based on the GL Entry table?
Unfortunately, I have a problem with memory use because I have a 32 bit version of Windows 7/Office 2010, so if I set it up the way I think it should be (attached, but not pretty), it spins for about 20 minutes, runs out of memory, and restarts excel. Very frustrating. :|
Does anyone have a better solution or have any idea how to make this work more efficiently?
We are running NAV 5.0 SP1
Any suggestions are appreciated!
Date
Votes
17 comments
-
Jet Reports Historic Posts Hi!
I think you might be able to do what you're trying to do using Link=. If you only want to see accounts that actually have activity in the specified period then you could create a formula in C5 that looks like this:=NL("Rows","G/L Account",,"Account Type","Posting","Income/Balance","Balance Sheet","No.","10000..11111","Link=","G/L Entry","G/L Account No.","=No.","Posting Date","..06/30/2012")
This will only replicate accounts which have activity in the specified posting date range. Then in cells D5, E5, G5 and you can just use NF formulas like this:=NF(C5,"No.") =NF(C5,"Name") =NF(C5,"Global Dimension 1 Code")
There are a couple questions here. Are there multiple different Global Dimension 1 Code values in the G/L Entry for each account or is there just one value for each account? Assuming there is just one value for each account, you can use the NF formula like I did above.
The other question is Fund No. I'm not sure if that field exists on the G/L Account table or only on the G/L Entry table. If it's on the G/L Account table and there's only a single fund number for each G/L Account, you can just just an NF formula like I did with the other fields above. If there are multiple fund numbers for each account, depending on the entry, then you might have to use your rows replicator.
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts First, thanks so much for the response, I will try your suggestion for C5 and let you know if that seems to help, although it seems like the place that the report is stalling out and dying is during F5 expansion…I'm not sure it ever gets to G5 expansion.
Second, unfortunately for each G/L Account there may be multiple combinations of Global Dimension 1 and Fund values.
There is a Fund Filter field in the G/L Account Table, but there may be instances where more than one Fund will be used in a single account. That means I probably do have to use a rows replicator - same for the Global Dimension, correct? -
Jet Reports Historic Posts There are a couple reasons the formula in C5 might be helpful. For one, you will only get the account numbers that have entries in your posting date range. If you actually expected that your account numbers ALL have entries for this date range, then you could leave out the Link= part. The other reason this type of formula is helpful is that there is an optimization for NF formulas in Jet (at least in Jet 2012, not sure the first version it appeared in) where it will only perform 1 query for the entire column of NF formulas rather than doing separate queries for each formula. My formulas would take advantage of this optimization, at least for account number and name.
So given what you said, there are several possibilities. My first thought would be to actually change the C5 replicator to replicate all the unique combinations of G/L Account No., Fund No., and Global Dimension 1 Code directly from the G/L Entry table like this:=NL("Rows","G/L Entry",{"G/L Account No.","Fund No.","Global Dimension 1 Code"},"G/L Account No.","10000..11111","Posting Date","..06/30/2012","Link=","G/L Account","No.","=G/L Account No.","Account Type","Posting","Income/Balance","Balance Sheet")
I had to use Link= to link from the G/L Entry to the G/L Account number to filter for the Account Type and Income/Balance fields, assuming that those 2 filters are both necessary and not available directly on the G/L Entry. If either of those assumptions is not true, you could remove the Link=. Then the values in D5, F5, and G5 should be changed to NF functions and you could keep your NL(First) function in cell E5. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts And I thank you again, sir! Yes, that helps. Unfortunately I still have the 32-bit related memory limitations, so if I use your method for my full chart of accounts, it runs out of memory and dies, but I'm attempting to limit it to specific accounts to see if that works better.
Do you have any other suggestions to make this report less memory intensive? -
Jet Reports Historic Posts Incredibly bizarre. The formula you provided works great if I use the G/L Account No. filter that you used. As soon as I try to expand that range to be "10000..19999" it returns #VALUE errors in every cell except D5. Looking at the log, it looks like it is somehow returning an empty filter somewhere, which is bizarre since it works with the smaller account range just fine. :shock:
Any idea what could be causing that to happen? -
Jet Reports Historic Posts So the answer on the memory usage is maybe. The other option I was thinking about for this report would be to replicate the G/L accounts, global dimensions, and funds from separate tables and use Link= to link into the G/L Entry table. I'm not positive whether this would be less memory intensive, but it might. So using this approach we go back to replicating the G/L Accounts from the G/L Account table like this:
=NL("Rows","G/L Account",,"Account Type","Posting","Income/Balance","Balance Sheet","No.","10000..11111","Link=","G/L Entry","G/L Account No.","=No.","Posting Date","..06/30/2012")
Then we would replicate the Global dimensions from the Dimension Value table like this:=NL("Rows","Dimension Value","Code","Global Dimension No.","1","Link=","G/L Entry","Global Dimension 1 Code","=Code","G/L Account No.",D5,"Posting Date","..06/30/2012")
Then you could probably do something very similar for Fund number although I don't know what table that would be since it's not standard Navision (I'm guessing probably the Fund table). If it were the Fund table, it might look like this:=NL("Rows","Fund","No.","Link=","G/L Entry","Fund No.","=No.","G/L Account No.",D5,"Posting Date","..06/30/2012")
Does this help with the memory usage?
Regards,
Hughes -
Jet Reports Historic Posts As far as your #VALUE error, that sounds like you changed the filter to a cell reference (like on an options sheet or at the top of the report) but didn't make it an absolute cell referece (such as $B$2) so the cell reference is being updated by Excel on each line and is thus referring to an empty cell on all rows other than the first one. Could something like this be happening?
Regards,
Hughes -
Jet Reports Historic Posts Actually, I wish I had changed it to a cell reference, that would make it easy to diagnose…but I did not. I actually changed the hard coded filter. I don't like to set up the options until I have a working hard-coded report in cases such as this.
I will try the Link suggestions and get back to you as to whether it affected the memory usage or not.
Yes, the Fund table is its own table. I believe it is part of Serenic Navigator, though, not (as you said) base NAV. I'd have to look at the table numbering to tell, but I'd guess that is the case.
Thanks again for helping with this, if we can get this working I will be a very happy camper. -
Jet Reports Historic Posts Well, by changing to your Link= formulas I went from 20 minutes of spinning resulting in a crash to an actual report in under 2 minutes! The only thing it's not doing now is pulling in lines that have no cost centers (while this should never happen, it does occasionally occur). I have been unable to figure out where to place a filter to tell it to include blanks ("@@" is usually what I use to include them in a filter).
Also, do you have any suggestions as to where I might learn more about using Link in formulas?
Thank you so much for all your help! -
Jet Reports Historic Posts Hi,
Hey great news on the performance front there. I'm not sure I understand what you mean by cost centers. Sorry, I'm not really an expert in accounting. Do you want to see accounts which don't have any entries in the G/L Entry table, is that the issue? Or is there somewhere else you need to filter by Cost Center explicitly? You could view all accounts in your range and not filter for only ones with entries in the G/L Entry by removing the Link= from my NL(Rows) formula pulling in accounts like this:=NL("Rows","G/L Account",,"Account Type","Posting","Income/Balance","Balance Sheet","No.","10000..11111")
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Oh, no. Not messing with that one! LOL :) I forget that you don't read minds and don't know that Cost Centers = Global Dimension 1 for me. :)
What I'm wanting to do is return lines for accounts that have activity associated with a "missing" or blank Global Dimension 1 value. Currently, it returns only those lines with an actual value in Global Dimension 1 - I have some G/L Entry lines that have no Global Dimension 1 value that I'd want to have pull up on this report as well.
Does that make more sense? -
Jet Reports Historic Posts Oh Yes I see what you're saying. Hmmm, we might be able to do that by unioning together the results from the Dimension Value table and the blank from the G/L Entry table like this:
NL("Rows",NP("Union",NL("Filter","Dimension Value","Code","Global Dimension No.","1","Link=","G/L Entry","Global Dimension 1 Code","=Code","G/L Account No.",D5,"Posting Date","..06/30/2012"),NL("Filter","G/L Entry","Global Dimension 1 Code","G/L Account No.",D5,"Posting Date","..06/30/2012","Global Dimension 1 Code","@@")))
I think this should give you the blank value for Global Dimension 1 Code if one exists for that account for that date range as well as the dimension values. It might make the report a little slower since we're going to the G/L Entry again but hopefully not too much. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts BRILLIANT! Another thing I really need to learn about: Unions. That, my friend, worked like a charm.
The last thing, I think, that I want to try to do is eliminate the report from returning any rows that have Zero balance, but always return rows that don't have a zero balance. The challenge being that an account may have a zero balance but an account/fund/global dimension 1 combination may not. Any clue how to accomplish this? Seems like it might be a fairly circular thing. If not, I think we can take the results and filter out the zeroes quite easily, I'd just prefer to have the report do everything so that we don't have any need for manipulation.
I honestly thought this report would be really simple to write. Thank heaven for people like you who can help when things end up being not so simple. -
Jet Reports Historic Posts Hey I'm glad that worked! :)
Hmmm, there might be a way to do that with a calculated filter, but honestly a conditional hide is probably easier and possibly faster as well. To do a conditional hide, you just put "Hide+?" in cell B1. Then in column B you put an Excel IF function that returns "Hide" if you want the row hidden and "Show" if you want the row not hidden (or really anything other than "Hide" will show the row). So your IF function can just reference the balance and return Hide if the balance is zero. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Yeah, I know about the conditional hide, but since we will be giving this report (in excel) to our audit team, they will just end up unhiding the hidden rows. If we were just presenting it, that would work, but they are gonna import it into their software, so..meh. No worries!
You've been a HUGE help, I really appreciate it!
I really need to learn about Unions and Links though, if you can point me to anything specific that's good, please let me know! Those functions have literally saved us a ton of time by speeding up this process and allowing Excel/Jet to do the vast majority of the work for us. -
Jet Reports Historic Posts Hey I'm glad to help. As far as help learning linking and unioning, there are a couple things you could do. Of course there's always the Jet help documentation, but that's pretty dry and basically reference material. Still you can get to it online here:
http://help.jetreports.com
Another thing you can do as far as linking goes is use the new Table Builder tool in Jet. The Table Builder can build reports with multiple tables using the NL(Table) function and it uses Link= and InclusiveLink= extensively, so creating some reports with multiple tables and looking at the output formulas that it creates could be pretty useful. Hope that helps!
Regards,
Hughes -
Jet Reports Historic Posts I will dive into the help files and build some tables using the table builder; thank you again for the pointers and the assistance last week!
Please sign in to leave a comment.