Considerations When Writing Detail Sales Order Reports

There are several aspects of the Order Entry files that should be considered when laying out a report that uses the Order Entry files.  For instance, what date field will be used?  I frequently receive requests for these reports to use the Invoice date and inevitably someone using the report will compare it to one of the Sales Analysis Inquiry screens and think that the report is incorrect because the totals don't match.  There are eleven different date fields in the Order Header files (OEPTOH & OEPTOHY) so you need will need to use the correct date field based on the requirements of your report and you should also indicate this in the title of the report (i.e. "Sales by Invoice Date" or "Sales by Sales Period).  Doing this will help prevent users from making incorrect comparisons.

When writing a report that uses detail line item information items that are flagged as no charge have to be excluded from the sales amount but must be included in the cost amount.  When writing iSeries queries this is somewhat problematic but is greatly simplified when using Excel.  When extracting the data from the Invoiced Line Item file (OEPTOLY) include the "No Charge Item Code" field (OECD43).  When the data is returned to your worksheet insert a column between the Extended Price Amount (OEAM05) and the Extended Cost Amount (OEAM17).  In the cell F3 of the example the formula =IF(H3="Y",0,E3) was entered and copied down to the last row of data.  This returns a value of 0 if the no charge flag is "Y" otherwise the Extended Price value is displayed.

Next hide the columns with the Extended Price Amount and the No Charge fields.  The report now displays the correct Sell and Cost amounts and using these two fields the correct GM Amount and Percentage can be calculated.  Now, just hide the columns containing the Extended Sell Price and No Charge values.

You'll experience a similar issue if you want to include non-stock items in the report because non-stock descriptions reside in the "Non-Stock Description File" (IVPTNSK).  One of the oddities about MS Query is the fact that it does not allow joining more than two files with a left outer join (i.e. that loads all the records from the primary file and just the matching records from the secondary file) and this means there is no way to get all the OE records from OEPTOLY and the stock item descriptions from IVPMSTR and the non-stock descriptions from IVPTNSK with a single query.  The simplest way to extract the data is to write a query on the iSeries and send the output to a data file which can then be accessed with MS Query or transferred from the data file to a PC using Client Access.  Another option would be to create an SQL view for use with MS Query.  We'll discuss these methods in a future post but for right now we'll just look at working with the data once it's loaded to Excel.

We'll use an IF formula similar to the one used above to display the item description.  The worksheet below has a column for the Stock Description (D) and a column for the Non-Stock Description.  Insert a new column (F) and enter =IF(D3="",E3,D3) and copy down to the end of the data.  As you can see below column F now contains the appropriate description and hiding columns D and E will leave the report with a single column for the Product Description.

As always, if you have any questions or comments regarding this post or questions about Excel in general leave a comment here or email me at the office.