Click the link below to download a print-ready PDF version of this webpage.

IRW Integrated Report Writer Documentation.pdf

Integrated Report Writer
(IRW)

1-31-2014

v6 

Table of Contents
Overview
Break Here- The SELECT Command (What your report will contain)
Data Sets and Variables
Printing the IRW Data Dictionary
Dissecting the Select and Sort Statements just used
Using the Wizard to look up Dataset names
Reading the IRW Data Dictionary Listing
Operators
[Equal to [ = ] |#_Toc378943012]
[Not Equal to [ <> ] |#_Toc378943013]
[Contains [ ~ ] |#_Toc378943014]
[Does not contain [ | ] |#_Toc378943015]
[Greater than [ > ] |#_Toc378943016]
[Less than [ < ] |#_Toc378943017]
Connectors
Space
Comma
Wild Cards
Asterisk
Question Mark
Break Here- The SORT Command (How your Report will Look)
Syntax of Sort Command
Basic Sort Syntax
Getting a Little Fancier
Some Simple Examples Using Two Fields
By State showing Labels Sold
By Label Showing sales by State
By Label and Separately by State
Showing Label and State on Separate Pages
Sort Statement Switches
Page Breaks
No Page Break
Suppressing a Total
Ranking or Sorting your Report
Rank and Show Top __ Items
Adding Intermediate Subtotals to your Ranking
Rank in Descending Order
Rank in Ascending Order
Sort Descending
Rank by Trend
Printing Codes
Show the Code
Suppressing Codes
Headings
Printing
Suppressing
Totals
Print Total
Suppress Total
Percent of Total
Redundant Totals
More Information

Overview

The Integrated Report Writer (IRW) is a flexible report writing tool used system wide in AMS. Using the IRW, users flexibly define their own reports.
To define an IRW report, two types of command statements are used:

• SELECT statements "select" the data to be used for the report
• SORT statements tell how it will be displayed

Many AMS report formats use IRW commands. When using IRW, the format always determines report columns. IRW instructions specify the rows.
This document explains what IRW commands are available, and how to use them. Examples for each command demonstrate their use.
Our detailed Table of Contents is clickable so it this manual can be used as a reference. Click any TOC entry to go to that topic in the document.

Break Here- The SELECT Command (What your report will contain)

Your Select statement tells AMS what data to retrieve for your report request.
The request may be broad, such as including sales of all wines. Or may be narrow - only show activity for a certain part number, a specific date range, or only for selected states.
In many AMS report screens only the Select Command is used. It offers an additional way to request information. In those screens, Sort Commands are not used at all.

Data Sets and Variables

In the IRW you'll use fieldnames for both Select and Sort commands. The IRW Data Dictionary lists the fieldnames for each dataset. Each AMS module has its own vocabulary.
For example cased good sales use a hierarchy of Part, Label, Category, and Product Type. Bulk Inventory items have attributes such as Appellation, Block, Contract, Grower, Variety, and Vintage. If working with a Customer Master file Name, Phone, City, State, or Zip Code are some of the variables used. As you might expect, using the vocabulary of one module when writing a report in another won't work.
In AMS Data Sets describe the Variables used in each area of the system. Printing the IRW Data Dictionary provides a listing of Variables used in each part of the system. If unsure which dataset is used in the area of the system you're working in, give our help desk a call.

Printing the IRW Data Dictionary

Knowing AMS variable names is necessary for using the IRW. Variables are listed in the IRW Data Dictionary. The Data Dictionary can be printed in its entirety, or for selected areas of the system.
From the CTL menu located in System Administration choose PDD – Print IRW Data Dictionary, as indicated by the red box, below.

The screen to request the Data Dictionary:

Notice the Data Dictionary request screen uses IRW Select and Sort statements. The default request is shown above.
Click the Stream and Exit button to request your Data Dictionary (red box, above).

Dissecting the Select and Sort Statements just used

Let's examine what the commands are saying to the system in the example, above. In the Select statement, DATASET = *, uses a wild card which means any characters. So our Select statement is choosing all datasets. Because no matter what the dataset is named it qualifies as "any characters".
In the Sort statement, above, the following command lines are used:
FOR DATASET, NP
SHOW FILEDNAME TABLEID HEADING DESCRIPTION POSITION LENGTH
To the system it's saying don't use page breaks when datasets change (the ",NP" means no page break). List each dataset showing each value for fieldname, table ID, heading, description, position, and length.
This example uses a longer Show statement than you usually would. Click here to see example output.

Using the Wizard to look up Dataset names

Notice the note above the input area for Select (arrow). To lookup a dataset variable name enter DATASET=? and press Enter.

Entering DATASET=? and pressing return loads a Wizard to help craft your Select statement.
Using the Wizard you may select one or more datasets for your request.

Click any of the boxes in the Select column to include that dataset in your request.
In our example we've selected BBC, Bulk Inventory Composition (red box, above). Notice the green check in the Select box. Click the Continue button (above).
We've selected DATASET = SCD, or Standard Cost Deck:

Now we can make changes. For example we'll click the down arrow to the right of the Operators column (small red box, above). It will display operator choices.

Since we want to use equals we can select it from the pull down menu, or just press escape to exit without selecting (since "=" was already selected).
Choose Continue by clicking its button (red box, below).


The Wizard writes our select statement for us:

Clicking Stream and Exit (red box, above) will launch our Data Dictionary request.

Reading the IRW Data Dictionary Listing

The IRW Data Dictionary Listing we requested for Bulk Inventory Composition appears below:

The Fieldname (left column) lists the variable names which may be used in the IRW. Notice more than one name is used for some of the fields. So it's easier to remember, or guess, the field name.
Using the columns Position and Length we can see if field names are actually equivalent to one another. The long red box, above, contains two field names which are equivalent: COMP and COMPOSITION. Notice the Position/Length combination (0, 9) is identical for each…this confirms they are in fact referring to the same field in the file.
Fields TAG, TAGNUMBER, WEIGHTAG, and WEIGHTAGNUMBER are equivalent to one another (red boxes). How do we know? Their Position/Length combination (24, 10) is the same.
Can you spot other field names which can be used interchangeably? Hint: one more equivalent pair in the listing above can be identified by comparing Position/Length combinations (0, 10).

Operators

Every Select statement contains both AMS field names and operators. State = CA is an example. This section lists operators which may be used, and their meaning. Multiple fieldnames must be separated by either a comma or space.
Extra spaces are ignored. So State = CA and State=CA are the same to the system.

<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="f17d0fb3-9db5-4f2c-bcf2-a94ab3e5baac"><ac:parameter ac:name="">_Toc378943012</ac:parameter></ac:structured-macro>Equal to [ = ]

= means equal. So VAR = CH means only choose data for the report with a Variety of CH.

<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="e096cd1f-7185-4594-8a6d-43b5596e8385"><ac:parameter ac:name="">_Toc378943013</ac:parameter></ac:structured-macro>Not Equal to [ <> ]

<> means not equal to. For example VAR <> CH would select data for every varietal except CH.

<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="a00e87f4-cc6a-4654-a477-2ead64a2b871"><ac:parameter ac:name="">_Toc378943014</ac:parameter></ac:structured-macro>Contains [ ~ ]

~ means contains. For example, UPC ~ 85211 selects all parts with UPC codes containing 85211. PART ~ CS would select all records with a part number containing CS.

<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="914a14ae-61dc-4be7-9071-7d0e13f44a1e"><ac:parameter ac:name="">_Toc378943015</ac:parameter></ac:structured-macro>Does not contain [ | ]