Hi,
I've created a dashboard that allows the user to drill in to nav, looks like this.
Problem is that due to the number of NL functions (i think) it's taking upto 20 minutes to run.
Any suggestions on how to speed it up? Can I pull 1 count then filter that?
Perhaps magic? Something like:
A7: =NL("Count","Purchase Header","Document Type,"Quote")
A8 = "Count how many in A7 have Job No. <>''"
Mike
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Mike,
Since I don't know how many functions you have or what they're doing, it's hard for me to say how you would speed it up. Maybe you could attach the report in design mode?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Can't upload an example until monday but from memory there are 70 NL"Count" functions, basically 1 for each cell visible.
They filter the Sales (or Purchase) Header tables with Link= Sales (or Purchase) Line to find any lines with an outstanding quantity >0 on where the Promised Delivery (or Expected Reciept) Dates are today or today+7, or in the case of the data for the graph within the date ranges.
I know if i took the data down in a table then i could pivot / report easier but then I wouldn't have any drill down capability. -
Jet Reports Historic Posts Hmm, that's interesting. Well I was going to recommend using NL(Table) to retrieve all the data at once but if you need drilldown then that doesn't work for you. I wouldn't really expect 70 NL(Count) functions to take 20 minutes to run though. Is this NAV 2013 you're using or an earlier version?
Regards,
Hughes -
Jet Reports Historic Posts I've been reading the performance enhancement posts and I've yet to wrap the "=Today()" function in an NP("Eval") - which I hope will speed it up.
Jet version is 2012; NAV Database is 2009 R2.
There are 38,000 Sales Headers from memory, more than 50% of which will have 10-100 lines. Not sure off the top of my head the size of the PO header and line tables but they would be something similar. -
Jet Reports Historic Posts With Link=, it wouldn't matter how many lines there are (it does with NL(Filter) but not Link=). It does matter how many records are in the header table though. It's taking so long b/c it's spinning through all the header records and for each one it's filtering the line table to see if the Link filter matches. Your best bet if you can modify the database would probably be to add a flow field to the header which gets the data you need from the line. Then Jet would be able to deal with it without using Link= and wouldn't have to spin through records which should be much faster. Is that possible?
Regards,
Hughes -
Jet Reports Historic Posts I've attached the report.
Table Sizes
Sales Header 93,971
Sales Line 606,854
Purchase H 70,718
Purchase L 320,396
I'll investigate the option of adding a flow filter - is there any way of estimating the speed increase that could be gained? -
Jet Reports Historic Posts Hi,
So I have a couple suggestions for you as far as performance for this report. I noticed you have the TODAY() function embedded in several of your Jet formulas. TODAY() is a volatile function and Excel will evaluate it multiple times, causing the functions it's embedded in to be re-evaluated multiple times. To solve it, you should wrap it in an NP function like this:=NP("Eval","=TODAY()")
Then you can reference this NP function in all your Jet functions where you need the current date. The other thing that might help the performance is putting your off sheet cell references onto your report sheet and referencing them locally. You can use the NP(Eval) function to do this just like you do with TODAY like this:
=NP("Eval","=Options!$C$7")
You should do this at the top of your report sheet for all your options. Then you should change all the references in Jet functions to the values on the same sheet rather than those on the Options sheet. Does this help?
Regards,
Hughes
Please sign in to leave a comment.