What Is WebFOCUS? A Developer's Perspective By Grzegorz Swietlik

As the community of WebFOCUS users and developers grows, the simple but important and fundamental question returns again and again: What is WebFOCUS, and how to deal with it?

In a few words: WebFOCUS is the reporting engine that turns corporate data into meaningful information, complemented with Web components for presenting information and interacting with end users.

It is armed with a set of tools to extend its capabilities and facilitate ease of use, but we will concentrate on the core WebFOCUS technologies.

From the programmer point of view, there are two main parts of each WebFOCUS system to develop:

• Web interface – this is done using standard and typical Web technologies (any of them can be used: DHTML, J2EE™, PHP, ASP, etc.)
• Report logic – let's focus on this!

The report logic can be divided further into two parts:

• Data access logic (data integration), which describes underlying data structures and the physical location of data sources. It is determined by the data adapter's configuration, and the metadata layer, which consists of the set of defined synonyms. Data adapters are usually configured during the WebFOCUS server installation. Synonyms can be created using a reporting server Web console, data management console, or your favorite text editor. You can consider synonyms within the metadata layer as the set of relational tables to report from, and (in most cases) you just don't care about physical locations and database platforms where the tables reside.
• Data processing and formatting logic – this is done using FOCUS 4GL. FOCUS 4GL is the set of several data processing languages named requests, but they could be called the dialects as well. Each one of the dialects can be considered separately, as the language for the autonomous data processing engine, but (if needed) the different FOCUS dialects can be seamlessly combined into one processing procedure.

There are the following FOCUS 4GL dialects:

• MODIFY – a transactional processing language, usually used for batch processing.
• MAINTAIN – a transactional processing language, usually used for the interactive applications. (MODIFY and MAINTAIN are not necessary for reporting, although sometimes useful for very specific reporting tasks..)
• TABLE – the report definition language. The TABLE request describes the data-processing logic, from querying the data source, through more or less sophisticated data transformations, to formatting the results into the meaningful, human-readable report. The result of the single TABLE request can be HOLD AS the temporary data set, thus allowing for multistage and multi-request processing. The TABLE request is essential for understanding FOCUS 4GL reporting capabilities, so when we say "FOCUS," we usually mean "FOCUS TABLE request."
• GRAPH – chart definition language. We can think of GRAPH as the specialized TABLE request, tailored for the graphical charting needs.
• SQL – Yes! From the WebFOCUS developer point of view the SQL language is yet another FOCUS 4GL dialect. Moreover, we can treat every platform-specific SQL flavor (Oracle, DB2, MS SQL) as a FOCUS dialect, assimilated by the SQL pass-through mechanism! SQL does not have to be embedded into FOCUS, as it is a part of the core language.

Think of FOCUS 4GL as the description language, not a programming language. Trying to compare FOCUS with 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.

When you code in FOCUS, you just describe (declare) what has to be done, and the reporting engine takes care of the rest. The declarative nature of FOCUS makes the reporting extremely effective, but it demands understanding of (at least) the TABLE syntax basis, and how the syntax constructions translate themselves into the report results. Fortunately, the TABLE language is simple and concise, and its syntax directly reflects typical elements and requirements of the most common business-oriented reports.

Business reporting needs are not always simple, so FOCUS TABLE has many sophisticated constructs to serve them, and it is easy to get lost among them, especially for the beginners and occasional WebFOCUS developers. The GUI tools provided with WebFOCUS (Report Assistant, Graph Assistant, and Developer Studio) shield you from all of the FOCUS syntax and manual coding and allow you to effectively create useful and impressive reports, but…

The FOCUS reports may be provided with parameters specifying the reporting criteria. The parameters are resolved and handled by the FOCUS facility called Dialogue Manager. The name is slightly misleading – it does not have too much common with dialogue windows, message boxes, etc. (at least outside the MVS world). It is the macro processor that generates FOCUS code on the fly according to provided parameters and defined logic. This facility allows creating very flexible, highly parameterized reports, constructed from elements that may be reused across many reporting systems. To take the full advantage of all the WebFOCUS power and flexibility there is nothing better than the combination of the pure Web technologies (DHTML, J2EE, PHP, ASP, CGI scripting, etc.) with the customized, highly parameterized FOCUS code.

Even though you are going to stay with the GUI tools, understanding the FOCUS reporting language helps to realize what is going on behind the scenes. To learn the FOCUS language, 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 questions on Focal Point, contacting Information Builders' Tech Support, or hiring experienced consultant(s). You can also take the FOCUS language trainings from Information Builders, and continue to read this article.

I recommend the following learning path:

• Read the following "five-minute guide" (at your own risk)
• Read the WebFOCUS Quick Reference Card
• Browse through the following manuals:
  • Creating Reports With WebFOCUS Language
  • Developing Reporting Applications
  • Using Functions
  • Describing Data
• Use GUI tools to create reports, and then read the generated code…develop end experiment

Five-Minute Guide to Simplest Reporting Requests

(Does not cover important constructs and elements like DEFINEd and COMPUTEd fields, built-in functions, SUBFOOTs and SUBHEADs, matrix reporting ACROSS fields, JOINing, MATCHing, and merging data sources, passing parameters to reports, dynamically generated code, graphs, financial reporting, multiverb requests, and many others. Syntactic constructs are excessively simplified!)

Warning: May cause mental damage to serious WebFOCUS developers!

Notational conventions used in this guide, are simple and informal:
[ if_something_is_in_square_brackets_it_means_it_is_rather_optional ]
it_may_repeat_several_times_or_more…
it_may_repeat_several_times_or_more
it_may_repeat_several_times_or_more
it_may_repeat_several_times_or_more
because it was followed by the "…"

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

   TABLE FILE data_source
   [ heading_section ]
   print_phrase
   [ sort_phrase ]...
   [ subtotal_phrase ]...
   [ filter_phrase ]
   [ formating_section ]
   [ output_section ]
   END

2. data_source
   Data source specifies the name of the configured synonym, which will be used as the data source for the report.

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

3. print_phrase (aka verb 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 similar to the SELECT phrase of the SQL syntax.

   syntax:

   PRINT field_name [AS 'column title']
   [field_name [AS 'column title']]...

   or

   SUM [prefix_operator.]field_name [AS 'column title']
   [[prefix_operator.]field_name [AS 'column title']]...

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

   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_phrase
   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 SQL GROUP BY and ORDER BY phrases used together.

   syntax:

   BY field_name [AS 'column title']
   [BY field_name [AS 'column title']]...

   Example 1:

   TABLE FILE CAR
   SUM SALES
   BY COUNTRY
   END

   Example 2:

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

5. heading_section
   The heading_section contains the text that will be placed as the header on every page of the report.

   syntax:

   HEADING [CENTER]
   "Header text to display"

   Example:

   TABLE FILE CAR
   HEADING
   "The heading of the page"
   SUM SALES
   BY COUNTRY
   END

6. filter_phrase
   Used to filter out unnecessary data from the report, or in other words select the necessary data to the report. Similar to the SQL WHERE clause.

   syntax:
   WHERE logical_expression

   logical_expression is combined from simple_logical_expression(s), joined with AND and OR logical operators and parentheses, as usually happens to logical expressions.

   Most of the simple_logical_expressions have the following form: field operator value

   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 (also) in the form of:
   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 COUNTRY
   BY CAR
   WHERE COUNTRY EQ 'ENGLAND'
   END

   Example 2:

   TABLE FILE CAR
   SUM SALES
   BY COUNTRY
   BY MODEL
   WHERE COUNTRY LIKE '%E%'
   END

7. subtotal_phrase
   Places the summary line for the sort field, displaying aggregated print fields values.

   syntax:

   ON sort_field SUBTOTAL print_field_list
   [ON sort_field SUBTOTAL print_field_list]...

   where print_field_list is in the form of: [prefix_operator. ] print_field

   prefix_operator. is one of the SUM. AVE. MIN. MAX. ..., but there is a space " " after the dot "." print_field is one of the fields from the print_phrase.

   Example:

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

8. formating_section
   Specifies the formating options (fonts, colors, etc.) for the report elements.

   syntax:

   ON TABLE SET STYLE *
   TYPE=type, [COLUMN=column_name],
   property=value,[property=value,]...$
   [TYPE=type, [COLUMN=column_name],
   property=value, [property=value,]...$]...
   ENDSTYLE

   type may be one of the following: REPORT (the whole report), HEADING (the heading), DATA (the data values), TITLE (the column titles), and others (of course).

   There is a large number of format property(ies), 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

9. output_section
   Specifies the output of 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), or other.

   Example:

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

10. Using SQL within FOCUS reports
    Sometimes it is convenient to query the data source using SQL instead of FOCUS. It is especially useful if you are the SQL expert who just started to learn FOCUS. The results of the SQL statement can be easily used as the data source for the TABLE request.

    syntax:

    SQL rdbms_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 rdbms_interface.

    rdbms_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
    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

About the Author

Grzegorz Swietlik is a freelance IT consultant and software developer. He specializes in business intelligence and data warehousing, relational databases programming and design, Web programming, Java™ development, and last but not least: WebFOCUS. He is a Sun-Certified Java Developer, and a WebFOCUS Certified Developer. You can contact him at grzsw@poczta.onet.pl.