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 [ | ]

means does not contain. For example, UPC

85211 will include only UPC codes that don't contain 85211.
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="23b66a27-d4b5-43f1-9854-b3e8649a84b5"><ac:plain-text-body><![CDATA[h3. Greater than [ > ]]]></ac:plain-text-body></ac:structured-macro>
> means greater than
<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="6acce1f2-57c4-4086-bdef-3434988fabad"><ac:plain-text-body><![CDATA[h3. Less than [ < ]]]></ac:plain-text-body></ac:structured-macro>
< means less than

Connectors

Multiple fieldname values must be separated by either a space or comma. For example State = NV, CA selects data with state codes of NV (Nevada) or CA (California). The command State = NV CA is equivalent.

Space

Extra spaces are ignored. So State = CA is the same as State=CA. And State = CA NV is the same as State=CA NV.

Comma

Commas and spaces are treated the same way. Extra commas are ignored. Spaces and Commas can be used interchangeably.

Wild Cards

Asterisk

An asterisk means any characters. Part=CH* means include all part numbers that begin with the letters CH.

Question Mark

A question mark means any character in this position. So both Smith and Smyth qualify if we've specified Name=Sm?th.

Break Here- The SORT Command (How your Report will Look)

The Select statement specified what data is retrieved for a report. The Sort statement tells what information to display, and how to arrange it. Recall the menu item (report format) we choose will specify columns. Our Sort statement determines the rows.
The Sort statement uses Field Names from the data dictionary.

Syntax of Sort Command

Sort statements are built logically. For clarity we'll start with the simplest statement. And build up to its full capability.

Basic Sort Syntax

Basic Sort statements begin with the word SHOW followed by one or more field names. Separate each command or field name in your statement with a blank space.
For example to list sales by state the basic Sort statement looks like:

The report looks like:

Similarly we could have our report list products instead of listing by State. We'll use Label for products:

The report looks like:

Getting a Little Fancier

Using switch options we can change the sorting, totaling, and more. For example adding

Some Simple Examples Using Two Fields

To illustrate how the Sort statement works we'll begin with just 2 fields (State and Label). We're going to look at 3 examples using the same fields each time. We'll use them to produce 3 different reports.

By State showing Labels Sold

To produce a report showing which wines are sold in each state we'll use a Sort statement:

The report looks like:

So the statement (Show State Label) told the system to show Labels within each State.

By Label Showing sales by State

To show which wines we sold and where they were sold our Sort statement looks like:

The report looks like:

So this Show statement (Show Label State) told the system to show which states made up each label's sales.

By Label and Separately by State

Altering our Sort statement slightly we can show sales by Label and separately show sales by State.

By using 2 Show commands we're effectively displaying two reports in one:

Showing Label and State on Separate Pages

We described the report example above as two reports in one. Showing each on a separate page might make it clearer to a reader. All we need to do is add ,P as shown below.

With this simple change (,P) our report sections now print on separate pages:

By using a Page Break (,P) command our report now prints by Label on one page, and by State on another.

Sort Statement Switches

Page Breaks

P

No Page Break

NP

Suppressing a Total

NT

Ranking or Sorting your Report

Per SS, ranking in AMS is always on a quantity. Otherwise sorting and ranking is the same thing. Seems ranking has some implied behaviors in IRW, that Sorting may not be subject to.

Rank and Show Top __ Items

Adding Intermediate Subtotals to your Ranking

Rank in Descending Order

R, RD

Rank in Ascending Order

RA

Sort Descending

D

Rank by Trend

RT

Printing Codes

Show the Code

C

Suppressing Codes

NC

Headings

Printing

H

Suppressing

NH

Totals

Print Total

T

Suppress Total

NT

Percent of Total

%

Redundant Totals

More Information

If you need more information about any of the features mentioned here, please contact our helpdesk at (707) 539-7990 or helpdesk@AMSsoftware.com .

Browser not supported