My friend Chris at Infomanager forwarded a Microsoft newsletter to me yesterday with a link to instructions on how to embed a workbook into a webpage. Just a simple workbook that uses the formula =SUM((INDIRECT(C4), INDIRECT(C5)) INDIRECT(D4)). Change either of the Region entries or the Month and the total to the right will change.
Saturday, June 18, 2011
More Customer Workbooks - Customer Detail Sales
The Customer that requested this workbook lists it as one of his Top Ten analysis resources. The workbook displays Quantity, Sales Amount, GM Amount and GM% by Item for the selected Customer and date range. The date range selection is by Sales Period.
Clicking the "Help" button displays this text box.
The User can perform a search over the Customer Master file by clicking "Customer Search" and then typing in a portion of the Customer Name and pressing the Enter key. The search can be performed by either "Begins With" or "Contains" based on which radio button is selected. Once the desired Customer has been located the User simply selects the cell containing the Customer Number and clicks the "Display Data" button to display the Sales Data for that Customer.
Help text is also provided on the Customer Search worksheet.
As always, if you have any questions about this workbook or anything related to Excel leave a comment here or email me at the office.
Sunday, June 12, 2011
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.
Tuesday, June 7, 2011
Workbook That I Use At The Office
I've previously posted workbooks that our customers are using and thought that you might like to see one of the workbooks that's in use at the office. This is one is Mike's version of a workbook that others are currently using.
From left to right across the top. A count of filtered tasks being displayed. A button (Refresh Data) that runs a query to refresh the main query. A button (Display Task Notes) that will navigate to another worksheet where you can choose to either display Header Notes or Activity Notes from the selected Task. Check boxes that allow Mike to Include or Exclude EDI and WM Tasks. A button (Display Personal Notes) that allows for displaying personal task notes if they've been entered. A button (Add Personal Notes) that allows for the entry of personal task notes.
Subscribe to:
Posts (Atom)