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

    Any Jet Reports guru's here?

    IT Discussion
    jet reports
    5
    21
    3.0k
    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.
    • 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
                                        • FiyaFlyF
                                          FiyaFly @Sparkum
                                          last edited by FiyaFly

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

                                          @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

                                          Sweet! What changes did you make?

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