Re: BPCS reports -- BPCS-L

1. Suggest you check MIDRANGE-L archives. There is a wealth of informationon people transferring data from AS/400 reports to EXCEL, that isindependent of BPCS ... it can be done with ANY reports. If you cannotfind it there, perhaps I can dig it up ... there was an article about howto put SQL/400 inside of Microsoft Excel Macros so that at the time ofopening an Excel, it grabs the latest data out of BPCS or whatever yourAS/400 application source is.

2. Suggest you take a look at the CUSTOM options of Ops Nav setup. If itis installed with all the DEFAULT options, all you get is OperationsManagement type stuff ... spool files, jobs, performance etc. But throughCustom install, there is a wealth of additional programmer tools and directaccess to DB/2, which adds to security headaches depending on your versionof BPCS. This way you can go direct between 400 and other computingworld. I was just at an IBM seminar about this, not yet digested my notesor pursued the urls handed out at it. Remind me in a month or so, andperhaps I will have a better answer for you.

There's a way to do this with Client Access, which I have not encouragedexploring at our site, because we are on a version of BPCS with obsoletesecurity settings. We need to do the BPCS security upgrade. i studied itand figured the best solution was to buy the security upgrade package, butmanagement has not approved the purchase. Thus I try not to encourageco-workers to go into the areas that give them the capability of trashing BPCS.

3. I operate in an environment where management wants us to get the biggestbang for the buck ... what can we get out of the NATIVE tools. What I havebeen doing is making what I call EXCEL FRIENDLY REPORTS ... modifying theoutput on the 400 to just have the data the EXCEL person wants, andeliminating control breaks so that the control break data is repeated allthe way down the columns. I then use Ops Nav Windows short cut on PC desktop (a technique I learned from Rob on MIDRANGE-L) to get the modifiedreport from spool to PC desk top folder, then from there to e-mail to theEXCEL person who uses Visual Basic to combine multiple EXCEL columns forhis final objectives.

I guess I send reports from BPCS to my end users by this means on theaverage of 2 reports a week, with occasional spikes in volume. A few yearsago our auditors would tell us in advance what reports they needed tosee. We would e-mail them those reports in advance of their visit. Thereare a bunch of reports I run in association with end fiscal, where certainpeople get their end fiscal info in this form.

The commodities challenge that I recently asked about ... looks like wewill be sending our customers a monthly spreadsheet listing the items weshipped them last month (BPCS sales quantity totals for last month), howmuch copper is in there (derived from BPCS BOM and a new file for Guageconversion), multiply the increase in copper price from base quote.

Currently my report is page break by customer, which is not EXCEL friendly,and I also have another version that is EXCEL friendly. It is not yetfinalized what we will be doing, but I expect this will go into EXCEL andhave some other massaging going on before it gets split by Visual Basicinto separate attachment for each customer.

Does anyone know of an inexpensive way to convert a BPCS report to an EXCEL
spreadsheet ? Don't get enough requests to invest in expensive software but
every time someone requests it, it's a challenge. Any recommendations would
be greatly appreciated.

Regards,
Dennis Basiner
Information Systems Manager
Cains Foods L.P.