Any Jet Reports guru's here?
-
@scottalanmiller Jet Reports is an add on for MS Excel that gives the users some WYSIWYG SQL connectivity. Basically a BI solution.
I have a client that uses it to create "dashboards" for upper management to get insight into their numbers at will. You pay based on the number of designers you have. You get unlimited viewer licensing. The view can refresh data, only the designer can change a spreadsheet.
@Sparkum To me, it sounds like you are running something that should not be done in Excel in the first place. You need a SQL developer to come in and help your rewrite that.
I get how you are at the point you are, but honestly it should have been rewrote years ago if it takes 45 minutes to execute before.
-
@Sparkum To me, it sounds like you are running something that should not be done in Excel in the first place. You need a SQL developer to come in and help your rewrite that.
I get how you are at the point you are, but honestly it should have been rewrote years ago if it takes 45 minutes to execute before.
I dont disagree with you; however, its simply not an option.
You know how higher ups work. This report has been "the report" for over 10 years, no ifs ands or buts.
-
@Sparkum said in Any Jet Reports guru's here?:
@Sparkum To me, it sounds like you are running something that should not be done in Excel in the first place. You need a SQL developer to come in and help your rewrite that.
I get how you are at the point you are, but honestly it should have been rewrote years ago if it takes 45 minutes to execute before.
I dont disagree with you; however, its simply not an option.
You know how higher ups work. This report has been "the report" for over 10 years, no ifs ands or buts.
Then the speed has to be the speed, no ifs ands or buts
-
Well the fact that in the last 2 months its gone from 45 minutes, to 3 hours, to 1 hour and now back up to 2 hours means there's obviously stuff that can be changed and optimized.
Thats fine I'll just keep googling and reading in the Jet Forums
-
@Sparkum said in Any Jet Reports guru's here?:
Well the fact that in the last 2 months its gone from 45 minutes, to 3 hours, to 1 hour and now back up to 2 hours means there's obviously stuff that can be changed and optimized.
Thats fine I'll just keep googling and reading in the Jet Forums
And get somebody to look over the SQL queries too.
-
@Sparkum said in Any Jet Reports guru's here?:
Well the fact that in the last 2 months its gone from 45 minutes, to 3 hours, to 1 hour and now back up to 2 hours means there's obviously stuff that can be changed and optimized.
Thats fine I'll just keep googling and reading in the Jet Forums
Well, that's not necessarily true. What changed to make it take longer? What we can assume is that something that wasn't Jet changed and now it takes this long. Maybe the data set is bigger. Unless Jet itself was de-tuned and made it take longer, there is no assumption that there is something in Jet that will make it faster.
Have you looked at performance monitors to see if there are any obvious bottlenecks?
-
@dafyre said in Any Jet Reports guru's here?:
@Sparkum said in Any Jet Reports guru's here?:
Well the fact that in the last 2 months its gone from 45 minutes, to 3 hours, to 1 hour and now back up to 2 hours means there's obviously stuff that can be changed and optimized.
Thats fine I'll just keep googling and reading in the Jet Forums
And get somebody to look over the SQL queries too.
That's what he's not allowed to change.
-
Esentially as long as EVERYTHING looks the same I can do it.
So it has to still be Jet, formatted the same way etc.
So formula's changing is alright as long as the numbers come out the same
-
@Sparkum said in Any Jet Reports guru's here?:
Esentially as long as EVERYTHING looks the same I can do it.
So it has to still be Jet, formatted the same way etc.
So formula's changing is alright as long as the numbers come out the same
I don't think anyone was suggesting changing Jet or formatting or anything. Just that the SQL Queries under the hood are where the bottlenecks are and where things need to be changed.
-
What Jared was saying, I think, is that things like your formulas and such should be handled in the database, not in the report front end. That's the SQL queries. I don't think that he was saying that Excel was the wrong tool for the report but that it was the wrong tool for where to be making the performance changes.
-
So we upgraded from Nav05 to Nav2016
Database side shrunk by ALOT
So it immediately jumped to 3 hours.
We contacted Jet, he found thousands of #REF under the Name manager, cleared those out and it shot it back down to an hour (origionally 45 minutes) so thats awesome, esentially back to where we were.
Then he suggested to do alot of things saying it will speed it up
So I started on the 61 page report, doing his suggestions and then adding/changing things like adding more logic like if(cell=0)hide or iferror="-" kinda thing
Which apparently was the wrong decision because it shot up to an hour.
Now the easy answer is to undo the "Make it look pretty" changes I added, but fuck its an ugly report filled with #VALUE and #0/DIV that I would love to clean up
-
Excel is a good tool, but not insanely fast. It's not meant to be. It's a desktop tool meant to run... on a desktop. SQL Server (I assume that's the database here) is meant to do some crazy things to be fast including using all available threads, memory, self tuning, crazy storage and more. That's where you can potentially make the reports way faster. By having Excel just display the results and letting the database do the heavy lifting.
-
Oooh, sorry I didnt catch that.
-
@Sparkum said in Any Jet Reports guru's here?:
So we upgraded from Nav05 to Nav2016
Database side shrunk by ALOT
So it immediately jumped to 3 hours.
Database shrinking could be normalization, dropping indexes or something similar. Looking for index opportunities might be all that is needed.
That a database change was made and you get tons slower suggests that the database is where things need to be fixed.
-
@Sparkum said in Any Jet Reports guru's here?:
So I started on the 61 page report, doing his suggestions and then adding/changing things like adding more logic like if(cell=0)hide or iferror="-" kinda thing
Which apparently was the wrong decision because it shot up to an hour.
Now the easy answer is to undo the "Make it look pretty" changes I added, but fuck its an ugly report filled with #VALUE and #0/DIV that I would love to clean up
Sounds like that portion is just "pretty" things and Jet itself being slow, probably from size. Might be nothing to do there except get a faster machine
-
For sure, and I am definately going to take SUMINDEXING to our developer.
So that would explain the 45-60 minutes jump (since we got the 3 hours back down to 60 minutes)
But the fact that it jumped from 60 minutes to 120 minutes is ENTIRELY on me since my previous version still runs in 60 minutes.
-
@Sparkum said in Any Jet Reports guru's here?:
For sure, and I am definately going to take SUMINDEXING to our developer.
So that would explain the 45-60 minutes jump (since we got the 3 hours back down to 60 minutes)
But the fact that it jumped from 60 minutes to 120 minutes is ENTIRELY on me since my previous version still runs in 60 minutes.
I hate to sound ignorant, as I don't know much of anything about Jet Reports, and I'm basing just off the information I've gleaned from this discussion, their website, and my knowledge of excel (which seems to be massively greater than I think I'd like to admit. Dark Days of "Doing Everything With Nothing"... /tangent)
Schedule reports to automatically run and distribute on a regular interval – daily, weekly or monthly. You can also trigger reports to send based on set alerts (did the budget get exceeded?). Tailor reports to your needs, save your scheduled tasks, then sit back and relax while Jet Reports does the work for you. Like magic!
Maybe set these reports on a schedule? That'd save actually noticing the run time, though it wouldn't actually reduce it.
It sounds like, since the formulas are a little longer, and I'm assuming there are many of them, it is trying to run calculations god knows how many times during the report compilation. Try turning off Automatic Workbook Calculation (File -> Options -> Formulas -> Workbook Calculation. Set to Manual. Will recalculate before saves and can be done manually with F9) See if that helps.
Another possibility is if your screen is showing Jet Reports trying to compile the report from a spreadsheet, it might be heavily taxing the processor due to the visuals. Take a look into programmatically turning off and on screen updating in excel. Might help
-
@FiyaFly
HeyYa one of the largest points of re-doing everything is to get it to a point where we can schedule it (we currently run it 2 times a week and then enter some information)
So we are also trying to get all the cells that we would manually add information to populate.
There are alot of one off reports (using this report) that people want, for example we currently run it on Monday and Thursday, but people also want then different time frames etc so while scheduling it eliminates the time problem it only really half eliminates it.
I was actually able to make a few changes and got the report down to 27 minutes, so I'm back on track for optimizing the report!
Thanks
-
@Sparkum said in Any Jet Reports guru's here?:
@FiyaFly
HeyYa one of the largest points of re-doing everything is to get it to a point where we can schedule it (we currently run it 2 times a week and then enter some information)
So we are also trying to get all the cells that we would manually add information to populate.
There are alot of one off reports (using this report) that people want, for example we currently run it on Monday and Thursday, but people also want then different time frames etc so while scheduling it eliminates the time problem it only really half eliminates it.
I was actually able to make a few changes and got the report down to 27 minutes, so I'm back on track for optimizing the report!
Thanks
Sweet! What changes did you make?
