Calculate Inventory In SRS (Visual Studio)

We upgraded to 4.02 SQL earlier this year and I have finaly had time to start using Visual Studio to create reports. It has been amazing compared to trying to do it via C/Side previously. We need to create some reports that will need to have date from items that are flow fields in NAV. The big one for me is Inventory.

Do you need to calculate the inventory everytime from the entire Item Ledger Table based on item number, location, etc? This seems like it will really slow down the report if done on the live database.

What is the preffered method to get a quantity on hand for an item (tables needed, expresion syntax, etc) for efficiently generating the quantity on hand?

It might be nice to setup a Forum specifically for SQL / SRS / NAV?

Kevin

Dear Kevin,

It sounds very interesting. I would love to hear more about what you’re actually doing. Are you using SQL Reporting Services?

Yes of cause the downside of using a non-C/SIDE report write, is that you don’t have access to use the flowfields from NAV.The flowfields are really one of the most powerful features of Navision, but you cant use them outside of the application.

The best way to see what you should do to get the same results, is properly to look up the field in the database and then see how the number is calculated.

Hey, it is possible to calculate flow fields in SRS, but you have to do it manually.As example use table company$32$0 (compsany$tableno$firstindexwithsiftfields)It depends on the bucket what query you have to define.As example, if you want to calculate inventory for the whole item you have to filter “bucket” = 1.Take a look in the C/side table and show all columns in the key view. There you can see which bucket is responsible for. Sometimes it can be useful to write the query in a stored procedure and use the sp as dataset.(settings in the dataset)Then you don’t have to write it once again every time you need the inventory.

Ok, let me now if you need more detail description.

Rene