Performance of Report 1001 Inventory Valuation

herrlutz

we are using Version 3.70B and Costing method "Average". Performance of Report 1001 is quite poor in 3.70B. We use SQL2000.
Just to try it, i "downgraded" Report 1001 from Version 5.0 SP1 to our version.
After a prototype adaption to Version 3.70B it becomes obvious, that this 5.0 report performs (even boosts) up to 20x faster than the original 3.70B!
Figures seem to be ok for trade activities (we have no production), but only without "Expected Costs"

Next step i plan is to make sure that under 3.70B figures are really the same in adapted report 1001 as in original one, also for "Expected Costs".

What i already did:
1. new field "Item Ledger Entry Quantity" in Value Entry
2. Report to fill "Item Ledger Entry Quantity" in Value Entries
3. Since Field "Expected Cost Posted to G/L" is not present in 3.70, Code in "OnAfterGetRecord" of Data Item "Value Entry"
modified as followed:

//Start
//old:
//ExpCostPostedToGL := "Expected Cost Posted to G/L";
//InvCostPostedToGL := "Cost Posted to G/L";
CLEAR(ExpCostPostedToGL);
CLEAR(InvCostPostedToGL);
IF "Expected Cost" THEN
ExpCostPostedToGL := "Cost Posted to G/L"
ELSE
InvCostPostedToGL := "Cost Posted to G/L";
// Stop

4. new key in T5802 "Item No.,Posting Date,Item Ledger Entry Type"

... and so i continue to align the report to 3.70B. Main issue is obviously the semantic of filling "Item Ledger Entry Quantity".

Does anybody have successfully implented and tested the 5.0 report in a previous version?
What has to be considered?
Any other hints to make Report 1001 in 3.70B faster?

headley27

This may not help you as it is not a specific fix for Report 1001, plus I am not sure if this report simple provides output, modifies data within Navision or both but.....

I have many times been frustrated with the speed of Navision Reporting especially because I have a SQL2000 database and I can (more often than not) get data via SQL in a fraction of the time that I can get it via Navision. I find that Navision's method of looping through DataItems.......soooooooooooooo slow.

What I have done for reports that run long is to take most of the processing outside of Navision.

For example, our developer created an incredibly data intensive report in Navision that rebuilds a custom Navision table with all kinds of data from other tables (totals, counts, averages, etc.) which users can filter and export to Excel as they see fit. This table has hundreds of thousands of rows and was taking over 15 hours to create. We would run it overnight but this wasn't working for us. We optimized it through Navision as best we could by modifying keys, etc. but could only shave an hour or two off of the total processing time.

I decided to rewrite the processing outside of Navision on the SQL Server. I called several custom SQL Stored Procedures directly from a Navision Form using the 'Microsoft ActiveX Data Objects 2.8 Library'.Connection Automation Object. The final stored procedure writes the data collected back into the Navision table which the users can then use. After every Stored Procedure I updated a Navision progress meter. To the user it looks like Navision is doing the work although it is not.

The process went from 13-14 hours to 4-5 minutes. This is now on-demand data, not a copy of the previous nights data.

I have since used this for other functions and reports (process the data outside of Navision, create a dataset and have a Navision Report read the collected data). I am currently writing a better, faster A/R Aging Report using this method.

Saying that, I wouldn't recommended anyone use this method for anything other than custom reporting (i.e. don't write data into the standard Navision tables) unless that person really knows Navision.

In short....Navision isn't the only way (and certainly not always the best way) to view or collect data.

Just food for thought.

herrlutz

modified entry

gdi163

I am interested!! That's exactly what I am doing right now and any help would be appreciated.

Eddie

User Herrlutz promissed me help with this issue. That was on Dec 16.
After that user Herrlutz don't comunicate, I haven't received promissed help after near 2 months!!!
No really correct guy!!!