Reporting

There are a variety of reporting options built into AMS for reporting on DTC key performance indicators. The majority of our reports are IRW (Integrated Report Writer) reports which is a flexible report writing tool that allows users flexibility in defining their own reports.  An IRW has two types of command statements:

Select Statements- used to select the data to be used for the report

Sort Statements-used to define how the report will be displayed

Field Names and the Wizard

In the IRW you'll use field names for both select and sort commands.  You can find the appropriate select and sort commands to choose from for each report in the wizard.  The wizard is a hidden menu that can be accessed when you click anywhere in the select statement.  Once the cursor is in the select statement you will notice a glyph on the right hand side of the box, select the glyph, by clicking on it or pressing the F12 function key, to access the field names available for the report you are using.

To access the field names select the gray box next to the fields column, see screenshot below.  Once the field name is selected, choose the operator and the value of the field that you want to report on.

Operators

Every Select statement contains both AMS field names and operators, State=CA for example.  Here are a list of operators and examples which can be used in the Select statement.  If you use multiple field names they must be separated by a space or a comma.

= Equal to

Example: VAR=CH means only choose data for the report with a Variety of CH.  The field name variety is attached to the part in the part master, you can access the part master through menu options SLS>INFMM>ACP or SLS>INFMM>PAG, part grid.

<> Not Equal to

Example: CTY<>RT means do not choose data for retail customers with a customer type of RT.  Every client's code is different so your retail customer code may not be RT.  You can find your customer codes in customer management SLS>PSCUM>CRM.

~ Contains

Example: UPC~ 85211 selects all parts with UPC codes containing 85211.  Part~CS would select all records with a part number containing CS. 

| Does not contain

Example: UPC|85211 will include only UPC codes that don't contain 85211

> Greater than

Example: Zipcode>94000 State=CA would include only Northern California customers.  In this case it was necessary to put the STATE= CA otherwise I would have include Hawaii and Alaska customers

< Less than

Example: Zipcode<94000 Zipcode>90000 would include customers in central and southern California.  In this case by using less than and greater than operators, I did not have to specify a state since all of those zip codes will fall in CA.

Wild Cards

*Asterik means any character

Example: Part=JW* means include all parts that begin with the letters JW.

? Question mark means any character in this position

Example: Name=Sm?th would select Smith and Smyth.

The Sort Command

The Sort statement tells what information to display and how to arrange it.  The Sort statement uses field names which you can find in the wizard, located in the Select statement.  Basic sort statements begin with the word SHOW followed by one or more field names.  For example to list sales by state the basic Sort Statement would look like this:

The report looks like this:

Instead of displaying state we can have our report display the products sold.

The report looks like this:

In the above report I specified to display part and the report only shows the part description, not the part code.  If you would like to display the part code as well you have to specify that in the report by using a ,C after the field name, the C stands for code.

Now if you want to display 2 field names such as State and Part then your show statement will look like the screenshot below.  Whatever order you type the field names in on the sort statement, that is the order the report will display.

In the above report you only get the totals for each state.  If you would like a recap with a total for each of your field names at the end of the report  then you can alter your show statement slightly. 

If you want to rank your report by most parts sold in descending order you can add a ,R after the part field name. The R is what we refer to as Sort statement switches, it will rank by the most sales for the part, not the units.

Here are some other Sort statement switches and printing codes you can use:

P - Page breaks

NP - No page break

T - Print total

NT - Suppressing a total

R - Rank in descending order

RA - Rank in ascending order

D - Sort descending

C - Show the Code

% - Percent of Total