Reporting Language Enhancements

Example: Returning the Ceiling of a Number

In the following request, CEILING returns the smallest integer greater than or equal to the GROSS_PROFIT_US value.

DEFINE FILE WF_RETAIL_LITE
CEIL1/D7.2= CEILING(GROSS_PROFIT_US);
END
TABLE FILE WF_RETAIL_LITE
PRINT GROSS_PROFIT_US/D9.2  CEIL1
ON TABLE SET PAGE NOPAGE
END

The partial output follows. Note that even though the value returned is an integer, it is returned with the same format as the CEIL1 field (D7.2):

  Gross Profit     CEIL1      
  ------------     -----     
        165.00    165.00
         13.99     14.00
         60.99     61.00
        225.98    226.00
         79.99     80.00
         44.59     45.00
         94.30     95.00
        238.50    239.00
        199.99    200.00
         68.99     69.00
         63.58     64.00
        129.99    130.00
         37.49     38.00
         75.99     76.00
         13.99     14.00
        119.00    119.00
        -30.01    -30.00
         54.99     55.00
        189.98    190.00
         44.59     45.00
         91.98     92.00
         89.00     89.00
         59.50     60.00
        129.99    130.00
         54.00     54.00
        109.98    110.00
         98.99     99.00
         98.99     99.00
         99.99    100.00
         44.59     45.00

Example: Raising e to a Power

The following request prints the value of e and the value of e raised to the fifth power.

DEFINE FILE WF_RETAIL_LITE
EXP1/D12.5 = EXPONENT(1);
EXP2/D12.5 = EXPONENT(5);
END
TABLE FILE WF_RETAIL_LITE
PRINT EXP1 EXP2
BY BUSINESS_REGION AS Region
WHERE BUSINESS_REGION EQ 'EMEA'
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Floor of a Number

In the following request, FLOOR returns the largest integer less than or equal to the GROSS_PROFIT_US value.

DEFINE FILE WF_RETAIL_LITE
FLOOR1/D7.2= FLOOR(GROSS_PROFIT_US);
END
TABLE FILE WF_RETAIL_LITE
PRINT GROSS_PROFIT_US/D9.2  FLOOR1
ON TABLE SET PAGE NOPAGE
END

Partial output follows. Note that even though the value returned is an integer, it is returned with the same format as the FLOOR1 field (D7.2):

  Gross Profit    FLOOR1       
  ------------    ------           
        165.00    165.00
         13.99     13.00
         60.99     60.00
        225.98    225.00
         79.99     79.00
         44.59     44.00
         94.30     94.00
        238.50    238.00
        199.99    199.00
         68.99     68.00
         63.58     63.00
        129.99    129.00
         37.49     37.00
         75.99     75.00
         13.99     13.00
        119.00    119.00
        -30.01    -31.00
         54.99     54.00
        189.98    189.00
         44.59     44.00
         91.98     91.00
         89.00     89.00
         59.50     59.00
        129.99    129.00
         54.00     54.00
        109.98    109.00
         98.99     98.00
         98.99     98.00
         99.99     99.00
         44.59     44.00

Example: Calculating the Remainder From a Division

In the following request, MOD returns the remainder of PRICE_DOLLARS divided by DAYSDELAYED:

DEFINE FILE WF_RETAIL_LITE
MOD1/D7.2= MOD(PRICE_DOLLARS, DAYSDELAYED);
END
TABLE FILE WF_RETAIL_LITE
PRINT PRICE_DOLLARS/D7.2  DAYSDELAYED/I5 MOD1
WHERE DAYSDELAYED GT 1
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT WP
END

Partial output follows:

Price    Days                      
   Dollars  Delayed      MOD1                 
   -------  -------      ----                                                                                                     
    399.00        3       .00
    489.99        3       .99
    786.50        2       .50
    599.99        4      3.99
     29.99        4      1.99
    169.00        2      1.00
    219.99        2      1.99
    280.00        3      1.00
     79.99        4      3.99
    145.99        2      1.99
    399.99        3       .99
    349.99        3      1.99
    169.00        3      1.00

Example: Raising a Base Value to a Power

In the following request, POWER returns the value COGS_US/20.00 raised to the power stored in DAYSDELAYED:

DEFINE FILE WF_RETAIL_LITE
BASE=COGS_US/20.00;
POWER1= POWER(COGS_US/20.00,DAYSDELAYED);
END
TABLE FILE WF_RETAIL_LITE
PRINT BASE IN 15 DAYSDELAYED POWER1
BY PRODUCT_CATEGORY
WHERE PRODUCT_CATEGORY EQ 'Computers'
WHERE DAYSDELAYED NE 0
ON TABLE SET PAGE NOPAGE
END

Partial output follows:

  Product                              Days         
  Category                BASE         Delayed          POWER1  
  --------                ----         -------          ------  
  Computers              12.15               3        1,793.61
                         16.70               2          278.89
                          8.35               1            8.35
                          8.10               2           65.61
                          4.05               1            4.05
                          4.05               2           16.40
                          4.05               4          269.04
                          8.35               1            8.35
                         16.70               1           16.70
                          8.35               3          582.18
                          8.35               1            8.35
                          4.05               1            4.05
                          4.05               1            4.05
                          8.35               4        4,861.23
                          8.35              -1             .12
                          8.35               1            8.35
                          8.35               3          582.18

Example: Retrieving the Last Non-Blank Value

The following request shows the SALARY field, the LAST_NONBLANK value from a DEFINE, and the LAST_NONBLANK value from a COMPUTE.

DEFINE FILE EMPLOYEE
MYSAL/I5 MISSING ON=IF DEPARTMENT EQ 'MIS' THEN SALARY ELSE MISSING;
LAST_NONBLANK1/I5 MISSING ON= LAST_NONBLANK(MYSAL);
END
TABLE FILE EMPLOYEE
PRINT LAST_NAME MYSAL  LAST_NONBLANK1
COMPUTE LAST_NONBLANK2/I5 MISSING ON=LAST_NONBLANK(MYSAL);
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image.

Example: Matching a String Against a Regular Expression

The following request matches the FIRSTNAME field against the regular expression '^Sara(h?)$', which matches Sara or Sarah:

TABLE FILE WF_RETAIL_LITE
PRINT FIRSTNAME AND COMPUTE
REG1/I1=REGEX(FIRSTNAME,'^Sara(h?)$') ;
BY LASTNAME/A10
WHERE LASTNAME EQ 'Allen'
END

The output is

              First           
  LASTNAME    Name                  REG1    
  --------    -----                 ----           
  Allen       Penny                    0
              Rosemary                 0
              Amber                    0
              Julie                    0
              Sarah                    1
              Leo                      0
              Margret                  0
              Donna                    0
              Damian                   0
              Alexander                0
              Diego                    0
              Amber                    0
              Susan                    0
              Amber                    0
              Sara                     1
              Sara                     1

Example: Concatenating Strings After Removing Blanks From the First

The following request concatenates city names with state names. Note that the city and state names are converted to fixed length alphanumeric fields before concatenation.

DEFINE FILE WF_RETAIL_LITE
CITY/A50 = CITY_NAME;
STATE/A50 = STATE_PROV_NAME;
CONCAT_CS/A100 = CONCAT(CITY,STATE);
END  
 
TABLE FILE WF_RETAIL_LITE
SUM CITY AS City STATE AS State CONCAT_CS AS Concatenation
BY STATE_PROV_NAME NOPRINT
WHERE COUNTRY_NAME EQ 'United States'
WHERE STATE LE 'Louisiana'
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Replacing a String

REPLACE replaces the string 'South' in the Country Name with the string 'S.'

SET TRACEUSER = ON 
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP=OFF
DEFINE FILE WF_RETAIL_LITE
NEWNAME/A20 = REPLACE(COUNTRY_NAME, 'SOUTH', 'S.');
END
TABLE FILE WF_RETAIL_LITE
SUM COUNTRY_NAME
BY NEWNAME AS 'New,Name'
WHERE COUNTRY_NAME LIKE 'S%'
ON TABLE SET PAGE NOLEAD
END

The generated SQL passes the REPLACE function to the DBMS REPLACE function.

SELECT   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.'),  
MAX(T3."COUNTRY_NAME")  
FROM   
wrd_wf_retail_geography T3  
WHERE   
(T3."COUNTRY_NAME" LIKE 'S%')  
GROUP BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.')  
ORDER BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.'); 

The output is shown in the following image.

Example: Replacing All Instances of a String

In the following request, the virtual field DAYNAME1 is the string DAY1 with all instances of the string 'DAY' replaced with the string 'day'. The virtual field DAYNAME2 has all instances of the string 'DAY' removed.

DEFINE FILE WF_RETAIL
DAY1/A30 = 'SUNDAY MONDAY TUESDAY';
DAYNAME1/A30 = REPLACE(DAY1, 'DAY', 'day' );
DAYNAME2/A30 = REPLACE(DAY1, 'DAY', '' );
END
TABLE FILE WF_RETAIL
PRINT DAY1 OVER
DAYNAME1 OVER
DAYNAME2
WHERE EMPLOYEE_NUMBER EQ 'AH118' 
ON TABLE SET PAGE NOPAGE
END

The output is:

  DAY1      SUNDAY MONDAY TUESDAY         
  DAYNAME1  SUNday MONday TUESday         
  DAYNAME2  SUN MON TUES                  

Example: Extracting an Element From a String

The following request defines strings and extracts elements from them.

DEFINE FILE WF_RETAIL_LITE
STRING1/A50 WITH COUNTRY_NAME= 'http://www.informationbuilders.com';
STRING2/A20 = 'user1@ibi.com';
STRING3/A20 = 'Louisa May Alcott';
Protocol/A20 = SPLIT(URL_PROTOCOL, STRING1);
Path/A50 = SPLIT(URL_PATH, STRING1);
Domain/A20 = SPLIT(EMAIL_DOMAIN, STRING2);
User/A20 =  SPLIT(EMAIL_USERID, STRING2);
First/A10 = SPLIT(NAME_FIRST, STRING3);
Last/A10 = SPLIT(NAME_LAST, STRING3);
END
TABLE FILE WF_RETAIL_LITE
SUM Protocol Path User Domain First Last
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Using the CHAR Function to Insert Control Characters Into a String

The following request defines a field with carriage return (CHAR(13)) and line feed (CHAR(10)) characters inserted between the words HELLO and GOODBYE (in an ASCII environment). To show that these characters were inserted, the output is generated in PDF format and the StyleSheet attribute LINEBREAK='CRLF' is used to have these characters respected and print the field value on two lines.

DEFINE FILE WF_RETAIL_LITE
MYFIELD/A20 WITH COUNTRY_NAME='HELLO' | CHAR(13) | CHAR(10) | 'GOODBYE';
END
TABLE FILE WF_RETAIL_LITE
SUM MYFIELD 
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,LINEBREAK='CRLF',$
ENDSTYLE
END

The output is shown in the following image.

Example: Using the CTRLCHAR Function to Insert Control Characters Into a String

The following request defines a field with carriage return (CTRLCHAR(CR)) and line feed (CTRLCHAR(LF)) characters inserted between the words HELLO and GOODBYE. To show that these characters were inserted, the output is generated in PDF format and the StyleSheet attribute LINEBREAK='CRLF' is used to have these characters respected and print the field value on two lines.

DEFINE FILE WF_RETAIL_LITE
MYFIELD/A20 WITH COUNTRY_NAME='HELLO' | CTRLCHAR(CR) | CTRLCHAR(LF) | 'GOODBYE';
END 
TABLE FILE WF_RETAIL_LITE
SUM MYFIELD 
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,LINEBREAK='CRLF',$
ENDSTYLE
END

The output is shown in the following image.

Example: Converting a Date to a Character String

The following request defines a date field as YYMD format and converts it to a character string (CharDate) in YYMtrD format.

DEFINE FILE WF_RETAIL_LITE
DATE1/YYMD = TIME_DATE_DAY_COMPONENT;
CharDate/A20 = EDIT2(DATE1,'YYMtrD');
END
TABLE FILE WF_RETAIL_LITE
SUM COGS_US
BY CharDate
WHERE TIME_MTHNAME  EQ 'FEB'
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Returning a Hexadecimal View

The following request returns a hexadecimal view of the country names and the sum of the days delayed.

DEFINE FILE WF_RETAIL_LITE
Days/I8 = DAYSDELAYED;
Country/A20 = COUNTRY_NAME;
HexCountry/A30 = HEXTYPE(Country);
END
TABLE FILE WF_RETAIL_LITE
SUM COUNTRY_NAME NOPRINT  Country HexCountry Days
COMPUTE HexDays/A40 = HEXTYPE(Days);
BY COUNTRY_NAME NOPRINT
WHERE COUNTRY_NAME LT 'P'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Generating a Phonetic Key

The following request changes the spelling of the last name for MARY SMITH to SMYTHE and generates a phonetic key for each last name.

DEFINE FILE EMPLOYEE
LAST_NAME2/A16 = IF LAST_NAME EQ 'SMITH' AND FIRST_NAME EQ 'MARY' THEN 'SMYTHE' ELSE LAST_NAME;
PKEY/A10 = PHONETIC(LAST_NAME2);
END
TABLE FILE EMPLOYEE
PRINT FIRST_NAME LAST_NAME2 
BY PKEY
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. Note that the two spellings for SMITH are assigned the same index number.

Example: Converting a Character String to an Integer Value

The following request converts character strings to integers. Digits following the decimal point are truncated.

DEFINE FILE WF_RETAIL_LITE
INT1/I8 = TO_INTEGER('56.78');
INT2/I8 = TO_INTEGER('.5678');
INT3/I8 = TO_INTEGER('5678');
END
TABLE FILE WF_RETAIL_LITE
PRINT INT1 INT2 INT3
BY BUSINESS_REGION AS Region
WHERE READLIMIT EQ 1
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Example: Converting a Character String to a Number

The following request converts character strings to double-precision floating point numbers.

DEFINE FILE WF_RETAIL_LITE
NUM1/D12.1 = TO_NUMBER('56.78');
NUM2/D12.2 = TO_NUMBER('0.5678');
END
TABLE FILE WF_RETAIL_LITE
PRINT NUM1 NUM2
BY BUSINESS_REGION AS Region
WHERE READLIMIT EQ 1
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Example: Returning the Current Date

The following request returns the current date.

DEFINE FILE WF_RETAIL_LITE
CURRDATE/YYMD WITH COUNTRY_NAME = DT_CURRENT_DATE();
END
TABLE FILE WF_RETAIL_LITE
SUM CURRDATE
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Current Date and Time

The following request returns the current date and time, with the time specified in microseconds.

DEFINE FILE WF_RETAIL_LITE
CURRDATE/HYYMDm WITH COUNTRY_NAME = DT_CURRENT_DATETIME(MICROSECOND);
END
TABLE FILE WF_RETAIL_LITE
SUM CURRDATE
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Current Time

The following request returns the current time, with the time precision set to milliseconds.

DEFINE FILE WF_RETAIL_LITE
CURRTIME/HHISs WITH COUNTRY_NAME = DT_CURRENT_TIME(MILLISECOND);
END
TABLE FILE WF_RETAIL_LITE
SUM CURRTIME
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Encrypting a Password

The following request encrypts the value guestpassword using the encryption algorithm configured in the server.

-SET &P1 = ENCRYPT('guestpassword');
-TYPE &P1

The returned encrypted value is {AES}963AFA754E1763ABE697E8C5E764115E.

Example: Retrieving the Value of an Environment Variable

The following request retrieves the value of the server variable EDAEXTSEC.

-SET &E1 = GETENV('EDAEXTSEC');
-TYPE &E1

The value returned is ON if the server was started with security on or OFF if the server was started with security off.

Example: Assigning a Value to the UNIX PS1 Variable

The following request assigns the value FOCUS/Shell: to the UNIX PS1 variable.

-SET &P1 = PUTENV('PS1','FOCUS/Shell:');

This causes UNIX to display the following prompt when the user issues the UNIX shell command SH:

FOCUS/Shell:

The following request creates a variable named xxxx and sets it to the value this is a test. It then retrieves the value using GETENV.

-SET &XXXX=PUTENV(xxxx,'this is a test');
-SET &YYYY=GETENV(xxxx);
-TYPE  Return Code: &XXXX,  Variable value: &YYYY

The output is:

Return Code: 0,  Variable value: this is a test

Example: Calculating the Distance Between Two Geometry Points

The following uses a citibike .csv file that contains station names, latitudes, and longitudes and trip start times and end times. It uses the GIS_POINT function to define geometry points for start stations and end stations. It then uses GIS_DISTANCE to calculate the distance between them.

DEFINE FILE esri/esri-citibike
STARTPOINT/A200 = GIS_POINT('4326', START_STATION_LONGITUDE, START_STATION_LATITUDE);
ENDPOINT/A200 = GIS_POINT('4326', END_STATION_LONGITUDE, END_STATION_LATITUDE);
Distance/P10.2 = GIS_DISTANCE(ENDPOINT, STARTPOINT);
END
TABLE FILE esri/esri-citibike
PRINT END_STATION_NAME AS End Distance
BY START_STATION_NAME AS Start
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Calculating the Drive Route Between Two Geometry Points

The following uses a citibike .csv file that contains station names, latitudes, and longitudes and trip start times and end times. It uses the GIS_POINT function to define geometry points for start stations and end stations. It then uses GIS_DRIVE_ROUTE to calculate the route to get from the end point to the start point.

DEFINE FILE esri/esri-citibike
STARTPOINT/A200 = GIS_POINT('4326', START_STATION_LONGITUDE, START_STATION_LATITUDE);
ENDPOINT/A200 = GIS_POINT('4326', END_STATION_LONGITUDE, END_STATION_LATITUDE);
Route/TX140 (GEOGRAPHIC_ROLE=GEOMETRY_LINE) = 
          GIS_DRIVE_ROUTE(ENDPOINT, STARTPOINT);
END
TABLE FILE esri/esri-citibike
PRINT START_STATION_NAME AS Start END_STATION_NAME AS End Route
WHERE START_STATION_ID EQ 147  
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,SIZE-11,$
ENDSTYLE
END

The output is shown in the following image.

Example: Charting a Driving Route Between Two Geometry Points

The following request uses GIS_DRIVE_ROUTE to generate a driving route between a station start point and end point and charts the route on an Esri map.

DEFINE FILE esri-citibike
STARTPOINT/A200 = GIS_POINT('4326', START_STATION_LONGITUDE, START_STATION_LATITUDE);
ENDPOINT/A200 = GIS_POINT('4326', END_STATION_LONGITUDE, END_STATION_LATITUDE);
Route/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_LINE) = 
          GIS_DRIVE_ROUTE(ENDPOINT, STARTPOINT);
END

GRAPH FILE ESRI-CITIBIKE 
 PRINT 
   START_STATION_NAME
   END_STATION_NAME
WHERE START_STATION_ID EQ 147
 ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH CHOROPLETH
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, /*START_STATION_NAME*/  
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N2, /*END_STATION_NAME*/

  *GRAPH_JS_FINAL 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "dark-gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "Route"}}, "title" : "Chart"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Drive Route" 
END

The output is shown in the following image.

Example: Building a Geometry Point

The following request uses the spatial reference code 4326 (decimal degrees) and state capital longitudes and latitudes to build a geometry point.

DEFINE FILE WF_RETAIL_LITE
GPOINT/A200 = GIS_POINT('4326', STATE_PROV_CAPITAL_LONGITUDE, STATE_PROV_CAPITAL_LATITUDE);
END
TABLE FILE WF_RETAIL_LITE
SUM FST.STATE_PROV_CAPITAL_LONGITUDE AS Longitude
FST.STATE_PROV_CAPITAL_LATITUDE AS Latitude 
FST.GPOINT AS Point
BY STATE_PROV_CAPITAL_NAME AS Capital
WHERE COUNTRY_NAME EQ 'United States'
WHERE STATE_PROV_CAPITAL_NAME LT 'C'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Charting Geometry Points

The following request generates geometry points using GIS_POINT charts them on an Esri map.

DEFINE FILE WF_RETAIL
GPOINT/A200 = GIS_POINT('4326', STATE_PROV_CAPITAL_LONGITUDE, STATE_PROV_CAPITAL_LATITUDE);
END

 GRAPH FILE WF_RETAIL
 PRINT
 STATE_PROV_NAME
 WHERE STATE_PROV_CAPITAL_LONGITUDE NE MISSING
 ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH BUBBLEMAP 
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, 
   BUCKET=tooltip, $ 

  *GRAPH_JS_FINAL 
"bubbleMarker": {"maxSize": "10%"}, 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "GPOINT"}}, "title" : "Report"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Geometry Points" 
END

The output is shown in the following image.

Example: Geocoding a Complete Address

The following request creates a complete address by concatenating the street address, city, state, and zip code. It then uses GIS_GEOCODE_ADDR to create a GIS point for the address.

DEFINE FILE WF_RETAIL_LITE
GADDRESS/A200 =ADDRESS_LINE_1 || '  ' | CITY_NAME || '  ' | STATE_PROV_NAME || ' ' | POSTAL_CODE;
GEOCODE1/A200 = GIS_GEOCODE_ADDR(GADDRESS); 
END
TABLE FILE WF_RETAIL_LITE
PRINT ADDRESS_LINE_1 AS Address GEOCODE1
BY POSTAL_CODE AS Zip
WHERE CITY_NAME EQ 'New York'
WHERE POSTAL_CODE FROM '10013' TO '10020'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Geocoding a Street Address, City, and State

The following request geocodes a street address using GIS_GEOCODE_ADDR_CITY.

DEFINE FILE WF_RETAIL_LITE
GEOCODE1/A200 = GIS_GEOCODE_ADDR_CITY(ADDRESS_LINE_1, CITY_NAME , STATE_PROV_NAME); 
END
TABLE FILE WF_RETAIL_LITE
PRINT ADDRESS_LINE_1 AS Address GEOCODE1
BY POSTAL_CODE AS Zip
WHERE CITY_NAME EQ 'New York'
WHERE POSTAL_CODE FROM '10013' TO '10020'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Geocoding a Street Address and Postal Code

The following request geocodes a street address using GIS_GEOCODE_ADDR_POSTAL.

DEFINE FILE WF_RETAIL_LITE
GEOCODE1/A200 = GIS_GEOCODE_ADDR_POSTAL(ADDRESS_LINE_1, POSTAL_CODE); 
END
TABLE FILE WF_RETAIL_LITE
PRINT ADDRESS_LINE_1 AS Address GEOCODE1
BY POSTAL_CODE AS Zip
WHERE CITY_NAME EQ 'New York'
WHERE POSTAL_CODE FROM '10013' TO '10020'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Building a JSON Geometry Object

The following request builds a polygon geometry of the area encompassing zip code 10036 in Manhattan. The input geometry object is stored in a text (.ftm) file that is cross-referenced in the esri-citibike Master File. The field containing the geometry object is GEOMETRY.

DEFINE FILE esri/esri-citibike
WKID/A10  = '4326';
 MASTER_GEOMETRY/TX256 (GEOGRAPHIC_ROLE=GEOMETRY_AREA) = 
    GIS_GEOMETRY( 'esriGeometryPolygon', WKID , GEOMETRY );
END
TABLE FILE esri/esri-citibike 
 PRINT 
    START_STATION_NAME AS Station
    START_STATION_LATITUDE AS Latitude
    START_STATION_LONGITUDE AS Longitude
    MASTER_GEOMETRY AS 'JSON Geometry Object'
 WHERE START_STATION_ID EQ 479  
ON TABLE SET PAGE NOLEAD
 ON TABLE SET STYLE * 
type=report, grid=off, size=10,$
 ENDSTYLE 
END  

The output is shown in the following image.

Example: Charting a Geometry Object

The following request uses GIS_GEOMETRY to build a geometry object and chart it on an Esri map.

DEFINE FILE esri-citibike
WKID/A10  = '4326';
 MASTER_GEOMETRY/TX256 (GEOGRAPHIC_ROLE=GEOMETRY_AREA) = 
    GIS_GEOMETRY( 'esriGeometryPolygon', WKID , GEOMETRY );
END

 GRAPH FILE ESRI-CITIBIKE 
 PRINT 
   START_STATION_NAME
   END_STATION_NAME
   ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH CHOROPLETH
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, /*START_STATION_NAME*/  
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N2, /*END_STATION_NAME*/

  *GRAPH_JS_FINAL 
 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "dark-gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "MASTER_GEOMETRY"}}, "title" : "Chart"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Geometry Object" 
END

The output is shown in the following image.

Example: Determining if a Point is in a Polygon

The following example determines if a station is inside zip code 10036. GIS_IN_POLYGON returns 1 for a point inside the polygon definition and 0 for a point outside. The polygon definition being passed is the same one used in the example for the GIS_GEOMETRY function described previously and defines the polygon for zip code 10036 in Manhattan in New York City. The value 1 is translated to Yes and 0 to No for display on the output.

DEFINE FILE esri/esri-citibike
WKID/A10  = '4326';
MASTER_GEOMETRY/TX256 (GEOGRAPHIC_ROLE=GEOMETRY_AREA) = 
  GIS_GEOMETRY( 'esriGeometryPolygon', WKID , GEOMETRY );
START_STATION_POINT/A200=GIS_POINT(WKID, START_STATION_LONGITUDE, START_STATION_LATITUDE);
STATION_IN_POLYGON/I4=GIS_IN_POLYGON(START_STATION_POINT, MASTER_GEOMETRY);
IN_POLYGON/A5 = IF STATION_IN_POLYGON EQ 1 THEN 'Yes' ELSE 'No';
END
TABLE FILE esri/esri-citibike 
 PRINT 
     START_STATION_NAME AS Station
    IN_POLYGON AS 'Station in zip, code 10036?'
BY START_STATION_ID AS 'Station ID'
ON TABLE SET PAGE NOLEAD
 ON TABLE SET STYLE * 
type=report, grid=off, size=10,$
type=data, column=in_polygon, style=bold, color=red, when = in_polygon eq 'Yes',$
 ENDSTYLE 
END  

The output is shown in the following image.

Example: Building a JSON Line

The following request prints start stations and end stations and builds a JSON line between them.

DEFINE FILE ESRI/ESRI-CITIBIKE
STARTPOINT/A200 = GIS_POINT('4326', START_STATION_LONGITUDE, START_STATION_LATITUDE);
ENDPOINT/A200 = GIS_POINT('4326', END_STATION_LONGITUDE, END_STATION_LATITUDE);
CONNECTION_LINE/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_LINE) =
   GIS_LINE(STARTPOINT, ENDPOINT);
END
TABLE FILE ESRI/ESRI-CITIBIKE
PRINT END_STATION_NAME AS End CONNECTION_LINE AS 'Connecting Line'
BY START_STATION_NAME AS Start
WHERE START_STATION_NAME LE 'D'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Example: Charting Geometry Lines

The following request generates geometry lines and charts them on an Esri map.

DEFINE FILE ESRI-CITIBIKE
CONNECTION_LINE/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_LINE) =GIS_LINE(START_STATION_POINT, END_STATION_POINT);
DISTANCE/P33.11 TITLE 'Distance'=GIS_DISTANCE(START_STATION_POINT, END_STATION_POINT); 
END

GRAPH FILE ESRI-CITIBIKE 
 PRINT 
   START_STATION_NAME
   END_STATION_NAME
   DISTANCE
 ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH BUBBLEMAP 
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, /*START_STATION_NAME*/  
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N2, /*END_STATION_NAME*/
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N3, /*DISTANCE*/
   BUCKET=tooltip, $

  *GRAPH_JS_FINAL 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "dark-gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "CONNECTION_LINE"}}, "title" : "Chart"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Geometry Lines" 
END

The output is shown in the following image.

Example: Calculating a Service Area Around a Geometry Point

The following request calculates the geometry area that is a five-minute walk around a station.

DEFINE FILE esri/esri-citibike 
WKID/A10='4326';
START_STATION_POINT/A200=GIS_POINT(WKID, START_STATION_LONGITUDE, START_STATION_LATITUDE); 
DISTANCE/A10='5';
TRAVEL_MODE/A10='WalkTime';
STATION_SERVICE_AREA/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_AREA)= 
 GIS_SERVICE_AREA(START_STATION_POINT, DISTANCE, TRAVEL_MODE);
END
TABLE FILE esri/esri-citibike 
 PRINT 
    START_STATION_ID AS 'Station ID'
    START_STATION_NAME AS 'Station Name'
    STATION_SERVICE_AREA AS '5-Minute Walk Service Area Around Station'
 WHERE START_STATION_ID EQ 479 OR 512;
ON TABLE SET PAGE NOLEAD 
ON TABLE SET STYLE * 
TYPE=REPORT, GRID=OFF, SIZE=12,$
ENDSTYLE 
END  

The output is shown in the following image.

Example: Charting a Geometry Service Area Around a Point

The following request generates service areas that are 5-minute walking distances from start station geometry points and charts them on an Esri map.

DEFINE FILE esri-citibike 
WKID/A10='4326';
START_STATION_POINT/A200=GIS_POINT(WKID, START_STATION_LONGITUDE, START_STATION_LATITUDE); 
DISTANCE/A10='5';
TRAVEL_MODE/A10='WalkTime';
STATION_SERVICE_AREA/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_AREA)= 
 GIS_SERVICE_AREA(START_STATION_POINT, DISTANCE, TRAVEL_MODE);
END

GRAPH FILE ESRI-CITIBIKE 
 PRINT 
   START_STATION_NAME
   END_STATION_NAME
   DISTANCE
 ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH CHOROPLETH
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, /*START_STATION_NAME*/  
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N2, /*END_STATION_NAME*/
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N3, /*DISTANCE*/
   BUCKET=tooltip, $

  *GRAPH_JS_FINAL 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "dark-gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "STATION_SERVICE_AREA"}}, "title" : "Chart"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Geometry Service Area" 
END

The output is shown in the following image.

Example: Calculating a Service Area Around a Coordinate

The following request calculates the geometry area that is a five-minute walk around a station, using the longitude and latitude that specify the station location.

DEFINE FILE esri/esri-citibike 
DISTANCE/I4=5;
WKID/A10='4326';
TRAVEL_MODE/A10='WalkTime';
STATION_SERVICE_AREA/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_AREA)=
   GIS_SERV_AREA_XY(START_STATION_LONGITUDE, START_STATION_LATITUDE, DISTANCE, TRAVEL_MODE, WKID);
END
TABLE FILE esri/esri-citibike 
 PRINT 
    START_STATION_ID AS 'Station ID'
    START_STATION_NAME AS 'Station Name'
    STATION_SERVICE_AREA 
       AS '5-Minute Walk Service Area Around Station Coordinate'
 WHERE START_STATION_ID EQ 479 OR 512;
ON TABLE SET PAGE NOLEAD 
ON TABLE SET STYLE * 
TYPE=REPORT, GRID=OFF, SIZE=12,$
ENDSTYLE 
END  

The output is shown in the following image.

Example: Charting a Geometry Service Area Around a Coordinate

The following request generates service areas that are 5-minute walking distances from start station coordinates and charts them on an Esri map.

DEFINE FILE esri-citibike 
WKID/A10='4326';
DISTANCE/A10='5';
TRAVEL_MODE/A10='WalkTime';
STATION_SERVICE_AREA/TX80 (GEOGRAPHIC_ROLE=GEOMETRY_AREA)=
   GIS_SERV_AREA_XY(START_STATION_LONGITUDE, START_STATION_LATITUDE, DISTANCE, TRAVEL_MODE, WKID);
END

GRAPH FILE ESRI-CITIBIKE 
 PRINT 
   START_STATION_NAME
   END_STATION_NAME
   DISTANCE
 ON TABLE PCHOLD FORMAT JSCHART 
 ON TABLE SET LOOKGRAPH CHOROPLETH
 ON TABLE SET EMBEDHEADING ON 
 ON TABLE SET AUTOFIT ON 
 ON TABLE SET STYLE * 
  TYPE=REPORT, TITLETEXT='Map', PAGESIZE=E, CHART-LOOK=com.esri.map, $ 
  TYPE=DATA, COLUMN=N1, /*START_STATION_NAME*/  
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N2, /*END_STATION_NAME*/
   BUCKET=tooltip, $
  TYPE=DATA, COLUMN=N3, /*DISTANCE*/
   BUCKET=tooltip, $

  *GRAPH_JS_FINAL 
"legend": {"visible": true}, 
"extensions" : { "com.esri.map" : 
  { "scalebar" : 
{ 
    "scalebarUnit": "dual", 
    "attachTo" : "bottom-left" 
  }, 
  "baseMapInfo": { 
       "drawBasemapControl" : false, 
       "showArcGISBasemaps" : false, 
            "customBaseMaps" : [ 
            {"ibiBaseLayer" : "dark-gray"} 
        ] 
   }, 
  "overlayLayers": 
  [{ 
  "ibiDataLayer": {"map-geometry" :  {"map_by_field" : "STATION_SERVICE_AREA"}}, "title" : "Chart"}] 
  }, 
"introAnimation": "{\"enabled\":false}" 
} 
 
  *END 
 ENDSTYLE 
 HEADING 
  "Chart Geometry Service Area" 
END

The output is shown in the following image.