ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Any Jet Reports guru's here?

    IT Discussion
    jet reports
    5
    21
    2.9k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • S
      Sparkum
      last edited by scottalanmiller

      I would say this would probably be a better thing to take into private chat, but I was looking to pick someone's brain on Jet reports.

      Currently have a huge report (61 pages) that when we upgraded our ERP started taking 3 hours to run. (From 45 minutes)

      With some quick help from jet we cut it in half and were given some tasks to complete,

      I've gone through all 61 pages, making tons of changes trying to increase performance and make it simply look better....

      Up to taking over 2 hours to run now.

      So obviously I did something wrong, looking to shoot some stuff back and forth with someone

      Thanks

      1 Reply Last reply Reply Quote 3
      • scottalanmillerS
        scottalanmiller
        last edited by

        Don't know Jet Reports at all, I'm afraid.

        1 Reply Last reply Reply Quote 0
        • JaredBuschJ
          JaredBusch
          last edited by JaredBusch

          @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.

          S 1 Reply Last reply Reply Quote 2
          • S
            Sparkum @JaredBusch
            last edited by

            @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.

            scottalanmillerS 1 Reply Last reply Reply Quote 0
            • scottalanmillerS
              scottalanmiller @Sparkum
              last edited by

              @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 🙂

              S 1 Reply Last reply Reply Quote 1
              • S
                Sparkum @scottalanmiller
                last edited by

                @scottalanmiller

                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

                dafyreD scottalanmillerS 2 Replies Last reply Reply Quote 0
                • dafyreD
                  dafyre @Sparkum
                  last edited by

                  @Sparkum said in Any Jet Reports guru's here?:

                  @scottalanmiller

                  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.

                  scottalanmillerS 1 Reply Last reply Reply Quote 1
                  • scottalanmillerS
                    scottalanmiller @Sparkum
                    last edited by

                    @Sparkum said in Any Jet Reports guru's here?:

                    @scottalanmiller

                    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?

                    S 1 Reply Last reply Reply Quote 1
                    • scottalanmillerS
                      scottalanmiller @dafyre
                      last edited by

                      @dafyre said in Any Jet Reports guru's here?:

                      @Sparkum said in Any Jet Reports guru's here?:

                      @scottalanmiller

                      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.

                      S 1 Reply Last reply Reply Quote 0
                      • S
                        Sparkum @scottalanmiller
                        last edited by

                        @scottalanmiller

                        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

                        scottalanmillerS 1 Reply Last reply Reply Quote 0
                        • scottalanmillerS
                          scottalanmiller @Sparkum
                          last edited by

                          @Sparkum said in Any Jet Reports guru's here?:

                          @scottalanmiller

                          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.

                          1 Reply Last reply Reply Quote 2
                          • scottalanmillerS
                            scottalanmiller
                            last edited by

                            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.

                            S 1 Reply Last reply Reply Quote 0
                            • S
                              Sparkum @scottalanmiller
                              last edited by

                              @scottalanmiller

                              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

                              scottalanmillerS 2 Replies Last reply Reply Quote 0
                              • scottalanmillerS
                                scottalanmiller
                                last edited by

                                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.

                                1 Reply Last reply Reply Quote 0
                                • S
                                  Sparkum @scottalanmiller
                                  last edited by

                                  @scottalanmiller

                                  Oooh, sorry I didnt catch that.

                                  1 Reply Last reply Reply Quote 0
                                  • scottalanmillerS
                                    scottalanmiller @Sparkum
                                    last edited by

                                    @Sparkum said in Any Jet Reports guru's here?:

                                    @scottalanmiller

                                    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.

                                    S 1 Reply Last reply Reply Quote 0
                                    • scottalanmillerS
                                      scottalanmiller @Sparkum
                                      last edited by

                                      @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 🙂

                                      1 Reply Last reply Reply Quote 1
                                      • S
                                        Sparkum @scottalanmiller
                                        last edited by

                                        @scottalanmiller

                                        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.

                                        FiyaFlyF 1 Reply Last reply Reply Quote 0
                                        • FiyaFlyF
                                          FiyaFly @Sparkum
                                          last edited by

                                          @Sparkum said in Any Jet Reports guru's here?:

                                          @scottalanmiller

                                          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

                                          S 1 Reply Last reply Reply Quote 1
                                          • S
                                            Sparkum @FiyaFly
                                            last edited by

                                            @FiyaFly
                                            Hey

                                            Ya 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

                                            FiyaFlyF 1 Reply Last reply Reply Quote 1
                                            • 1
                                            • 2
                                            • 1 / 2
                                            • First post
                                              Last post