Version: 8.0 & Up

We think the best way for you to understand this feature is to give you examples so you can follow our concept step by step. Let's say we have a need to develop a report that shows the current quantity on hand, on order, allocated, backordered, as well as the sales history for the item information.  You start by going to I/M -> Reports -> Reorder Advice/User Def CSV -> Maintenance -> Add.  Add a specification that has not been used before, as shown below:

• Specification No: Use a unique specification number when you add.
• Description: This is for reference purposes.
• User ID: You can leave it blank, which means any user can run this report, or enter a specific user ID to allow access.
• CSV Path: Enter the file name and path.  The path must be accessible by your user. Note that the file extension must be "CSV."

In the next screen, you will have the opportunity to define the columns for the CSV.  See sample screen below:

Typically, your first column will be the item number.  If you know the two-digit code for item number, you can just enter that in column 1. If you don't, you can press the F7 key to search. See sample screen below of the F7 search window:

You can navigate this list by using the Up, Down, Page Up or Page Down keys. If you feel this is too time consuming, you may guess that an item number starts with an "I," then you can simply type "I."  The system will bring your cursor to the first code type that starts with "I"  and we are lucky that it happens to be "Item Number."

In addition to the explanation of the code type display for each entry in the list, additional explanations may be provided at the bottom of the window for the highlighted code type.

In this case, we simply press the Enter key to select the code type "I" = Item Number and return back to the layout screen. Press it again and the system will now prompt for "Column Heading":

Generally speaking, our default suggestion is that the column heading be short and even abbreviated. But you can override it as you'd like.  Usually we suggest keeping the column heading short so that when you perform the auto-sizing function in Excel, your column won't be wider than necessary and waste valuable space on the screen.

You'll follow the same principle above to add the Description, Product Category, Qty On Hand, Allocated, On Order and Back Order columns.

Add a "Formula" column as shown in the following screen:

At this moment, the system is asking you for the formula.  Our intention is to calculate Qty Avail, which is equal to Qty On Hand minus Qty Allocated. The formula for that is to specify the column C? (? is the column number) with the arithmetic operator. So the formula is expressed as:

    C4-C5

Note the formula is limited to 12 digits so you can specify a complicated formula.  To see more details on how to use a formula, press F1 = Formula Help.

We will also add another formula column of Qty Excess, which is equal to Qty On Hand minus Qty Allocated plus Qty BO.  Qty Excess is considered the short-term quantity available by adding back the BO Qty (long-term) vs the Qty Avail, which is considered the long-term quantity available.

Finally, we will stop by adding the Qty Sold column.  See sample screen below:

At this moment, the system is asking you for the period of Qty Sold. Let's say, we want to add 8 columns of Qty Sold to represent the following information:

So when we define Last Year Quarter 1, the starting period should be "1" (month 1). Now the system will prompt you for the starting year. See sample screen below:

You could enter a specific year for last year. But that would not be desirable because then the report you defined here will not work one year from now.  So the better choice is to use "LY" to stand for Last Year. So we enter the 01/LY as the starting period and 03/LY as the ending period. This is equivalent to Last Year Quarter 1. After we enter the last period, the following window pops up:

In this window, you could specify the quantity sold for a certain customer, customer type, or customer group. However, if you choose to do so, there may be a performance implication because that information is stored in the inventory transaction audit trail table (IMINVTRX), which may take longer to retrieve. If you only need to know the total quantity sold for all customers, the system will retrieve the corresponding information from IMLOCHST, which will be faster. So, in this case, we choose "N" and accept the default to show Qty Sold for all customers. We continue this process until we define all eight Quantity Sold columns as shown below:

Now we can save this report and see what the output looks like.  To run it, you will go to IM -> Reports -> Reorder Advice/User Def CSV -> Print -> User defined CSV export.  Let's say we are only interested in product category U95. So we enter the following:

Again, most of our users ignore the printout, so we usually just print it to screen and close it. Then you will see the following prompt: CSV File Created, Would You Like to Launch it?

You would normally answer "Y." The following is a sample screen output:

The actual Item# and Desc are taken out to protect sensitive information.  After choosing the "Auto Size" function, the spreadsheet allocates just enough space to display each column:

Keep in mind that this is a format of one item per line because we did not define the location column in the layout.  If we add the "Location" column to the layout, then you can expect the following sample output, which is one item/location per line:

For more information on Reorder Advice/User Defined CSV Export, please refer to the Introduction and other related articles:

http://support.elliott.com/knowledgebase/articles/1952719-feature-reorder-advise-user-defined-csv-export-i  

 

EMK