Lans2Us: April 2015

 WebFOCUS Managed Reporting Developer’s Manual.pdf

 WebFOCUS Reporting Server Installation

 Developer Studio Installation and Configuration

 iWay Server Installation

 WebFOCUS Managed Reporting Developer's Manual

 WebFOCUS Desktop Error Messages

 WebFOCUS Security and Administration

 Using WebFOCUS Visual Discovery Components

 WebFOCUS InfoAssist User's Manual

 Describing Data with Graphical Tools

 WebFOCUS Web Services

 Developing Reporting Applications with Graphical Tools

 ReportCaster Development and Administration

 WebFOCUS Best Practices

 Navigate and understand the different components of WebFOCUS Developer Studio

 Create reports using the Report Painter tool:

 Add structure to reports through sorting and display techniques

 Format your report headers, footers, and column headings

 Employ efficient data selection techniques

 Create sophisticated virtual columns

 Join multiple tables as needed

 Limit report contents with static or parameterized selection criteria

 Highlight data with conditional styling

 Establish drill-downs between summary and detail reports

 Display report output in Excel or PDF format

1）eGuide

· Reporting Concepts for Relational Databases

· Creating Financial Reports with the Financial Painter (Duplicate)

· Building Reports with Report Painter (Part 1)

· Advanced WebFOCUS Application Techniques (Duplicate)

· Building Reports with Report Painter (Part 2)

· Designing a User Interface for a Web Application With the HTML Composer

· Developing Composite Reports

· Controlling the Execution of Application Components

· WebFOCUS Application Administration

· Working with ReportCaster

· Integration Techniques for Microsoft Office

· Creating Metadata for Data Sources

· WebFOCUS Reporting for Business Analysts Using InfoAssist

· Managed Reporting Content Development Using InfoAssist

· Managed Reporting Administration

· Building a WebFOCUS Dashboard

· Building Reports using InfoAssist (for Developers)

· Basic Reporting (WebFOCUS Syntax)

· intermediate Reporting

· WebFOCUS Reporting for Business Analysts Using Report Assistant

· Managed Reporting Content Development Using Report Assistant

· Building Reports with Report Painter – Sample (Part 1)

· Create a customized interface for public and group views using the tools of the Dashboard View Builder

· Customize the look of a Dashboard by selecting from a variety of convenient templates, as well as choose background colors, wallpaper, links and a company logo

· Personalize Dashboard content by creating role trees, managing content blocks, and manipulating the composition of the Web page layout

· Explore advanced customization techniques beyond the View Builder

Learning Plan –

2）Building Reports Using InfoAssist (For Developers) v7.x

· Access Managed Reporting through the Business Intelligence Dashboard and understand its significant components

· Create sophisticated reports and graphs with InfoAssist using existing reporting objects and selective data access with custom reports

· Add structure to reports through sorting and display techniques

· Customize your report headers, footers, and column headings

· Employ efficient data selection techniques

· Create sophisticated virtual fields

· Join multiple tables as needed

· Limit report contents with parameterized selection

3）Building a WebFOCUS Dashboard v7.x

· Create a customized interface for public and group views using the tools of the Dashboard View Builder

· Customize the look of a Dashboard by selecting from a variety of convenient templates, as well as choose background colors, wallpaper, links and a company logo

· Personalize Dashboard content by creating role trees, managing content blocks, and manipulating the composition of the Web page layout

· Explore advanced customization techniques beyond the View Builder

1.1 Report Developer

1）Building Reports Using InfoAssist (For Developers) v7.x

Need to transform enterprise data into compelling reports without the intervention of technical staff? WebFOCUS InfoAssist provides business users with the most advanced – yet simple to use – ad hoc reporting capabilities available in a BI tool. This course explores how to create personal reports and graphs with InfoAssist in a WebFOCUS Managed Reporting environment.

You will learn how to:

· Access Managed Reporting through the Business Intelligence Dashboard and understand its significant components

· Create sophisticated reports and graphs with InfoAssist using existing reporting objects and selective data access with custom reports

· Add structure to reports through sorting and display techniques

· Customize your report headers, footers, and column headings

· Employ efficient data selection techniques

· Create sophisticated virtual fields

· Join multiple tables as needed

· Limit report contents with parameterized selection

2）Building Reports with Report Painter – Part 1 v7.x

What does it take to create all the reports you need for your WebFOCUS application? For this course, you will learn how to use an integrated development environment optimized for building and testing Web reporting applications. You will use WebFOCUS to produce reports and graphs destined for deployment as a Web application.

· Navigate and understand the different components of WebFOCUS Developer Studio

· Create reports using the Report Painter tool:

· Add structure to reports through sorting and display techniques

· Format your report headers, footers, and column headings

· Employ efficient data selection techniques

· Create sophisticated virtual columns

· Join multiple tables as needed

· Limit report contents with static or parameterized selection criteria

· Highlight data with conditional styling

· Establish drill-downs between summary and detail reports

· Display report output in Excel or PDF format

3）Building Reports with Report Painter – Part 2 v7.x

How can you produce highly complex reports, and run them more efficiently in your business environment? With this course, you’ll learn techniques to think critically about a business reporting need and translate it to a very productive WebFOCUS request. You will also be able to look at existing reports and eliminate needless processes.

You will learn how to:

· Decide what tools to use

· Manage report output through Hold files

· Control the destination of an extract file

· Control the order of data displayed

· Rank and group data automatically

· Sort aggregated data

· Use complex screening techniques

· Calculate using subtotal values

· Generate summary information

· Add flexibility to your report request

· Call a procedure from another procedure

· Work efficiently with reports on presorted data

· Select efficient join techniques

· Merge data using the Match Wizard

· Add visual impact to reports

· Highlight data with conditional styling

· Visually display complex data

4）Managed Reporting Content Development Using InfoAssist v7.x

How do you create the reports and other content required by users of a Managed Reporting Environment? This course will teach you how to manage domains that limit access to certain data, create standard reports that users execute, and define reporting objects from which users can create their own reports and graphs.

You will learn how to:

· Build standard reports with InfoAssist

· Create reporting objects

· Build and graph templates

· Publish reports

· Work with data servers

· Implement change management

1.2 Application Developer

1）Building a WebFOCUS Dashboard v7.x

How can you quickly and easily create a customized interface for WebFOCUS Managed Reporting? With WebFOCUS Business Intelligence Dashboard, you will learn how to animate a Managed Reporting repository with portal-like functionality for your reporting applications on the Web.

You will learn how to:

· Create a customized interface for public and group views using the tools of the Dashboard View Builder

· Customize the look of a Dashboard by selecting from a variety of convenient templates, as well as choose background colors, wallpaper, links and a company logo

· Personalize Dashboard content by creating role trees, managing content blocks, and manipulating the composition of the Web page layout

· Explore advanced customization techniques beyond the View Builder

2）Creating Metadata for Data Sources v7.x

When your application accesses a data source, it needs to know how to interpret the data used in the application. With this course, you’ll learn how to create, manage, and enhance a data source description (Master File), a key component in a WebFOCUS application.

You will learn how to:

· Generate a Synonym for an existing relational data source

· Create a Master File using the Synonym Editor in Developer Studio

· Explore the required and optional attributes for a field

· Edit a Master File using the Text Editor in Developer Studio

· Add a virtual field to a segment

· Interpret the Master File for a HOLD file

· Create a cross reference to another file

· Create a Business View from an existing data source description

3）Designing a User Interface for a Web Application With the HTML Composer v7.x

Release 7.x

How can you make your WebFOCUS reports and graphs available in a Web application? This course will show you how to build a cohesive, efficient, professional user interface for your reporting application. Using Developer Studio’s HTML Composer tool, you will build a Web application and discuss how it can be deployed in several different environments.

You will learn how to:

· Graphically build a user interface for your Web application using components such as tabs, images, reports, graphs, frames, lines, text and others

· Incorporate WebFOCUS parameterized procedures and form controls, including list boxes, drop-down lists, calendars, sliders, radio buttons and check boxes

· Create Active pages where components interact with each other and their controls to efficiently analyze business situations

· Fully test your application and all its components

· Discuss different deployment scenarios and their considerations

4）Developing Composite Reports v7.x

Release 7.x

Do you want to design and build sophisticated reports that your company will use every day? How about reports that will capture key performance indicator status or show current business scenarios in a variety of graphical and tabular formats all on one page? This course will show you how.

You will learn how to:

· Lay out composite reports that involve graphical and tabular components

· Provide both browser and true printer-ready versions

· Build HTML launch pages for parameter driven reporting

· Build Web pages that invoke dynamic dependent controls without additional coding

· Enable reports to launch multiple drilldown reports from a single link

5）Reporting Concepts for Relational Databases

How can you optimize reporting from your Relational Database Management Systems (RDBMS), while maintaining the accuracy and integrity of the reports? This course enables you to analyze your reporting results from the RDBMS, determine where any potential performance gains can be found, and make any necessary adjustments to speed the process from raw data to finished report.

You will learn how to:

· Describe the process of adapter optimization

· Recognize potential report inefficiencies

· Analyze database metadata and know what attributes affect reporting efficiency

· Explore syntax that relates to the RDBMS

· Determine where optimization commands can be best utilized

· Enable RDBMS optimization for all reports

· Write efficient code with hands-on techniques that work with your RDBMS

· Save time in your report request processing

· Improve reporting system performance from your RDBMS

6）Business Analyst

WebFOCUS Reporting for Business Analysts Using InfoAssist v 7.x

What is the secret to your success as a business analyst? With WebFOCUS Managed Reporting, you will be able to access your company’s data and execute a variety of predefined standard reports. Furthermore, you will be able to develop your own sophisticated ad hoc reports.

You Will Learn How To:

· Access Managed Reporting through the Business Intelligence Dashboard and understand its significant components

· Execute a variety of standard reports and graphs that demonstrate WebFOCUS features, such as drill-downs and on-demand paging

· Submit, review, and save output of standard reports executed in deferred receipt mode

· Use the InfoAssist tool to build sophisticated ad hoc reports based on reporting objects

· Customize your ad hoc reports with headers and footers, fonts, and color

· Highlight information in your ad hoc reports through the use of conditional styling

· Turn your ad hoc report data into Business Intelligence by adding virtual fields

· Select just the right data for your analysis with filters, or build your own selection statements

· Select your ad hoc report display format as HTML, Excel, and Adobe Acrobat

· Share your work with other Managed Reporting users

· Use the OLAP tool to “slice and dice” report data for additional analysis

7）Managed Reporting Administration v7.x

How can you give users easy access to the information they require while protecting sensitive or confidential data? With the WebFOCUS Managed Reporting Environment, you can control the users’ environment by building domains that limit access to certain data, creating users and groups, and assigning privileges for accessing the Managed Reporting environment.

You will learn how to:

Add a new WebFOCUS environment

Create domains

Copying existing procedures into domains

Create and Manage Users, Groups, and Roles

Enhance metadata and procedures with OLAP (Online Anaytical Processing) capabilies

Generate reports about your Managed Reporting Applications

Use Impact Analysis to analzye your procedures

Use the WebFOCUS Administration console to modify your application

1.3 WebReport Language

This documentation describes how to use WebFOCUS to create and style tabular reports, generate a wide variety of graphs, prepare data for those reports and graphs, and enable links to provide additional information. It is intended for application developers and other information technology professionals who use WebFOCUS to create and deploy reporting applications on the Internet or corporate intranets.

1. JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS J1

2. DEFINE FILE EMPDATA

YEAR/YY=COURSESTART;

3. END

4. TABLE FILE EMPDATA

5. HEADING CENTER "Education Cost vs. Salary"

6. SUM EXPENSES AS 'Education,Cost' SALARY AS 'Current,Salary'

7. AND COMPUTE PERCENT/D8.2=EXPENSES/SALARY * 100; AS 'Percent'

8. BY DIV

BY DEPT

9. WHERE YEAR EQ 1991

10. ON TABLE SUMMARIZE

11. ON TABLE SET STYLE *

TYPE=HEADING, STYLE=BOLD, COLOR=BLUE,$

TYPE=REPORT, FONT=TIMES, SIZE=8,$

TYPE=REPORT, GRID=OFF,$

ENDSTYLE

12. END

1）Display command

display [THE] [SEG.]fieldname1 [AND] [THE] fieldname2 ...

display *

PRINT, LIST, SUM, or COUNT command. WRITE and ADD are synonyms of SUM

TABLE FILE EMPLOYEE

LIST LAST_NAME AND FIRST_NAME

END

TABLE FILE EMPLOYEE

PRINT LAST_NAME AND FIRST_NAME

END

CHECK FILE CENTORD PICTURE

SUM ADD Write CURR_SAL

COUNT EMP_ID

BY DEPARTMENT

COUNT LAST_NAME AND DEPARTMENT AND JOBCODE

SET COUNTWIDTH = ON, set count width=9

2）Manipulating Display Fields With Prefix Operators

http://ecl.informationbuilders.com/wf/index.jsp?topic=%2Fshell_7702%2Fopener.htm

SUM AVE.ED_HRS BY DEPARTMENT

SUM DELIVER_AMT AND ASQ.DELIVER_AMT BY CITY

SUM CNT.DST.ED_HRS

COUNT DST.ED_HRS

TABLE FILE GGSALES

COUNT CATEGORY AS 'TOTAL,COUNT'

BY REGION

SUM CNT.CATEGORY AS 'STATE,COUNT'

CNT.DST.CATEGORY CNT.DST.PRODUCT

BY REGION

BY ST

END

SUM FST.EMP_ID

DEFINE FILE EMPDATA

TODAY/YYMD = &YYMD;

YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');

END

TABLE FILE EMPDATA

PRINT SALARY

RNK.YRS_SERVICE AS 'RANKING,BY,SERVICE'

RNK.SALARY AS 'SALARY,RANK'

BY DEPT

BY HIGHEST YRS_SERVICE

BY HIGHEST SALARY NOPRINT

WHERE DEPT EQ 'MARKETING' OR 'SALES'

ON TABLE SET PAGE NOPAGE

END

DEFINE FILE EMPDATA

TODAY/YYMD = &YYMD;

YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');

END

TABLE FILE EMPDATA

PRINT LASTNAME FIRSTNAME RNK.SALARY

BY HIGHEST YRS_SERVICE BY HIGHEST SALARY

WHERE TOTAL RNK.SALARY LE 2

END

DEFINE FILE EMPDATA

TODAY/YYMD = &YYMD;

YRS_SERVICE/I9 = DATEDIF(HIREDATE,TODAY,'Y');

END

PRINT RNK.SALARY RNK.YRS_SERVICE

COMPUTE FLAG/A1 = IF RNK.SALARY LE 5 AND RNK.YRS_SERVICE LE 6

THEN 'Y' ELSE 'N';

BY DEPT BY SALARY BY YRS_SERVICE

WHERE DEPT EQ 'MARKETING' OR 'SALES'

ON TABLE SET PAGE NOPAGE

END

3）Displaying Pop-up Field Descriptions for Column Titles

Popupdesc

4）Sort

vertical (BY) and horizontal (ACROSS) phrases

PRINT LAST_NAME BY DEPARTMENT

SET BYDISPLAY = {OFF|ON}

SET BYDISPLAY command, you can display the appropriate BY field on every row of a report produced in a styled output format, usable by Excel

PRINT FIRST_NAME LAST_NAME

BY FIRST_NAME NOPRINT

Multiple Vertical (BY) Sort Fields: by first by second by third_column

[RANKED] BY {HIGHEST|LOWEST|TOP|BOTTOM} nsrtfield [AS 'text']

[PLUS OTHERS AS 'othertext']

[IN-GROUPS-OF m1 [TOP n2]]

[IN-RANGES-OF m3 [TOP n4]

PRINT CURR_SAL LAST_NAME

BY HIGHEST 2 ED_HRS

PLUS OTHERS AS 'Others'

END

TABLE FILE EMPLOYEE

PRINT CURR_SAL LAST_NAME

BY LOWEST 2 ED_HRS

PLUS OTHERS AS 'Others'

IN-GROUPS-OF 25 TOP 50 --------- in this group, only show the top 50

END

The output is:

ED_HRS CURR_SAL LAST_NAME

------ -------- ---------

.00 $9,500.00 SMITH

$29,700.00 BANNING

$21,120.00 ROMANS

$18,480.00 MCCOY

25.00 $11,000.00 STEVENS

$13,200.00 SMITH

$26,862.00 IRVING

$9,000.00 GREENSPAN

$27,062.00 CROSS

Others $56,360.00

PRINT LAST_NAME ACROSS DEPARTMENT

PAGE 1

LAST_NAME

DEPARTMENT

STEVENS

PRODUCTION

SMITH

MIS

JONES

MIS

SMITH

PRODUCTION

BANNING

PRODUCTION

IRVING

PRODUCTION

ROMANS

PRODUCTION

MCCOY

MIS

BLACKWOOD

MIS

MCKNIGHT

PRODUCTION

GREENSPAN

MIS

CROSS

MIS

PAGE 1

DEPARTMENT

MIS

PRODUCTION

STEVENS

SMITH

JONES

SMITH

BANNING

IRVING

ROMANS

MCCOY

BLACKWOOD

MCKNIGHT

GREENSPAN

CROSS

within

WITHIN a sort group, require by, can be used with a prefix operator

SUM and COUNT commands aggregate an entire column

{SUM|COUNT} display_field WITHIN by_sort_field [WITHIN across_sort_field]

BY by_sort_field [ACROSS across_sort_field]

Sum something within groupName by groupName

SUM UNIT_SOLD AS 'UNITS'

AND PCT.UNIT_SOLD AS 'PCT,SOLD,WITHIN,TABLE'

AND PCT.UNIT_SOLD WITHIN STORE_CODE AS 'PCT,SOLD,WITHIN,STORE'

BY STORE_CODE SKIP-LINE BY PROD_CODE

Collation Settings: SRV_CI and SRV_CS, case sensitive, in-sensitive

SET COLLATION = {BINARY|SRV_CI|SRV_CS|CODEPAGE}

{BY|ACROSS} {LOWEST|HIGHEST/top} sortfield

PRINT LAST_NAME BY LOWEST CURR_SAL

Sort default in ascending order

PRINT LAST_NAME

BY BANK_NAME ROWS 'BEST BANK' OVER STATE

OVER ASSOCIATED OVER 'BANK ASSOCIATION'

END

TABLE FILE EMPLOYEE

SUM CURR_SAL

ACROSS BANK_NAME COLUMNS 'BEST BANK' AND STATE

AND ASSOCIATED AND 'BANK ASSOCIATION'

END

Rank

PRINT LAST_NAME RANKED AS 'Sequence' BY CURR_SAL

sort a report

IN-GROUPS-OF 5000, $5,000 increments

IN-RANGES-OF ,

FOR, Tiles. These include percentiles, quartiles, or deciles

PRINT LAST_NAME

BY CURR_SAL IN-GROUPS-OF 5000

User defined grouping:

PRINT LAST_NAME

FOR CURR_SAL

9000 TO 13500 OVER

14000 TO 19700 OVER

19800 TO 30000

Group numeric data into any number of tiles --- segmentation (percentiles=100, deciles=10, quartiles=4)

TABLE FILE EMPLOYEE

PRINT LAST_NAME FIRST_NAME

BY DEPARTMENT

BY CURR_SAL IN-GROUPS-OF 5 TILES

END

TABLE FILE EMPLOYEE

PRINT LAST_NAME FIRST_NAME

BY DEPARTMENT

BY LOWEST 3 CURR_SAL IN-GROUPS-OF 5 TILES

END

The employees with the three lowest salaries are listed and grouped into five tiles, but only the tiles that are in the top 3 (tiles 1, 2, or 3) are displayed in the report

PRINT LAST_NAME FIRST_NAME

BY DEPARTMENT

BY LOWEST 3 CURR_SAL IN-GROUPS-OF 5 TILES TOP 3 AS DECILES

TABLE FILE EMPLOYEE

PRINT LAST_NAME

BY HIGHEST 5 CURR_SAL

END

TABLE FILE EMPLOYEE

SUM SALARY CNT.SALARY

BY DEPARTMENT

BY HIGHEST 2 TOTAL AVE.SALARY AS 'HIGHEST,AVERAGE,SALARIES'

BY CURR_JOBCODE

END

Sorting, Aggregating, and Ranking Report Columns

TABLE FILE EMPLOYEE

SUM SALARY CNT.SALARY

BY DEPARTMENT

RANKED BY HIGHEST 2 TOTAL AVE.SALARY AS 'HIGHEST,AVERAGE,SALARIES'

BY CURR_JOBCODE

END

TABLE FILE EMPLOYEE

SUM SALARY CNT.SALARY

BY DEPARTMENT

BY HIGHEST 2 TOTAL COMPUTE MONTHLY_SALARY/D12.2M=SALARY/12;

AS 'HIGHEST,MONTHLY,SALARIES'

BY CURR_JOBCODE

END

TABLE FILE EMPLOYEE

PRINT LAST _NAME FIRST_NAME HIRE_DATE

BY HIRE_DATE NOPRINT

END

Using Multiple Display and Sort Fields

TABLE FILE EMPLOYEE

SUM CURR_SAL

SUM CURR_SAL BY DEPARTMENT

SUM CURR_SAL BY DEPARTMENT BY LAST_NAME

END

Display in duplicate column

SET DUPLICATECOL={ON|OFF}

TABLE FILE EMPLOYEE

SUM CURR_SAL ED_HRS

SUM CURR_SAL ED_HRS BY DEPARTMENT

END

SET DUPLICATECOL={ON|OFF}

SET SPACES = 1

TABLE FILE EMPLOYEE

SUM CURR_SAL ED_HRS

SUM CURR_SAL ED_HRS BY DEPARTMENT AS 'DEPT'

PRINT FIRST_NAME CURR_SAL ED_HRS BY DEPARTMENT BY LAST_NAME

END

SET DUPLICATECOL = OFF

TABLE FILE EMPLOYEE

SUM CURR_SAL ED_HRS BY DEPARTMENT

PRINT FIRST_NAME CURR_SAL ED_HRS BY DEPARTMENT BY LAST_NAME

ON TABLE SET STYLE *

TYPE = REPORT, COLUMN= P4, VERBSET = 1, STYLE = ITALIC, COLOR=BLUE,$

TYPE = REPORT, COLUMN= P3, VERBSET = 2, STYLE = ITALIC, COLOR=GREEN,$

TYPE = REPORT, COLUMN= B2, VERBSET = 3, STYLE = UNDERLINE, COLOR = RED,$

ENDSTYLE

END

P (print)means that column number on the report , including BY columns.

B (by)means BY field column

Improving Efficiency With External Sorts

Internal sort procedure is optimized for reports from 180 to 200,000

MainFrame: SyncSort, DFSORT

External sort: extsort

Selecting Records Overview

Selections Based on Individual Values.

Selection Based on Aggregate Values

Select by existence of missing values for a field, whether field values fall within a range, or whether a field does not contain a certain value

Where > If

TABLE FILE EMPLOYEE

SUM CURR_SAL

BY DEPARTMENT

WHERE TOTAL CURR_SAL EXCEEDS 110000

END

TABLE FILE EMPLOYEE

SUM CURR_SAL

BY LAST_NAME AND BY FIRST_NAME

WHERE TOTAL CURR_SAL EXCEEDS 20000

WHERE DEPARTMENT IS 'MIS'

END

WHERE (SEATS EQ 2) AND (SEATS NOT-FROM 3 TO 4)

WHERE (LAST_NAME EQ 'CROSS') OR (LAST_NAME EQ 'JONES')

WHERE (LAST_NAME EQ 'CROSS' OR 'JONES')

TABLE FILE EMPLOYEE

PRINT CURR_SAL BY LAST_NAME

WHERE (LAST_NAME EQ 'CROSS' OR 'JONES')

AND (CURR_SAL GT 22000)

END

WHERE Operators:

EQ,IS,NE,IS-NOT,GE,GT,EXCEEDS,IS-MORE-THAN,LT,IS-LESS-THAN,LE,GE lower AND

LE upper,LT lower OR GT upper,FROM lower,TO upper,IS-FROM lower,TO upper,NOT-FROM lower

TO upper,IS MISSING,IS-NOT MISSING ,NE MISSING,CONTAINS,LIKE,OMITS ,NOT LIKE,INCLUDES, EXCLUDES

IN (z,x,y),NOT ... IN (z,x,y) ,IN FILE ,NOT ... IN FILE

IF Operator:

EQ,IS,NE,IS-NOT,GE,FROM,IS-FROM,GT,EXCEEDS,IS-MORE-THAN,LT,IS-LESS-THAN,LE, TO, IS-FROM lower,TO upper,NOT-FROM lower ,TO upper,IS MISSING ,IS-NOT MISSING ,NE MISSING, CONTAINS,LIKE,OMITS,UNLIKE,INCLUDES,EXCLUDES

Range Tests With FROM and TO and

Range Tests With GE and LE or GT and LT

Missing Data Tests

Screening on Masked Fields

Qualifying Parent Segments Using INCLUDES and EXCLUDES

Limit the Number of Records Read

WHERE RECORDLIMIT EQ n

Using Multiple IF Phrases

TABLE FILE EMPLOYEE

PRINT EMP_ID LAST_NAME

IF SALARY GT 20000

IF DEPARTMENT IS MIS

IF LAST_NAME IS CROSS OR BANNING

END

Selection Values From a File:

WHERE [NOT] fieldname EQ, IS, NE,

IS-NO/IN FILE file

IF fieldname operator (file) [OR (file) ... ]

TABLE FILE GGPRODS

SUM UNIT_PRICE

BY PRODUCT_DESCRIPTION

IF PRODUCT_ID IS (FileName)

END, ---------------the following have the same function

IF PRODUCT_DESCRIPTION EQ 'B141' or 'B142'

Assigning Screening Conditions to a File -- Filter

A filter is a packet of definitions that resides at the file level
Declare a common set of screening conditions that apply each time you retrieve data from a data source

Define a filter

FILTER FILE filename [CLEAR|ADD]

[filter-defines;]

NAME=filtername1 [,DESC=text]

where-if phrases

NAME=filternamen [,DESC=text]

where-if phrases

END

FILTER FILE CAR ADD

MARK_UP/D7=RCOST-DCOST;

NAME=UK

WHERE MARK_UP GT 1000

END

FILTER FILE CAR CLEAR

NAME=ASIA,DESC=Asian cars only

IF COUNTRY EQ JAPAN

NAME=UK

IF COUNTRY EQ ENGLAND

NAME=LUXURY

IF RETAIL_COST GT 50000

END

Activating and Deactivating Filters

SET FILTER= {*|xx[yy zz]} IN file {ON|OFF}

SET FILTER= {*|filterName1 filterName2 …} IN file {ON|OFF}

Example: SET FILTER = A B C IN CAR ON

SET FILTER = D E F IN CAR ON

SET FILTER = G IN CAR OFF

Query the Status of Filters

? FILTER [{file|*}] [SET] [ALL]]

? FILTER ---- Query All the fileters.

? FILTER * SET

Preserve Filter Definitions With KEEPFILTERS

SET KEEPFILTERS = {OFF|ON}

Temporary Fields

A virtual field (DEFINE) is evaluated as each record that meets the selection criteria

A calculated value (COMPUTE) is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed.

Totals and Subtotals

Row and column totals, grand totals, and subtotals.

ROW-TOTAL to display a new column containing the sum of all numbers in each row.
COLUMN-TOTAL to display a final row on the report, which contains the totals for each column of numbers.

display_command fieldname AND ROW-TOTAL [alignment][/format] [AS 'name']

display_command fieldname AND COLUMN-TOTAL [alignment][AS 'name']

ON TABLE COLUMN-TOTAL [alignment][AS 'name'][field field field]

ON TABLE ROW-TOTAL [alignment][/format] [AS 'name']

TABLE FILE SALES

SUM RETURNS DAMAGED AND ROW-TOTAL AND COLUMN-TOTAL

BY PROD_CODE

END

TABLE FILE EMPLOYEE

PRINT CURR_SAL

BY LAST_NAME

ON TABLE COLUMN-TOTAL

END

TABLE FILE EMPLOYEE

SUM CURR_SAL AND ROW-TOTAL AND COLUMN-TOTAL

BY BANK_NAME

ACROSS DEPARTMENT

END

TABLE FILE CAR

SUM DCOST RCOST

COMPUTE PROFIT/D12=RCOST-DCOST;

ROW-TOTAL/L/D12 AS 'TOTAL_COST'

BY COUNTRY

ON TABLE COLUMN-TOTAL/L AS 'FINAL_TOTAL'

END

Section Totals and a Grand Total

SUB-TOTAL and SUBTOTAL

SUMMARIZE and RECOMPUTE (used with calculated values)

RECAP and COMPUTE

see Conditionally Displaying Summary Lines and Text).

SET DROPBLNKLINE=ON | OFF --- remove the blank line on report

You can use prefix operators with SUBTOTAL, SUB-TOTAL, SUMMARIZE, and RECOMPUTE

TABLE FILE EMPLOYEE

SUM DED_AMT BY DED_CODE BY DEPARTMENT

BY BANK_ACCT

WHERE BANK_ACCT NE 0

WHERE DED_CODE EQ 'CITY' OR 'FED'

ON DEPARTMENT SUBTOTAL

END

SUB-TOTAL / SUBTOTAL /NOTOTAL
SUMMARIZE and RECOMPUTE
SUB-TOTAL and SUBTOTAL

SUBTOTAL, SUB-TOTAL, RECOMPUTE, and SUMMARIZE commands used at ON TABLE level

prefix operators used with SUBTOTAL, SUB-TOTAL, RECOMPUTE, SUMMARIZE at both the sort break and grand total levels.

numeric prefix: ASQ. AVE. CNT. FST. LST. MAX. MIN. SUM.

alphanumeric prefix :FST. LST. MAX. MIN.

Suppress grand totals: ON TABLE NOTOTAL

SUBFOOT, SUBHEAD, SUBTOTAL, SUB-TOTAL, SUMMARIZE, RECOMPUTE, and RECAP

Expression:

Numeric: COMPUTE BONUS/D12.2 = CURR_SAL * 0.05 ;
Date: COMPUTE DELIVERY/MDY = SHIPDATE + 5 ;
Character: COMPUTE FIRST_INIT/A1 = EDIT (FIRST_NAME, '9$$$$$$$$$') ;
Logical: return true or false
Conditional. A conditional expression (IF ... THEN ... ELSE) returns a numeric or alphanumeric value

Example:

COMPUTE STATE/A2 = 'NY';
DEFINE FILE EMPLOYEE TITLE/A19 = 'DR. ' | LAST_NAME; END
DEFINE FILE EMPLOYEE INITIAL/A1 = EDIT(FIRST_NAME, '9$$$$$$$$$$'); END

Concatenating Character Strings

DEFINE FILE EMPLOYEE

FIRST_INIT/A1 = EDIT(FIRST_NAME, '9$$$$$$$$$');

NAME/A19 = LAST_NAME ||(', '| FIRST_INIT |'.');

END

| Preserves trailing blanks.

|| -- Moves trailing blanks to the end of a concatenated string

CONTAINS and OMITS

x LIKE mask ...

Logical Operators

EQ,NE,GE,GT,LE,LT,AND,OR,NOT,CONTAINS,OMITS,IS MISSING,IS-NOT MISSING

value {EQ|NE} value value {LE|LT} value value {GE|GT}

valuecharacter_value {CONTAINS|OMITS} character_value

Boolean Expression

(relational_expression) {AND|OR} (relational_expression)

NOT (logical_expression)

IF expression1 THEN expression2 [ELSE expression3]

DEFINE FILE EMPLOYEE

BANK_NAME/A20 = IF BANK_NAME EQ ' ' THEN 'NONE'

ELSE BANK_NAME;

END

DEFINE FILE EMPLOYEE

MYTEST= (CURR_SAL GE 11000) OR (DEPARTMENT EQ 'MIS');

END

TABLE FILE EMPLOYEE

PRINT CURR_SAL AND DEPARTMENT

BY EMP_ID

IF MYTEST IS TRUE

END

Saving and Reusing Your Report Output

HOLD command – save output with a master file

Save command – save output without masterfile

Saveb Command – save output without master file in binary format

PCHOLD command – create output result, download to clients’ PC plathform

Specify a Storage Location for a HOLD Master File

APP MAP appname path_to_directory
APP HODDATA appname
APP HOLDMETA appname

APP MAP holdapp /root/dir --- specify save dir for the master files

APP HOLDDATA holdapp

APP HOLDMETA holdapp

TABLE FILE GGSALES

PRINT SEQ_NO CATEGORY PRODUCT

ON TABLE HOLD AS SALES ----- use hold command to save a SALES.mas file to that dir

END

Save a table or a column to filename

ON TABLE SAVE [AS filename] [FORMAT fmt] [MISSING {ON|OFF}]
save_field SAVE [AS filename] [FORMAT fmt] [MISSING {ON|OFF}]

Examples:

ON TABLE SAVE FILENAME 'dir\filename.ext' FORMAT fmt(HTML, HTMTABLE, DHTML / ALPHA, DOC, PDF, WP, Text/ DIF, EXCEL, EXL2K, LOTUS, (WK1), SYLK/COMMA, COM, COMT)

TABLE FILE EMPLOYEE

PRINT LAST_NAME AND FIRST_NAME

BY DEPARTMENT

ON TABLE SAVE

END

ON TABLE PCHOLD FORMAT EXCEL

Supported data format

AHTML

AHTMLTAB

ALPHA

APDF

BINARY

CLIP

COMMA

COM

COMT

DB2

DBASE

DFIX

DHTML

DIF

DOC

EXCEL

EXL2K

EXL2K FORMULA

EXL2K PIVOT

EXL97

FLEX

FOCUS

GIF

HTML

HTMTABLE

INGRES

INTERNAL

JPEG

LOTUS

PDF

POSTSCRIPT (PS)

PPT

REDBRICK

SQL

SQLDBC

SQLINF

SQLMAC

SQLMSS

SQLODBC

SQLORA

SQLSYB

SYLK

TAB

TABT

VISDIS

WK1

XFOCUS

Create a Delimited Sequential File

ON TABLE {HOLD|PCHOLD} [AS filename] FORMAT DFIX

DELIMITER delimiter [ENCLOSURE enclosure] [HEADER {YES|NO}]

TABLE FILE CENTORD

SUM QUANTITY LINEPRICE BY REGION BY YEAR

ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER |

END

Creating a Tab-Delimited File

TABLE FILE CENTORD

SUM QUANTITY LINEPRICE BY REGION BY YEAR

ON TABLE HOLD AS TAB1 FORMAT DFIX DELIMITER TAB

END

Linking a Report to Other Resources

Link Reports to Procedures:

TYPE=type, [subtype], FOCEXEC=fex[(parameters ...)], [TARGET=frame,] [ALT = 'description',] $

Lans2Us

Monday, April 13, 2015

WebFocus Syntax