0

Jet Reports speed when using Business Central

Brendan Ryan

September 03, 2024 17:07

None

Follow

Does anyone have tips and best practices to improve the speed of running Jet Reports when using Business Central?

For example I try to get an inventory balance by item at a certain date (2024-07-31).

• I run NL(Rows) function to get a list of items from Business Central. There are 56,000 items. Only 20,000 items have had a transaction against them in Item Ledger Entries/Value Entries table. So 35,000 items in BC that we haven't transacted against yet. Of the 20,000 items that have had a transaction, only 1,000 had a quantity or value at 2024-07-31.
• I then run NL(SUM) function against both Item Ledger Entries table (to get the quantity as of date) AND Value Entries (to get the inventory cost as of that date).
• Finally I follow the instructions under the Jet Docs to filter out any items that do not have a balance as of the date i want (2024-07-31).

this report ran for over an hour and i gave up as its just too slow. you're faster to dump the data in excel from Business Central and pivot and vlookup (until the rows are greater than what excel can hold...)

There has got to be a way to get an inventory value/quantity report at a point in time in a faster manner. 

This is one example of seemingly many where Jet reports runs at an absolute crawl. Running this in SQL takes mere seconds. What advise does the community have.

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  CascadiaDataworks September 18, 2024 20:10

  There are a number of ways to help with this.  The easiest way is to build your NL Rows from the ILE table that way you are only showing items that have some sort of transaction.  If you have a way to factor in active/inactive items, you can expand on that to whittle it down further.

  And if you are just after inventory levels at a point in time, you can use flowfields in the Item table.  Net Change will allow you to determine the QOH based on a date.

  If you want additional help, happy to offer an initial meeting to review your needs at no charge.  Just reach out to my username at gmail

• 0
  
  Brendan Ryan September 19, 2024 12:51

  Thanks i appreciate your help.

  So i did apply the NL rows against the ILE to get just items with trx. Not best practice but works better. 

  There are no flow fields for inventory cost (from Value Entries) that i can see. Do i need to create an add-on to customize the fields/tables to get this data?

  Appreciate your help, thank you.

• 0
  
  CascadiaDataworks September 20, 2024 17:14

  The more you can leverage flowfields the better and while it would be nice to have a flowfield for everything, it doesn't always work out, like your inventory cost.  You would typically find the flowfield in the Item table rather than the VE table, though.  You can create custom flowfields but you would probably want to work with a BC consultant on that.

  Depending on your report, there are probably a lot of tricks that can be used to reduce speed but without seeing the report as a whole, it is difficult to give you the silver bullet.  That said, report efficiency was my specialty and would be happy to meet with you just to peruse the report for any inefficiencies.

  For future reference, using the table that gets you the right data set (ILE vs Item) is always the best practice.  It is a bigger table but prevents unnecessary Jet queries for those items without transactions.  In your case, 35,000 rows times however many columns to return 0 is A LOT of overhead for Jet to communicate with BC. As a hypothetical example, if you had 4 columns of queries for those 35,000 rows, and let's just say that each query takes .01 seconds (100 queries per second), that adds 1400 seconds to your run time for 0 data.