Hi,

I am trying to better understand how to code using Web Focus. SQL is not embedded, I have alraedy tried. I have been succesful in code my basic queries using the very helpful discussion on this site.

Can someone direct me to which programming language I should read up on?

Thanks,
Shyam

Shyam, Focus is a programming language.
The most powerful of all programming languages.
You need to start with the 4 basic manuals that come with your software.
You can also write in sql within focus. But you need to start with the 4 basic books; you can order them from the Publications site on the ibi.com home page. you'll need your site code.

A great book to get is the Keysheet book (recent topic) once you understand FOCUS. But I would start with 'Creating Reports With WebFOCUS Language 5.3'.

This message has been edited. Last edited by: Kerry,

This topic brings up the fundamental question: What is WebFOCUS, and how to start to work with it ?

In a short way: WebFOCUS is the reporting engine which turns the corporate data into the meaningful information, complemented with the web components for presenting the information, and interacting with the end-users.

There are two parts of each WebFOCUS system to develop
(from the programmer point of view):

• the web interface - this is done using standard web technologies
  (any of them can be used: DHTML, J2EE, PHP, ASP etc., etc.)
  
• the report logic - lets FOCUS on this
  

The report logic can be divided further in two parts:

• data access logic (data integration) - the metadata layer
  (data adapters configuration, synonyms: master files, access files)
  
• data processing and formating logic - this is done using FOCUS 4GL
  

FOCUS 4GL language contains several different dialects (aka requests).
Each one of the dialects can be considered separately as the language for the autonomous data processing engine, but the dialects can be seamlessly combined into one processing procedure.

There are following FOCUS 4GL dialects:
MODIFY - language for transactional processing, usually used for batch processing
MAINTAIN - laguage for transatcional processing, usually used for the interactive applications
(MODIFY and MAINTAIN are not necessary for reporting, although sometimes useful for very specific reporting tasks.)
TABLE - report definition language. The result of the TABLE request is the tabular (or free-form) report, or the temporary data set for further processing. TABLE request is essential for understanding FOCUS 4GL reporting capabilities.

GRAPH - chart definition language. The result is a graphical chart. You can consider GRAPH as the TABLE request cut to charting. Understanding the TABLE and the specification of the chart type (pie, line, bar, ...) allows to understand the CHART request.
SQL - Yes (!), from the (Web)FOCUS developer point of view SQL is yet another FOCUS 4GL dialect. Moreover, every platform-specific SQL flavour (Oracle, DB2, MS SQL, ...) is a FOCUS dialect.
SQL does not have to be embedded in FOCUS 4GL as it is a part of the FOCUS 4GL core language.

It is good to think of FOCUS 4GL as the description language not a programming language. Trying to compare FOCUS to any of the procedural database programming languages (like Oracle PL/SQL, Informix 4GL/SPL, Sybase/MS T-SQL, etc.) may lead to misunderstanding - FOCUS is entirely different (I do not even mention about comparing FOCUS to general purpose languages like Java, C, Pascal, VB ...).
Coding in FOCUS you just describe (declare) what have to be done, and the reporting engine takes care about the rest. The declarative nature of FOCUS makes the reporting very (i.e. extremely) effective, but it demands understanding of TABLE syntax basis, and how the syntax constructions translates into the report results.
Fortunately, TABLE language is simple, and its syntactic constructs are directly related to the typical elements of the most common business-oriented reports.

Business reporting needs are not always simple, so FOCUS TABLE has many sophisticated constructs, as well.
I think that it is good to start with the most simple reports, and then extend it with more advanced constructs,
learning them by browsing the documentation, asking on the forum, or contacting IBI TechSupport.

I recommend the following learning path:

• read the "Trivialized kick-start guide to FOCUS TABLE", placed below (on your own risk)
  
• read "WebFOCUS Quick Reference Card" (should be included with the software),
  experiment with most interesting syntax constructs.
  
• browse through the following manuals:
  "Creating Reports with WebFOCUS Language .."
  "Developing Reporting Applications ..."
  "Using Functions ..."
  "Desribing Data ..."
  
• use GUI tools to create reports, and read the generated code.
  
• develop end experiment
  

----------------------------------------------------------------------------------------
Trivialized kick-start guide to FOCUS TABLE request
(does not include anything about many important constructs and elements)
WARNING: May cause the mental damages to serious WebFOCUS developers !

1. The overall structure of the TABLE request looks like this:


TABLE FILE data_source
[set section]
[heading section]
[footing section]
print phrase [AS 'column title']
[sort phrases [AS 'column title']
[subtotal phrases]
[filters]
[formating section]
[output section]
END


2. The data source
Specifies the name of the configured synonym, which will be used as the datasource for the report.

Example: TABLE FILE CAR
(use CAR synonym as the data source)

3. PRINT phrase
Print phrase specifies what fields from the data source will be printed within the report, and how they are aggregated (or not).
From the dimensional modeling point of view, the print phrase specifies measures presented within the report. It is somehow (but not very exactly) similar to the SELECT phrase of SQL.

syntax:

PRINT field1
...
fieldn

or

SUM [prefix_operator.]field1
...
[prefix_operator.]fieldn


PRINT - does not aggregate the data
SUM - aggregate data according to prefix_operator
prefix_operator. - one of the: SUM. (summary) - default aggregation method,
AVE. (average), MIN. (minimum), MAX. (maximum)

Example 1:
TABLE FILE CAR
PRINT CAR SALES AS 'Car sales'
END

Example 2:
TABLE FILE CAR
SUM SALES SUM.SALES AVE.RETAIL_COST AS 'Retail cost' MAX.DEALER_COST
END

4. Sort phrases
The fields from the print phrase are sorted and aggregated according to the sort phrases. From the dimensional modeling point of view each sort phrase contains a dimension. It is somehow similar to the GROUP BY and ORDER BY phrases used together in SQL.

syntax:

BY sort_field1
...
BY sort_fieldn


Example 1:
TABLE FILE CAR
SUM SALES
BY COUNTRY
END

Example 2:
TABLE FILE CAR
SUM SALES
BY MODEL
BY CAR
BY COUNTRY AS 'Made in'
END

5. Filters
Used to filter out unnecessary data from the report. Similar to the WHERE clause in SQL.

syntax:

WHERE condition1 AND|OR condition2 ...

conditions are combined using AND or OR logical operators,and parentheses.
Each of the conditions has the following form:
field operator value
field - the field from the data source
operator - one of the following EQ (equals), NE (not equal), LT (lower than), LE (lower or equal), GT (grater than), GE (greater or equal).
It can be of the form:
text_field LIKE 'pattern'
where the pattern is the same as text pattern in standard SQL (_ single character, % the sequence of characters).

Example 1:
TABLE FILE CAR
SUM SALES
BY CAR
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
END

Example 2:
TABLE FILE CAR
SUM SALES
BY MODEL
BY COUNTRY
WHERE COUNTRY LIKE '%A%'
END

6. Heading and footing
The heading and footing sections contains the text wchich will be placed for the header and footer on every page of the report.

syntax:

HEADING|FOOTING [CENTER]
"Text to be displayed"


Example:
TABLE FILE CAR
HEADING
"The heading of the page"
FOOTING CENTER
"The footing of the page"
SUM SALES
BY COUNTRY
END

7. SET section
Various settings for the report. The most common:
ON TABLE SET HTMLCSS ON - always use when the output is HTML
ON TABLE SET PAGE-NUM OFF - do not display the page numbers
ON TABLE SET LINES nn - how many lines on the page
ON TABLE SET LINES 999999 - do not divide report into pages

Example:
TABLE FILE CAR
ON TABLE SET PAGE-NUM OFF
SUM SALES
BY COUNTRY
END

8. Subtotal phrases
Places the summary line for each of the sort fields, displaying aggregated print fields values.

syntax:

ON sort_field SUBTOTAL print_field_list

where print_field_list is of the form:
[prefix_operator. ] print_field
prefix_operator. is one of the SUM. AVE. MIN. MAX., but
there is a space ' ', after the dot '.'.
print_filed is one of the field from the print phrase.

Example:
TABLE FILE CAR
PRINT SALES DEALER_COST RETAIL_COST
BY MODEL
BY CAR
BY COUNTRY
ON CAR SUBTOTAL AVE. SALES SUM. DEALER_COST MIN. RETAIL_COST
END

9. Formating section
Specifies the formating options (fonts, colors, etc.) for the report elements.

syntax:

ON TABLE SET STYLE *
TYPE=report_element_type, [COLUMN=column_name], format_property1=value1,
format_property2=value2, ..., format_propertyn=valuen,$
...
TYPE=report_element_type, [COLUMN=column_name], format_property1=value1,
format_property2=value2, ..., format_propertyn=valuen,$
ENDSTYLE

report_element_type may be one of the following: REPORT (the whole report),
HEADING (the heading), FOOTING (the footing), DATA (the data values),
TITLE (the column titles).

There is a large number of format properties, the most common are:
COLOR='name of color' (WHITE, BLACK, RED, NAVY, LIME, RED, ...) the
foreground color.
BACKCOLOR='name of color' - the background color
FONT = 'name of font' - (ARIAL, COURIER, HELVETICA, ...)
SIZE = the size of font (8, 9, 10, 11, 12, ...)

Example:
TABLE FILE CAR
SUM SALES RETAIL_COST
BY COUNTRY
ON TABLE SET STYLE *
TYPE=REPORT, BACKCOLOR=YELLOW,$
TYPE=TITLE, SIZE=14, FONT=ARIAL,$
TYPE=DATA, COLUMN=SALES, SIZE=10, FONT=COURIER,$
TYPE=DATA, COLUMN=COUNTRY, COLOR=NAVY,$
ENDSTYLE
END

10. Output section
Specifies the output for the report.
syntax:

ON TABLE HOLD AS temporary_set_name

(saves the result of the TABLE request in the temporary data set for further processing)

ON TABLE PCHOLD AS output_format

where output_format is one of the following: HTML (default),
PDF, EXL2K (MS Excel 2000).

Example:
TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE PCHOLD FORMAT PDF
END

11. Combining TABLE and SQL

To use the result of the SQL SELECT as the data source
for the TABLE, the syntax looks like this:


SQL native_interface
select_statement
;
TABLE
ON TABLE HOLD AS SQLRESULT
END

TABLE FILE SQLRESULT
focus request
END


select_statement is the SQL SELECT with the syntax specific to the native_interface.

native_interface is one of the following
empty - SQL statement is performed on the WebFOCUS server synonyms.
SQL syntax is a subset of the ANSI standard in this case.
SQL - DB2 interface
SQLORA - Oracle
SQLMSS - MS SQL Server
SQLSYB - Sybase
SQLINF - Informix
SQLMYSQL - MySQL
SQLODBC - ODBC interface
... - and many others

Example:
SQL
SELECT COUNTRY, SALES FROM CAR
;
TABLE
ON TABLE HOLD AS CARTMP
END

TABLE FILE CARTMP
SUM SALES
BY COUNTRY
END

applause.
Gregorz, that was the best write up imaginable! could you post all that in a article for the Developer's Corner on FP's home page? A catchy title something like "What is webfocus? A Deveoper's Perspective". Would be a great read, and so encouraging to newcomers.

That was excellent, thank you very much

You may also like to mention that it's a highly parameterizable (!) language (with Dialog Manager) that you can use to generate WebFOCUS code. This is a very valuable part of WebFOCUS.

Susannah, I will contact IBI about posting the article (refined, and with good English correction, I hope).

Pietro, I tried to keep the post as short as possible (really so I did not mention about the -DM, which is of course not only valuable, but invaluable (passing parameters from HTML forms) as well.

Thanks
Grzegorz