Interactive Workbook

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.

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.

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.

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.

Most of the columns are self explanatory except for the ones on the right side.  These indicate which profiles a task is assigned to (Requested, Approved, Programming, User Acceptance and Mike Current).  The last column indicates if a task has personal notes attached

Use Names for Branch Select in "Inventory Valuation by Branch"

Rather than using a list of Branch numbers for the drop down selection you might prefer to use the actual Branch Names.  You could write another query to pull the Branch Name (or City) and the Branch Number but since Branches do not normally change frequently you could just manually enter the list in a worksheet.  Either way you choose you should end up with something similar to this.

As we've done previously you will need to name the range of cells containing your list.  The list of Branch Names has been named nameBranch.  This range will be used for the data validation.  Additionally select all the names and numbers (i.e. in this example you would select A2:B14) and name it dataBranch. 

Before changing the source used in the drop down you'll need to change how the parameter value is passed to the query; if it's not changed and the value in the drop down changes from a numeric value to an alpha value an error message will display.  Open the Parameters dialog window and select "Prompt for value using the following string:" and click OK.

On the worksheet where the Branch number is currently being selected click on the cell that has the drop down list.  Display the Data Validation dialog box and change the entry in the Source box from =validBrNum to =nameBranch (or whatever you named your list of Branch Names) and click OK.  Now the drop down will display the list of names rather than numbers.

Now select a cell to contain the Branch number to be passed as the parameter to the query.  I recommend using a cell in a row or column that can be hidden so nonessential data is not displayed on the worksheet (I've chosen J6).  In your selected cell enter =VLOOKUP(G3,dataBranch,2,0) where G3 is the cell containing the Branch Name.  The Branch number should now display in the cell.

Now reopen the Parameter dialog box and change it to use the cell where the Branch number is being displayed and to refresh when the cell value changes and then click OK.

Now the user will be able to select the desired Branch by Name rather than Number.

As always if you have any questions about this post or anything related to Excel leave a comment here or email me at the office.

Add Branch Select to "Inventory Valuation by Branch"

Continuing with the development of "Inventory Valuation by Branch" we'll add the ability for users to select valid Branches from a drop down.  First we'll look at how to create this list using Data Validation.  If you're only dealing with a few Branches the list can be entered directly in the "Data Validation" dialog box but because other lists that you may want to use will need to be updated we'll add the valid Branches to a worksheet and name the range of cells containing the list.

To begin add a new worksheet to the "Inventory Valuation by Branch" workbook.  In cell A1 type validBrNum.  Beginning in cell A2 enter your valid Branch numbers.  When all Branch numbers have been entered select all the cells containing the numbers and then click in the Name Box (the box immediately above column A) and by validBrNum.  This names the selected range of cells.

On the worksheet containing the data select the cell that you contains the Branch number linked to the query.  If you're using Excel 2007 select the Data tab on the ribbon and click Data Validation in the Data Tools section (for 2003 click Data > Validation).  The Data Validation dialog box will display.  In the Allow box click the drop down and select List.  In the Source box type =validBrNum and verify that "In-cell dropdown" is checked.

Click OK.  Now the cell containing the Branch number has a drop down and when it's clicked the user can select from the list of Branches and when selected the query will run and display data for the selected Branch.

In the next post we'll look at using a list of valid Branch Names rather than Numbers.

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

Chagnge "Inventory Valuation by Branch" Query to a Parameter Query

To change the "Inventory Valuation by Branch" query to a parameter query you will need to edit the query.  If you're using Excel 2007 right click on any cell in the data returned by the query and click Table > Edit Query.  For 2003 right click on any cell in the data and Edit Query.  In the Microsoft Query editor replace the value previously entered for IVNO10 with [brnum] as below...

Now when you run the query or return the data to Excel you will be prompted to enter a Branch number.  Type the desired Branch number and click OK.  This will either display the data for the selected Branch or will return the data to the worksheet.

Next we want to pass the Branch parameter from the worksheet back to MS Query when we want the query to execute and display data for a different Branch.  This is done by designating a specific cell on the worksheet to contain the Branch number and then setting the query to refresh when the value of that cell changes.  To do this in Excel 2007 right click on any cell in the returned data and click Table > Parameters to display the dialog box below...

Select the "Get the value from the following cell:" radio button and check "Refresh automatically when cell value changes" check box.  Click in the box below "Get the value..." and then click in the cell on your worksheet that is to contain the Branch number.  Click OK or simply press the enter key.

Now the user can easily go from displaying Branch 9 data...

To displaying Branch 99 data by changing the value in cell G3...

If an invalid value is entered in the cell the query will run but no data will be returned.  To prevent this you can provide a list of valid Branch numbers from which the user can select the desired Branch.

The simplest way to achieve this is a drop down list containing a list of your Branches.  This will be covered in the next post.

As always if you have any questions about this post or anything related to Excel you can post a comment here or email me at the office.