Performance Issues

[Southerndiva]

I have been asked to develop an Aged Inventory Report for my client. Scenario: All items in stock are to be shown. There are multiple locations, costing method is average, and aging is to be based on last purchase date of the item. Total number of items: 2000+ Aging brackets are 0-90, 91-180 etc. application is PLATINUM FOR WINDOWS by Best

Method used: 2 reports: main and sub
I extracted all records from the main file with quantities on hand and average cost. and calculated value. Created a formula field based on item and location and grouped on this. Fine.

Created a sub-report using a history file which would give me all purchases and their dates. Created an itemlocation formula as above. Created a lastpurchasedate formula using the maximum(date) function and got the last purc.date for each item per location. Subtracted this date from the current date to get number of days since last purchased. This is to be returned to the main report as a shared variable. Fine.

In the main report, items are placed in the group footer(b) of the itemloc group. Group Footer(a) contains the sub report.

The report works fine but it takes approximately 2 hours to run, and even after it says 100% complete, when moving from page to page it seems to be re-calculating.

I find this unacceptable and i know my client will to. Any ideas? I have tried positioning the sub in different locations but it does not work. My only problem is the length of time the report takes to run. All other information returned is correct.

thanks

 

[dgillz]

I would not use the subreport to get the maximum date. Why did you make it a subreport? Maybe we can find a way around that.

Also, what is the database type? Btrieve\Pervasive? If so I have used the indexed fields in a record selection formula to good effect before, but lets start with eliminating the subreport first.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Southerndiva]

Database type is Pervasive. My first attempt at the report did not use a subreport. If I do not use a sub-report, how am i going to get the last purchase date. The main report uses the item master and item location file to get basic information. The subreport uses the history file to get the last purchase date per item per location. Index fields which i can use are itemkey, and item location. is this workable generic sql statement e.g. select itemkey, itemlocation, itemqtyonhand, itemavgcost, maximum(lastpurchasedate) etc to extract the info. I will try this and see what happens and let you know.

thanks

 

[dgillz]

Do a maximum summary on purchase date. Hide the details section.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Southerndiva]

It's maximum purchase date peer item location. I grouped by item+location. Then created a max(purchase date field). Based on that I got the aged days . I can take it from there to get all the aging buckets. Now I have a problem of calculating a grand running total, total,for each aging bracket. What I propose to tell the client is to export to excel. I am spending a lot of non-billable time on this. thanks for your help though.

 

[dgillz]

No this is a snap! Insert, field object, running total field, make it s sum() operation, evalaute once per group.

You will need to sort the details section so that the desired record is the first one shown (if you were to display the details section).



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Southerndiva]

I finally got it right. i removed the sub report. grouped on item+location. calculated the last purchase date, per itemlocation. calculated the aging buckets etc. inserted group footer b, and did a running total of the value of each aging bucket by multiplying by the avg. cost. no need to export to excel.

My client was VERY VERY impressed. He knows the value of his inventory in each aging bracket, and can take the necessary action. I know I am going to be getting aqdditional business from him.

Thanks a mil, for setting me on the right path by removing the sub.

 

[wwwedward]

The last purchase date is that a macola field in a package or something mined from exsisting data? I am needing the same type of report.

 

[Southerndiva]

It's a calculated field using the maximum function