0

SQL Pass Through Query

Jet Reports Historic Posts

November 08, 2013 17:49

None

Follow

Hello,

Is there a way to run an SQL pass though query using Jet reports? I'd like to be able to incorporate basic SQL code in my reports if possible.

Thanks,

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts November 08, 2013 18:41

  Hi,
  
  Yes there is a way to use SQL queries with a Universal data source in Jet with the NL function with SQL=. The documentation for this along with a couple examples is here: http://help.jetreports.com/14.0/Essentials/index.html?SQL=.html
  
  Does this help?
  
  Regards,
  Hughes

• 0
  
  Steven Bain September 09, 2017 21:39

  Hiya Guys,

   

  I have a Jet Report that takes >13mins to run/refresh (regardless of the NL Filter used - which suggests that Jet brings ALL the data through before reducing... which is appalling!).

   

  I'd like to trying using an SQL Pass-Through so the data can be reduced server-side (which should massively speed-up the Report)... however the above link doesn't work.  Does anyone have any documentation on how to achieve this? (as there isn't an "SQL" option within the NL Function as I was expecting - and Jet, so far, has been pretty unintuitive to say the least!)

   

  Cheers,

   

  Steve.

   

  EDIT:  Ah, I've just found this:
  
  **Note: SQL= can only be used with a Universal Jet Reports license.  This functionality is *not* available to Dynamics NAV users.

  Edited by Steven Bain September 09, 2017 21:51
• 0
  
  Gabriele Hayden September 12, 2017 16:17

  Hi Steve,

  Jet includes all filters in its SQL queries, but there are a number of other reasons why your connection might be running slow. You might have a slow connection to the database, you might be trying to pull back 500,000 records, or you might have poor report design. For example, if at all possible you should never wrap your Jet functions in IF statements or other Excel commands, since this interferes with Jet optimization. If you'd like to post your report, I'd be happy to have a look at it and offer suggestions.

  You might also want to read the following articles on optimizing report performance:

  https://jetsupport.jetreports.com/hc/en-us/articles/218954498-Jet-Professional-Performance-with-NAV-Web-Services

  https://jetsupport.jetreports.com/hc/en-us/articles/218954028-Best-Practices-G-L-Reports

  Best,

  Gabriele

• 0
  
  Steven Bain September 14, 2017 11:12

  Hiya Gabriele,

   

  Thanks for your input - I have tried running the Report solely based on the "G/L Entry" Table (no other linked Tables) - and no Excel Functions (any IF, VLOOKUPs, etc. removed)... filtering on "Posting Date" (for only 5 days) the Report only returns 8191 Rows but with "#VALUE" in every Cell.

   

  I've since posted the issue here also - and tried the suggestion of disabling "Batch Processing"... this did complete correctly - however it took over 6hrs to run!

   

  I've passed this over to our Support (who will no doubt contact Jet directly)... if I get a resolution I will let you know.  In the meantime, if you can think of anything else to try, please let me know (I tried to attach the Design of my Report but couldn't find the option?!...)

   

  Cheers,

   

  Steve.

  Edited by Steven Bain September 14, 2017 11:14
• 0
  
  Chris Berry August 14, 2018 15:39

  Just thought I'd check to see if there'd been any changes on the SQL='s functionality - is this still only available for Universal licenses? If so, is there any plan to make it available for Dynamics NAV licenses or would this require a complete overhaul in the way Jet works? Thanks.