Reporting Language Enhancements

In this section:

 

WebFOCUS is a complete information control system with comprehensive features for retrieving and analyzing data. It enables you to create reports quickly and easily. It also provides facilities for creating highly complex reports, but its strength lies in the simplicity of the request language. You can begin with simple queries and progress to complex reports as you learn about additional facilities.

WebFOCUS Release 8 includes the Reporting Language new features available in Release 7.7 Version 05.


Top of page

x
Using PowerPoint PPTX Display Format

In this section:

With PowerPoint (PPTX), Microsoft® introduced enhanced functionality in a new presentation file format. WebFOCUS Release 8.0 Version 08 introduces the capability to retrieve data from any WebFOCUS supported data source and to generate a PPTX presentation for data analysis and distribution.

The PPTX format generates fully styled reports in binary display format, which uses the same binary technology that is used for XLSX. When PPTX is specified as the output format, a PowerPoint presentation with a single slide that includes the report is created.

The WebFOCUS procedure generates a new presentation containing your defined report elements, such as headings and subtotals, as well as StyleSheet syntax, such as conditional styling and drill downs.

Additionally, you can add multiple graphs and images to a PowerPoint presentation. The PowerPoint output format can contain a variety of graphs positioned anywhere on a slide to create a visual layout.



x
Using PowerPoint PPTX Templates

You can place report output on a specific slide in a PowerPoint template. This enables you to populate existing presentations with preset Slide Masters, styling, and other business content. PowerPoint design templates (POTX) and PowerPoint macro-enabled templates (POTM) are stored on the server and can be distributed automatically with ReportCaster.



x
Syntax: How to Create PowerPoint PPTX Report Output
ON TABLE {PCHOLD|HOLD|SAVE} [AS name] FORMAT PPTX 
[TEMPLATE {'template.potx'|'template.potm'} SLIDENUMBER n]

where:

name

Is the name of the PowerPoint output file.

template

Is the name of the PowerPoint template file. The template file must have at least one blank slide and must be saved as a .POTX or .POTM extension on your WebFOCUS Reporting Server application directory.

Note: Since different extensions are supported for PowerPoint templates, it is recommended that you include the extension in the name of your template.

n

Is the number of the slide on which to place the report output. This number is optional if the template has only one slide.



Example: Using a PowerPoint PPTX Template

The following request against the GGSALES data source inserts a WebFOCUS report into a PowerPoint PPTX template named mytemplate.potx which is stored in the application directory:

TABLE FILE GGSALES
HEADING
" "
" "
" "
" "
" "
SUM DOLLARS UNITS CATEGORY 
BY  REGION
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PPTX TEMPLATE 'mytemplate.potx' SLIDENUMBER 3
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10,$
TYPE=HEADING, image=gglogo.gif, POSITION=(0.000000 0.000000),$
ENDSTYLE
END

The output is:


Top of page

x
Calculating the Median and Mode of a Field

As of Release 8.0 Version 06, you can use the MDN. (median) and MDE. (mode) prefix operators, in conjunction with an aggregation display command (SUM, WRITE) and a numeric or smart date field, to calculate the statistical median and mode of the values in a field.

These calculations are not supported in a DEFINE command, in WHERE or IF expressions, or in a summary command. If used in a multi-verb request, they must be used at the lowest level of aggregation.

The median is the middle value (50th percentile). If there are an even number of values, the median is the average of the middle two values. The mode is the value that occurs most frequently within the set of values. If no value occurs more frequently than the others, MDE. returns the lowest value.



Example: Calculating the Median and Mode

The following request against the EMPLOYEE data source displays the current salaries and calculates the average (mean), median, and mode within each department.

TABLE FILE EMPLOYEE
SUM CURR_SAL AS 'INDIVIDUAL,SALARIES'
AVE.CURR_SAL WITHIN DEPARTMENT AS 'DEPARTMENT,AVERAGE'  
MDN.CURR_SAL WITHIN DEPARTMENT AS 'DEPARTMENT,MEDIAN'
MDE.CURR_SAL WITHIN DEPARTMENT AS 'DEPARTMENT,MODE'
BY DEPARTMENT
BY CURR_SAL NOPRINT
BY LAST_NAME NOPRINT BY FIRST_NAME NOPRINT
ON TABLE SET PAGE NOPAGE
END

Both departments have an even number of employees. For the MIS department, the two middle values are the same, making that value ($18,480.00) both the median and the mode. For the PRODUCTION department, the median is the average of the two middle values ($16,100.00 and $21,120.00) and, since there are no duplicate values, the mode is the lowest value ($9,500.00).


Top of page

x
Embedded TrueType Font Support Enhancement

As of Release 8.0 Version 05, you can selectively embed a subset of Unicode (Arial Unicode and MS Lucida Sans Unicode) or Non-Unicode (Tahoma, Times New Roman, Trebuchet MS, and Courier New) TrueType fonts into a PDF output file. The generated PDF file will contain only the characters required to render the document.


Top of page

x
Accordion By Row Enhancement

As of Release 8.0 Version 05, multi-verb requests are supported in Accordion By Row reports that contain sort fields on the highest level display command.


Top of page

x
Translation of WebFOCUS Formulas to Excel 2007/2010 Formulas

As of Release 8.0 Version 05, you can use FORMAT XLSX FORMULA to convert summed information (such as column totals, row totals, and subtotals) and calculated values into Excel formulas that will automatically update as you edit the Excel worksheet.


Top of page

x
Defining Column Width and Cell Wrapping in an Excel Worksheet

As of Release 8.0 Version 05, you can now define column width and cell wrapping within an Excel worksheet using SQUEEZE and WRAP.

You can use SQUEEZE to size the worksheet columns. For FORMAT XLSX, SQUEEZE is always set on. This means the worksheet columns will be sized to fit the largest data value within the field. To explicitly change the column width, define SQUEEZE as a number representing the size in the specified UNITS parameter (default is inches). If the value within the cell is larger than the specified width, the data may be hidden from view, but is retained within the cell and can be viewed in the formula bar.

Additionally, as of Reporting Server Release 7.7 Version 05, WRAP is available within worksheets. By default, in all cells containing text values, WRAP is turned on. Cells containing numeric and date values are designated with WRAP turned off. You can set WRAP on within the current column width, or redefine the column width to a size in the specified UNITS (n) and with WRAP ON.


Top of page

x
Compound Layout Header Supports AS Name Syntax

As of WebFOCUS Reporting Server Release 7.7 Version 05 gen 116, the output file name for a Compound Report can be specified as an AS name in the COMPOUND layout block. The syntax is

COMPOUND LAYOUT HOLD AS filename FORMAT PDF

This allows the output file name to be defined by the compound syntax, rather than the first component report. If no AS name is specified, the hold file name is taken from the ON TABLE HOLD phrase in the first component report.


Top of page

x
DB_EXPR Function: Inserting an SQL Expression Into a Request

How to:

Reference:

The DB_EXPR function inserts a native SQL expression exactly as entered into the native SQL generated for a FOCUS or SQL language request.

The DB_EXPR function can be used in a DEFINE command, a DEFINE in a Master File, a WHERE clause, a FILTER FILE command, a filter in a Master File, or in an SQL statement. It can be used in a COMPUTE command if the request is an aggregate request (uses the SUM, WRITE, or ADD command) and has a single display command. The expression must return a single value.



x
Syntax: How to Insert an SQL Expression Into a Request With DB_EXPR
DB_EXPR(native_SQL_expression)

where:

native_SQL_expression

Is a partial native SQL string that is valid for inserting into the SQL generated by the request. The SQL string must have double quotation marks (" ") around each field reference, unless the function is used in a DEFINE with a WITH phrase.



x
Reference: Usage Notes for the DB_EXPR Function


Example: Inserting the DB2 BIGINT and CHAR Functions Into a TABLE Request

The following TABLE request against the WF_RETAIL data source uses the DB_EXPR function in the COMPUTE command to call two DB2 functions. It calls the BIGINT function to convert the squared revenue to a BIGINT data type and then uses the CHAR function to convert that value to alphanumeric:

TABLE FILE WF_RETAIL
SUM REVENUE NOPRINT
AND COMPUTE BIGREV/A31 = DB_EXPR(CHAR(BIGINT("REVENUE" * "REVENUE"))) ;
 AS 'Alpha Square Revenue'
BY REGION
ON TABLE SET PAGE NOPAGE
END

WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.

The trace shows that the expression from the DB_EXPR function was inserted into the DB2 SELECT statement:

SELECT   
  T11."REGION",  
   SUM(T1."Revenue"),  
   ((CHAR(BIGINT( SUM(T1."Revenue") *  SUM(T1."Revenue")) ) )) 
   FROM   
  wrd_fact_sales T1,  
  wrd_dim_customer T5,  
  wrd_dim_geography T11  
   WHERE   
  (T5."ID_CUSTOMER" = T1."ID_CUSTOMER") AND   
  (T11."ID_GEOGRAPHY" = T5."ID_GEOGRAPHY")  
   GROUP BY   
  T11."REGION  "
   ORDER BY   
  T11."REGION  "
   FOR FETCH ONLY; 
END  

The output is:


Top of page

x
Creating a Subquery or Sequential File With HOLD FORMAT SQL_SCRIPT

How to:

When used in a request against a relational data source, the HOLD FORMAT SQL_SCRIPT command generates the SQL SELECT statement needed to execute the current query and stores it in the application folder as a file with a .sql extension along with the Master and Access File pair that describes the SQL answer set.

When used in a request against any other type of data source, the HOLD FORMAT SQL_SCRIPT command executes the current query and stores the retrieved values in the application folder as a sequential file with an .ftm extension along with the Master File that describes the retrieved data.

You can use the output from HOLD FORMAT SQL_SCRIPT as the target file for the DB_INFILE function.

Note: Once you have the .sql file and its accompanying Master File, you can customize the .sql file using global Dialogue Manager variables. You must declare these global variables in the Master File. For information about parameterizing Master Files with global variables, see the Describing Data With WebFOCUS Language manual.



x
Syntax: How to Create an SQL Script or Sequential File Using HOLD FORMAT SQL_SCRIPT
ON TABLE HOLD AS script_name FORMAT SQL_SCRIPT

where:

script_name

Is the name of the .sql file or the .ftm file created as a result of the HOLD FORMAT SQL_SCRIPT command.



Example: Creating an SQL Script File Using HOLD FORMAT SQL_SCRIPT

The following request against the WF_RETAIL relational data source creates an SQL Script file in the baseapp application:

APP HOLD baseapp
TABLE FILE wf_retail
SUM REGION STATECODE
BY REGION NOPRINT BY STATECODE NOPRINT
WHERE REGION EQ 'Central' OR 'NorthEast'
WHERE STATECODE EQ 'AR' OR 'IA' OR 'KS' OR 'KY' OR 'WY' OR 'CT' OR 'MA' OR 'NJ' OR 'NY' OR 'RI'
ON TABLE HOLD AS RETAIL_SCRIPT FORMAT SQL_SCRIPT
END

WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.

The result of this request is a script file named retail_script.sql and a corresponding Master and Access File.

The retail_script.sql file contains the following SQL SELECT statement:

SELECT   MAX(T11."REGION") AS E01,  MAX(T11."STATECODE") AS E02  FROM  wrd_dim_geography T11  WHERE  (T11."STATECODE" IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', 'NJ', 'NY', 'RI')) AND  (T11."REGION" IN('Central', 'NorthEast'))  GROUP BY  T11."REGION", T11."STATECODE"

The retail_script.mas Master File follows:

FILENAME=RETAIL_SCRIPT, SUFFIX=DB2     , $
  SEGMENT=RETAIL_SCRIPT, SEGTYPE=S0, $
    FIELDNAME=REGION, ALIAS=E01, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON, $
    FIELDNAME=STATECODE, ALIAS=E02, USAGE=A2, ACTUAL=A2,
      MISSING=ON, $

The retail_script.acx Access File follows:

 SEGNAME=RETAIL_SCRIPT, CONNECTION=CON1, DATASET=RETAIL_SCRIPT.SQL, $


Example: Creating a Sequential File Using HOLD FORMAT SQL_SCRIPT

The following request against the EMPLOYEE data source creates a sequential file containing the values retrieved by the request along with a corresponding Master File:

APP HOLD baseapp 
TABLE FILE EMPLOYEE 
PRINT LAST_NAME FIRST_NAME 
WHERE DEPARTMENT EQ 'MIS' 
ON TABLE HOLD AS EMPVALUES FORMAT SQL_SCRIPT 
END

The sequential file empvalues.ftm contains the following data:

SMITH           MARY        JONES           DIANE       MCCOY           JOHN        BLACKWOOD       ROSEMARIE   GREENSPAN       MARY        CROSS           BARBARA     

The empvalues.mas Master File follows:

FILENAME=EMPVALUES, SUFFIX=FIX     , IOTYPE=BINARY, $ 
  SEGMENT=EMPVALUE, SEGTYPE=S0, $ 
    FIELDNAME=LAST_NAME, ALIAS=E01, USAGE=A15, ACTUAL=A16, $ 
    FIELDNAME=FIRST_NAME, ALIAS=E02, USAGE=A10, ACTUAL=A12, $

Top of page

x
DB_INFILE Function: Testing Values Against a File or an SQL Subquery

In this section:

How to:

Reference:

The DB_INFILE function compares one or more field values in a source file to values in a target file. It returns the value 1 (TRUE) if the set of source fields matches a set of values from the target file. Otherwise, the function returns zero (FALSE). DB_INFILE can be used anywhere a function can be used in a WebFOCUS request, such as in a DEFINE or a WHERE phrase.

The target file can be any data source that WebFOCUS can read. Depending on the data sources accessed and the components in the request, either WebFOCUS or an RDBMS will process the comparison of values.

If WebFOCUS processes the comparison, it dynamically reads the target data source and creates a sequential file containing the target data values along with a synonym describing the data file. It then builds IF or WHERE structures in memory with all combinations of source and target values. If the target data contains characters that WebFOCUS considers wildcard characters, it will treat them as wildcard characters unless the command SET EQTEST = EXACT is in effect.

The following situations exist when a relational data source is the source file:



x
Syntax: How to Compare Source and Target Field Values With DB_INFILE
DB_INFILE(target_file, s1, t1, ... sn, tn)

where:

target_file

Is the synonym for the target file.

s1, ..., sn

Are fields from the source file.

t1, ..., tn

Are fields from the target file.

The function returns the value 1 if a set of target values matches the set of source values. Otherwise, the function returns zero (0).

The mapping is done using pairs, where the s1 field is matched to t1 field, and so on.



x
Reference: Usage Notes for DB_INFILE


Example: Comparing Source and Target Values Using an SQL Subquery File

This example uses the WF_RETAIL DB2 data source.

WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.

The SQL file named retail_subquery.sql contains the following subquery that retrieves specified state codes in the Central and NorthEast regions:

SELECT  MAX(T11.REGION), MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', 'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))  GROUP BY T11.REGION, T11.STATECODE

The retail_subquery.mas Master File follows:

FILENAME=RETAIL_SUBQUERY, SUFFIX=DB2     , $
  SEGMENT=RETAIL_SUBQUERY, SEGTYPE=S0, $
    FIELDNAME=REGION, ALIAS=E01, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON, $
    FIELDNAME=STATECODE, ALIAS=E02, USAGE=A2, ACTUAL=A2,
      MISSING=ON, $

The retail_subquery.acx Access File follows:

SEGNAME=RETAIL_SUBQUERY,CONNECTION=CON1,DATASET=RETAIL_SUBQUERY.SQL, $

Note: You can create an SQL subquery file along with a corresponding synonym using the HOLD FORMAT SQL_SCRIPT command.

The following request uses the DB_INFILE function to compare region names and state codes against the names retrieved by the subquery:

TABLE FILE WF_RETAIL
SUM REVENUE
BY REGION
BY STATECODE
WHERE DB_INFILE(RETAIL_SUBQUERY, REGION, REGION, STATECODE, STATECODE)
ON TABLE SET PAGE NOPAGE
END

The trace shows that the subquery was inserted into the WHERE predicate in the generated SQL:

 SELECT   
  T11."REGION",  
  T11."STATECODE",  
   SUM(T1."Revenue")  
   FROM   
  wrd_fact_sales T1,  
  wrd_dim_customer T5,  
  wrd_dim_geography T11  
   WHERE   
  (T5."ID_CUSTOMER" = T1."ID_CUSTOMER") AND   
  (T11."ID_GEOGRAPHY" = T5."ID_GEOGRAPHY") AND  
  ((T11."REGION", T11."STATECODE") IN (SELECT  MAX(T11.REGION),  
  MAX(T11.STATECODE)  FROM wrd_dim_geography T11 WHERE  
  (T11.STATECODE IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA',  
  'NJ', 'NY', 'RI')) AND (T11.REGION IN('Central', 'NorthEast'))   
  GROUP BY T11.REGION, T11.STATECODE))  
   GROUP BY   
  T11."REGION",  
  T11."STATECODE  "
   ORDER BY   
  T11."REGION",  
  T11."STATECODE  "
   FOR FETCH ONLY; 
END  

The output is:



Example: Comparing Source and Target Values Using a Sequential File

The empvalues.ftm sequential file contains the last and first names of employees in the MIS department:

SMITH           MARY        JONES           DIANE       MCCOY           JOHN        BLACKWOOD       ROSEMARIE   GREENSPAN       MARY        CROSS           BARBARA     

The empvalues.mas Master File describes the data in the empvalues.ftm file:

FILENAME=EMPVALUES, SUFFIX=FIX     , IOTYPE=BINARY, $
  SEGMENT=EMPVALUE, SEGTYPE=S0, $
    FIELDNAME=LN, ALIAS=E01, USAGE=A15, ACTUAL=A16, $
    FIELDNAME=FN, ALIAS=E02, USAGE=A10, ACTUAL=A12, $

Note: You can create a sequential file along with a corresponding synonym using the HOLD FORMAT SQL_SCRIPT command.

The following request against the FOCUS EMPLOYEE data source uses the DB_INFILE function to compare employee names against the names stored in the empvalues.ftm file:

FILEDEF EMPVALUES DISK baseapp/empvalues.ftm
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY LAST_NAME BY FIRST_NAME
WHERE DB_INFILE(EMPVALUES, LAST_NAME, LN, FIRST_NAME, FN)
ON TABLE SET PAGE NOPAGE
END

The output is:



x
Controlling DB_INFILE Optimization Against a Relational DBMS

When used against a single RDBMS, DB_INFILE performs an analysis to determine whether the resulting expression can be optimized using a subquery. It then creates that subquery and passes it to the relational adapter for inclusion in the optimized SQL to be passed to the RDBMS for processing. Occasionally, because of specific syntax not supported by the RDBMS involved, the RDBMS is not able to process the subquery that was created by DB_INFILE. If this happens, you will see messages to that effect. In this case, you can prevent DB_INFILE from optimizing the expression as a subquery by setting the DB_INFILE parameter to EXPAND_ALWAYS. You should implement this setting only for those specific requests that demonstrate problems with the subquery generated by DB_INFILE.

You can also prevent DB_INFILE from expanding the query into IF and WHERE phrases by setting the DB_INFILE parameter to EXPAND_NEVER. One reason to use this value is if the target file is so large that reading it could freeze processing. In this case, DB_INFILE will attempt to create a subquery. If this is not possible, the following message will be issued and processing will halt:

(FOC32585) ERROR EXPANDING DB_INFILE


x
Syntax: How to Control DB_INFILE Optimization

To control whether to prevent optimization of the DB_INFILE expression, issue the following command:

SET DB_INFILE = {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

In a TABLE request, issue the following command:

ON TABLE SET DB_INFILE  {DEFAULT|EXPAND_ALWAYS|EXPAND_NEVER}

where:

DEFAULT

Enables DB_INFILE to create a subquery if its analysis determines that it is possible. This is the default value.

EXPAND_ALWAYS

Prevents DB_INFILE from creating a subquery and, instead, expands the expression into IF and WHERE clauses in memory.

EXPAND_NEVER

Prevents DB_INFILE from expanding the expression into IF and WHERE clauses in memory and, instead, attempts to create a subquery. If this is not possible, a FOC32585 message is generated and processing halts.


Top of page

x
Styling Tags for SUBTOTAL and GRANDTOTAL Lines

How to:

The tag is the text that is displayed in the leftmost portion of each SUBTOTAL and GRANDTOTAL row in a report. The tag is used to identify the type of data represented within this row. The text used to generate this tag can be customized by adding an AS name to the SUBTOTAL syntax.

You can define styling for the subtotal and grand total tag separately from the rest of the row. Text attributes available for the tag, including font, color, size, and style, can be used to differentiate and highlight the tags. Additionally, styling can be applied that turns tags into drill-down links.

Styling is supported for text attributes only. Cell or column features such as borders, background color, or justification are not supported.

This feature is available for PDF, DHTML, PS, HTML, AHTML, XLSX, and EXL2K formats.



x
Syntax: How to Style Subtotal and Grand Total Tags
TYPE={SUBTOTAL|GRANDTOTAL}, OBJECT=TAG,
   [FONT=font], [SIZE=size], [STYLE=style],
   [COLOR={color|RGB({r g b|#hexcolor})],
   [drilltype=drillparms], $

where:

font

Is the name of the font.

size

Is the point size of the font.

style

Is the font style, for example, bold, italic, or bold+italic.

color

Is a color name.

r g b

Specifies the font color using a mixture of red, green, and blue.

(r g b) is the desired intensity of red, green, and blue, respectively. The values are on a scale of 0 to 255, where 0 is the least intense and 255 is the most intense. Note that using the three color components in equal intensity results in shades of gray.

#hexcolor

Is the hexadecimal value for the color. For example, FF0000 is the hexadecimal value for red. The hexadecimal digits can be in uppercase or lowercase and must be preceded by a pound sign (#).

drilltype

Is any valid drill-down attribute, for example, URL= or FOCEXEC=. For information about drill-down links, see the Creating Reports With WebFOCUS Language manual.

drillparms

Are valid attribute values for the type of drill down.



Example: Styling SUBTOTAL and GRANDTOTAL Tags

The following request against the GGSALES data source generates subtotal and grand total rows. The tags for the subtotal rows are in italics and are white. The tag for the grand total row has a drill-down link to a URL:

TABLE FILE GGSALES  
SUM UNITS/D8C DOLLARS/D12CM BUDUNIT/D8C BUDDOLLARS/D12CM
BY REGION
BY CATEGORY
ON REGION SUBTOTAL
HEADING
"Gotham Grinds Sales Report"
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *  
INCLUDE=ENDEFLT,$ 
TYPE=SUBTOTAL, OBJECT=TAG,STYLE=ITALIC,COLOR=WHITE,$
TYPE=GRANDTOTAL, BACKCOLOR='LIGHT GREY',$
TYPE=GRANDTOTAL, OBJECT=TAG,URL='http://www.informationbuilders.com',$
ENDSTYLE   
END

The output is:


Top of page

x
Table of Contents for Coordinated Compound and Burst Reports

PDF coordinated compound reports defining the Table of Contents page or Bookmarks based on BY field entries can be coordinated (MERGE=ON) to generate a report segmented by the primary sort key. Additionally, these coordinated reports can be burst into separate documents by the primary sort key and distributed using ReportCaster.


Top of page

x
Support for Images Stored in BLOB Fields in HTML and DHTML Reports

How to:

Reference:

The following relational data sources support the Binary Large Object (BLOB) data type: Microsoft SQL Server, DB2, Oracle, Informix, and PostgreSQL, using its BYTEA data type.

WebFOCUS StyleSheets used to produce report output in HTML or DHTML format can access a BLOB field as an image source when an instance of the BLOB field contains an exact binary copy of a GIF, JPEG, or PNG image. Images of different formats (GIF, JPEG, and PNG) can be mixed within the same BLOB field. WebFOCUS can determine the format from the header of the image. The image can be inserted in report columns, headings, footings, subheadings, and subfootings.

The BLOB field must be referenced in a PRINT or LIST command in the request (aggregation is not supported). Reports containing BLOB images are supported as components in Coordinated Compound Reports.

With the following SET commands, BLOB images will work for both HTML and DHTML in all browsers:



x
Syntax: How to Insert a BLOB Image in HTML or DHTML Report Output

For a heading, footing, subhead, or subfoot:

TYPE=headtype, [BY=byfield,] IMAGE=(blobfield), POSITION=(+xpos +ypos), SIZE=(width height) [, PRESERVERATIO={ON|OFF}],$

For a report column:

TYPE=DATA, COLUMN=bloboutputfield, IMAGE=(blobfield), SIZE=(width height) [, PRESERVERATIO={ON|OFF}] ,$

where:

headtype

Is HEADING, FOOTING, SUBHEAD, or SUBFOOT.

byfield

Is the sort field that generated the subhead or subfoot.

blobfield

Is any valid column reference for the BLOB field that contains the image. Note that the BLOB field must be referenced in a PRINT or LIST command in the request.

If the BLOB field is embedded in a heading, subheading, footing, or subfooting rather than a column, the StyleSheet declaration is responsible for placing the image in the heading, subheading, footing, or subfooting. To make the BLOB image accessible to the StyleSheet, the BLOB field must be referenced in the PRINT or LIST command with the NOPRINT option. Do not reference the BLOB field name in the heading, subheading, footing, or subfooting itself.

xpos ypos

Are the offsets from the top-left corner of the heading, subheading, footing, or subfooting component in the units specified by the UNITS parameter (default is inches). The default is no offset. These numbers are specified with a leading plus (+) sign to indicate that they are relative to the top-left corner of the report component, as opposed to absolute page coordinates.

width height

Specify the dimensions to which the image is to be scaled in the units specified by the UNITS parameter (default is inches).

For a heading, subheading, footing, or subfooting component: If omitted, the original dimensions of the image are used (any GIF, PNG, or JPEG image has an original, unscaled size based on the dimensions of its bitmap). You must allow an adequate offset (POSITION) for the image to begin past the text of the heading, subheading, footing, or subfooting (if any) and also must allow for adequate space to prevent the image from overlapping the lines following the component. Reserve the space for them with blank lines. This space is accounted for when computing the heading or footing height.

There are no special considerations for FOOTING BOTTOM. The space required by the footing is computed in advance based on the height of the footing, and should not be affected by BLOB images in the columns, subheads, or other components.

For a report column: If omitted, the default size is 1 inch by 1 inch. The width of the column and the spacing between the lines is automatically adjusted to accommodate the image.

bloboutputfield

Designates the column to be replaced with an image (usually this will be the image field itself). Multiple report columns can contain images.

PRESERVERATIO={ON|OFF}

Supported for GIF and JPEG images only. PRESERVERATIO=ON specifies that the aspect ratio (ratio of height to width) of the image should be preserved when it is scaled to the specified SIZE. This avoids distorting the appearance of the image. The image is scaled to the largest size possible within the bounds specified by SIZE for which the aspect ratio can be maintained. OFF does not maintain the aspect ratio. OFF is the default value.



x
Reference: File Size and Compression Considerations

Files that contain many images can be large. Scaling the images to a smaller size using the SIZE attribute does not decrease the size of the file.



Example: Inserting an Image From a BLOB Field Into a Report Column

The Microsoft SQL Server data source named retaildetail contains product information for a sports clothing and shoe retailer. The Microsoft SQL Server data source named retailimage has the same product ID field as retaildetail and has an image of each product stored in a field named prodimage whose data type is BLOB.

The following request joins the two data sources and prints product names and prices with the corresponding image. The output is generated in DHTML format.

-* Rel 7705 DHTML and HTML supports including Image stored in 
-* BLOB field in report column, heading, footing, subhead, or 
-* subfoot
-* Rel 769 supports PDF format
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product List"
" "
PRINT NAME/A20 PRICE PRODIMAGE AS 'PICTURE'
BY PRODUCTID NOPRINT
BY NAME NOPRINT
ON NAME UNDER-LINE
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT DHTML
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,FONT=ARIAL, GRID=OFF,$
TYPE=HEADING, SIZE = 18, COLOR=RED,$
TYPE=DATA,COLUMN=PRODIMAGE,IMAGE=(PRODIMAGE),SIZE=(1 1),$
ENDSTYLE
END

The partial output shows that DHTML format preserves the specified spacing.

The following request generates the output in HTML format.

-* Rel 7705 DHTML and HTML supports including Image stored in 
-* BLOB field in report column, heading, footing, subhead, or
-* subfoot
-* Rel 769 supports PDF format
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product List"
" "
PRINT NAME/A20 PRICE PRODIMAGE AS 'PICTURE'
BY PRODUCTID NOPRINT
BY NAME NOPRINT
ON NAME UNDER-LINE
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE, GRID=OFF, FONT=ARIAL,$
TYPE=HEADING, SIZE = 18, COLOR=RED,$
TYPE=DATA,COLUMN=PRODIMAGE,IMAGE=(PRODIMAGE),SIZE=(1 1),$
ENDSTYLE
END

The partial output shows that the spacing is different because the browser removes blank spaces for HTML report output.

The following request generates the report output in PDF format.

-* Rel 7705 DHTML and HTML supports including Image stored in 
-* BLOB field in report column, heading, footing, subhead, or 
-* subfoot
-* Rel 769 supports PDF format
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product List"
" "
PRINT NAME/A20 PRICE PRODIMAGE AS 'PICTURE'
BY PRODUCTID NOPRINT
BY NAME NOPRINT
ON NAME UNDER-LINE
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE, GRID=OFF,$
TYPE=HEADING, SIZE = 18, FONT = ARIAL, COLOR=RED,$
TYPE=DATA,COLUMN=PRODIMAGE,IMAGE=(PRODIMAGE),SIZE=(1 1),$
ENDSTYLE
END

The PDF partial output preserves specified spacing providing results similar to DHTML output.



Example: Inserting an Image From a BLOB Field Into a Subheading

The Microsoft SQL Server data source named retaildetail contains product information for a sports clothing and shoe retailer. The Microsoft SQL Server data source named retailimage has the same product ID field as retaildetail and has an image of each product stored in a field named prodimage whose data type is BLOB.

The following request joins the two data sources and prints product images in a subheading. The output is generated in DHTML format. It can also be generated in HTML or PDF format:

-* Rel 7705 DHTML and HTML supports including Image stored in
-* BLOB field in HTML report column, heading, footing, subhead,
-* or subfoot
-* Rel 769 supports PDF format
-*SET BASEURL='' - Required for embedded images to work. Overrides default
-*setting from WF Client.
SET BASEURL='' 
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product Catalog"
" "
PRINT NAME NOPRINT PRODIMAGE NOPRINT
BY PRODUCTID NOPRINT
ON PRODUCTID SUBHEAD
""
" ID: <10<PRODUCTID "
" Name: <10<NAME "
" Price: <7<PRICE "
" Image: "
""
""
""
""
""
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT DHTML
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,FONT = ARIAL,$
TYPE=HEADING, COLOR = RED, SIZE = 16, JUSTIFY=CENTER,$
TYPE=SUBHEAD,BY=PRODUCTID,IMAGE=(PRODIMAGE),SIZE=(1 1), POSITION=(+2 +1),$
END

The partial output is:



Example: Sizing an Image From a BLOB Field

The Microsoft SQL Server data source named retaildetail contains product information for a sports clothing and shoe retailer. The Microsoft SQL Server data source named retailimage has the same product ID field as retaildetail and has an image of each product stored in a field named prodimage whose data type is BLOB.

The following request joins the two data sources and displays the same image on three columns of output using different sizes and different PRESERVERATIO settings. Note that PRESERVERATIO=ON is not supported with images in PNG format.

The output is generated in DHTML format. It can also be generated in HTML or PDF format.

-* Rel 7705 DHTML and HTML supports including Image stored in
-* BLOB field in report column, heading, footing, subhead, or
-* subfoot
-* Rel 769 supports PDF format
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
PRINT PRODIMAGE AS '' PRODIMAGE AS '' PRODIMAGE AS ''
BY STYLE NOPRINT
WHERE NAME CONTAINS 'Pant' OR 'Tank'
ON STYLE UNDER-LINE
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT DHTML
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,FONT = ARIAL,$
TYPE=DATA,COLUMN=P1,IMAGE=(PRODIMAGE),SIZE=(.75 .75),$
TYPE=DATA,COLUMN=P2,IMAGE=(PRODIMAGE),SIZE=(.75 1),PRESERVERATIO=ON,$
TYPE=DATA,COLUMN=P3,IMAGE=(PRODIMAGE),SIZE=(.75 1),PRESERVERATIO=OFF,$
END

Note that PRESERVERATIO=OFF is specified for the second column to preserve the image height and width ratio for that column even though the styling SIZE height specifies a different value than the first column image styling. In addition, PRESERVERATIO=OFF is specified for the third column, so for that column the image height to width ratio is not preserved and is rendered as specified by the styling SIZE height and width values specified in the request (FEX).

The partial output follows:



Example: Inserting an Image From a BLOB Field in a Summary Report

The Microsoft SQL Server data source named retaildetail contains product information for a sports clothing and shoe retailer. The Microsoft SQL Server data source named retailimage has the same product ID field as retaildetail and has an image of each product stored in a field named prodimage whose data type is BLOB.

The following request joins the two data sources. It contains two display commands, a SUM command and a PRINT command. The SUM command aggregates the total price for each category and displays this category name and total price in a subheading, The PRINT command displays the image for each item in the category along with its individual product number and price in a subfooting.

The output is generated in DHTML format. It can also be generated in HTML or PDF format.

-* Rel 7705 DHTML and HTML supports including images stored in 
-* BLOB field in report column, heading, footing, subhead, or
-* subfoot
-* Rel 769 supports PDF format
SET PRINTPLUS=ON
JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product Price Summary"
" "
SUM PRICE NOPRINT
BY CATEGORY NOPRINT
ON CATEGORY SUBHEAD 
" Category: <CATEGORY "
" Total Price: <PRICE "
" "
PRINT PRICE NOPRINT PRODIMAGE NOPRINT
BY CATEGORY NOPRINT
BY PRODUCTID NOPRINT
ON PRODUCTID SUBFOOT
" " 
" "
" "
" " 
" "
" "
" Product #: <PRODUCTID "
" Name: <NAME "
" Price: <FST.PRICE "
ON TABLE SET PAGE NOPAGE
-**************************
-* Lines between asterisk lines required for BLOB image support 
-* for HTML and DHTML formats.
ON TABLE SET HTMLEMBEDIMG AUTO
-* Required to support IE8 with images larger than 32K
ON TABLE SET HTMLARCHIVE ON
-*Required for image positioning in subheads in HTML reports
ON TABLE SET HTMLCSS ON 
-**************************
ON TABLE PCHOLD FORMAT DHTML
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,FONT=ARIAL,$
TYPE=HEADING, COLOR=RED, SIZE=14, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=SUBHEAD, COLOR=RED, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=SUBFOOT,BY=PRODUCTID,IMAGE=(PRODIMAGE),SIZE=(1 1), POSITION=(0 0),$
TYPE=SUBFOOT,BY=PRODUCTID,OBJECT=FIELD, ITEM=1, WRAP=5,$
END

The output for the first category is:


Top of page

x
Support for Column Totals in Accordion by Row Reports

Accordion by Row reports (ON TABLE SET EXPANDBYROW ON) display the grand total row as an anchor row below the data. This anchor row displays above the report and page footings aligned to the left margin of the report.

To generate Accordion by Row reports without the grand total anchor row, add ON TABLE NOTOTAL to the request.



Example: Controlling Grand Total Generation In an Accordion by Row Report

The following Accordion by Row report against the GGSALES data source generates a grand total row at the bottom. This is the default behavior:

TABLE FILE GGSALES
SUM DOLLARS/D8MC
UNITS/D8C
BY REGION
BY CATEGORY
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET EXPANDBYROW ON
ON TABLE SET DROPBLNKLINE ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
INCLUDE= ENDEFLT.STY,$
ENDSTYLE
END

The output is:

The following version of the same request includes the ON TABLE NOTOTAL command:

TABLE FILE GGSALES
SUM DOLLARS/D8MC
UNITS/D8C
BY REGION
BY CATEGORY
BY PRODUCT
ON TABLE SET HTMLCSS ON
ON TABLE SET EXPANDBYROW ON
ON TABLE SET DROPBLNKLINE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
INCLUDE= ENDEFLT.STY,$
ENDSTYLE
END

The output no longer has the grand total row:


Top of page

x
Displaying  an  Accordion  By  Row  Report  Using  HOLD  FORMAT  HTMTABLE  and  -HTMLFORM

How to:

Starting with the Release 7.7 Version 05 or Release 8.0 Version 02 Reporting Server and the Release 8.0 Version 02 WebFOCUS Client, the HOLD FORMAT HTMTABLE command in conjunction with -HTMLFORM functionality supports the display of Accordion By Row (SET EXPANDBYROW) reports to create an HTML page. Accordion by Column reports (SET EXPANDABLE) are not supported with -HTMLFORM.

Accordion By Row reports present sort field values and their corresponding aggregated measures rolled up so that the highest level sort field and the grand totals are at the top of the report. A tree control can be used to open each dimension and view the associated aggregated values. This type of Accordion Report is generated using the SET EXPANDBYROW command. For more information about Accordion Reports, see the Creating Reports With WebFOCUS Language manual.



x
Syntax: How to Display an Accordion Report Using HOLD FORMAT HTMLTABLE and -HTMLFORM

You can display one or more Accordion By Row reports on a custom HTML page by creating the report with the SET EXPANDBYROW command, specifying HOLD FORMAT HTMTABLE, and then using the Dialogue Manager command -HTMLFORM.

Include the following commands in the procedure:

ON TABLE SET EXPANDBYROW ON
ON TABLE HOLD FORMAT HTMLTABLE AS report

where:

report

Is the 1-character to 8-character name of a virtual file that contains the report output.

Running this report creates an output file that contains only the report data. In order to display the output as an HTML Accordion By Row report, the following JavaScript code must be included in the HTML by using the following syntax and placement:

IBI.OBJ.EXPBYROWJS

Is the JavaScript for HTML Accordion report functionality. Must be coded in the -HTMLFORM after the <HTML tag> and before the <BODY> tag.

<div id="IBI_popupHere"></div>

Is required prior to display of the first Accordion report (using !IBI.FIL.filename) for display of pop-up column title description.

!IBI.OBJ.IBIGBLONLOAD

Is the JavaScript that requests the load of the global environment setup for WebFOCUS HTML reports. Must be coded within the <BODY> tag before the comment line (!IBI.FIL.report) that indicates where to display the first HTML report as:

 <BODY ONLOAD='!IBI.OBJ.IBIGBLONLOAD';>
!IBI.OBJ.IBIGBLJS

Is the JavaScript for global environment setup for WebFOCUS HTML reports. Must be coded after the comment line (!IBI.FIL.report) that indicates where to display the last HTML report, and before the closing </HTML> tag.



Example: Displaying Two HTML Accordion Reports on an HTML Web Page created by –HTMLFORM.

The following request contains two Accordion By Row reports, RPT1 and RPT2, saved as format HTMTABLE. The -HTMLFORM block contains all of the JavaScript code to display them on an HTML page.

TABLE FILE GGSALES
HEADING
"Regional Budget and Sales Report"
" "
SUM BUDUNITS UNITS BUDDOLLARS DOLLARS
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE HOLD AS RPT1 FORMAT HTMTABLE 
ON TABLE SET EXPANDBYROW ON 
ON TABLE SET HTMLCSS ON
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=NAVY,FONT='ARIAL',SIZE=9,GRID=OFF,$
TYPE=HEADING,LINE=1,STYLE=BOLD,SIZE=12,JUSTIFY=CENTER,$
TYPE=TITLE,BACKCOLOR=RGB(45 111 205),COLOR=WHITE,STYLE=-UNDERLINE+BOLD, $
TYPE=DATA,BACKCOLOR=(WHITE RGB(235 235 255)),$
TYPE=SUBTOTAL,BACKCOLOR=RGB(163 200 236),STYLE=BOLD,$
END
TABLE FILE GGSALES
HEADING
"Product Category Sales Report"
" "
SUM GGSALES.SALES01.DOLLARS
GGSALES.SALES01.BUDDOLLARS
BY GGSALES.SALES01.CATEGORY
BY GGSALES.SALES01.REGION
BY GGSALES.SALES01.ST
ON TABLE HOLD AS RPT2 FORMAT HTMTABLE 
ON TABLE SET EXPANDBYROW ON 
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET DROPBLNKLINE ALL
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=NAVY,SQUEEZE=ON,FONT='ARIAL',SIZE=9,GRID=OFF,$
TYPE=HEADING,LINe=1,STYLE=BOLD,SIZE=12,JUSTIFY=CENTER,$
TYPE=TITLE,BACKCOLOR=RGB(45 111 205),COLOR=WHITE,STYLE=-UNDERLINE+BOLD, $
TYPE=DATA,BACKCOLOR=(WHITE RGB(235 235 255)),$
TYPE=SUBTOTAL,BACKCOLOR=RGB(163 200 236),STYLE=BOLD,$
END
-HTMLFORM BEGIN
<HTML>!IBI.OBJ.EXPBYROWJS; 
<BODY ONLOAD='!IBI.OBJ.IBIGBLONLOAD;'> 
<font face="arial" color="blue"><b>This HTML page is created with -HTMLFORM and displays <br> two Accordion By Row (SET EXPANDBYROW) Reports</b>
<br></br>
</font> 
<div id="IBI_popupHere"></div> 
!IBI.FIL.RPT1;  
<br></br>  
!IBI.FIL.RPT2;
!IBI.OBJ.IBIGBLJS; 
</BODY>
</HTML>
-HTMLFORM END

The output is:


Top of page

x
Displaying Superscripts On Data, Heading, and Footing Lines

How to:

Superscript characters are supported as a text style in text objects using HTML markup tags. The superscript markup tag is now supported in data columns, headings, and footings in HTML, PDF, and PS output formats. Superscript values can be defined within the data, added to virtual fields, or added to text strings displayed in headings and footings.

In order to activate the translation of the HTML markup tags, in the StyleSheet set MARKUP=ON for any report component that will display superscripts. Without this attribute, the markup tags will be treated as text, not tags.



x
Syntax: How to Display Superscripts on Report Data, Heading, and Footing Lines

If the tags are not within the data itself, create a field that contains the text to be used as a superscript. Also, turn markup tags on for the components that will display superscripts:



Example: Displaying Superscripts in Data and Footing Lines in PDF Output

The following request against the GGSALES data source defines two fields that will display as superscripts. SUP1 and SUP2 consist of the numbers 1 and 2, respectively. SUPCOPY consists of a copyright symbol. Note that the difference is the syntax defined for a text value as opposed to a HEX value.

The COMPUTE command compares sales dollars to budgeted dollars. If the value calculated is less than a minimum defined, the superscript SUP1 is concatenated after the category name. If the value is greater, SUP2 is concatenated.

The superscript SUPCOPY is used to display the copyright symbol in the footing of the report.

The footing concatenates the superscript fields in front of their explanations.

In the StyleSheet, every component that will display a superscript has the attribute MARKUP=ON.

DEFINE FILE GGSALES
SUP1/A12= '<SUP>1</SUP>';
SUP2/A15= '<SUP>2</SUP>';
SUPCOPY/A20= '<SUP>'||HEXBYT(169,'A2')||'</SUP>';
END
TABLE FILE GGSALES
SUM
COMPUTE PROFIT/D12CM=DOLLARS-BUDDOLLARS; NOPRINT
COMPUTE SHOWCAT/A100=IF PROFIT LE -50000 THEN CATEGORY || SUP1
       ELSE IF PROFIT GT 50000 THEN CATEGORY || SUP2
           ELSE CATEGORY; AS Category
BUDDOLLARS/D12CM
DOLLARS/D12CM
BY REGION 
BY CATEGORY NOPRINT
HEADING
"Analysis of Budgeted and Actual Sales"
FOOTING
""
"<SUP1 Dollar sales $50,000 less than budgeted amount."
"<SUP2 Dollar sales $50,000 greater than budgeted amount."
""
"Copyright<SUPCOPY 2012, by Information Builders, Inc " 
ON TABLE SET HTMLCSS ON
ON TABLE SET SQUEEZE ON
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,$
TYPE=DATA,MARKUP=ON,$
TYPE=DATA,COLUMN=N5, COLOR=RED, WHEN=PROFIT LT -50000,$
TYPE=DATA,COLUMN=N6, COLOR=GREEN, WHEN=PROFIT GT 50000,$
TYPE=HEADING, JUSTIFY=LEFT,$
TYPE=FOOTING, MARKUP=ON, JUSTIFY=LEFT,$
TYPE=FOOTING, LINE=2,JUSTIFY=LEFT, COLOR=RED,$
TYPE=FOOTING, LINE=3,JUSTIFY=LEFT, COLOR=GREEN,$
END

The output is:


Top of page

x
Support for Hyperlinks in Text Markups in PDF Report Output

How to:

Hyperlinks can be included in text objects to point navigation to other WebFOCUS procedures, web pages, or web resources, such as an email application.



x
Syntax: How to Insert Hyperlinks in a Text Object With Markup Tags
<a href="hyperlink">Text to display</a>

where:

hyperlink

Is the hyperlink to jump to when the text is clicked.

Text to display

Is the text to display for the hyperlink.

For example:

<a href="http://www.example.com/help.htm">Click here for help</a>

The <a> tag defines a hyperlink, which provides a link from one page to another. The href attribute specifies the URL of the page destination.

The only attribute of the <a> element supported in an anchor markup tag is href.



Example: Inserting Hyperlinks in a Text Object With Markup Tags

The following request against the EMPLOYEE data source defines a text object with hyperlinks to pages on the Information Builders website:

TABLE FILE EMPLOYEE
BY EMP_ID NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
type=report, size=8, $
object=string, position=(1 1), dimension=(7 3), wrap=on, markup=on,
linespacing=multiple(2),
text='<b><font face="Arial" size=11><full>Gerry D. Cohen is president and CEO of <a href="http://www.ibi.com/"><i>Information Builders,</a></i> a leader in <a href="http://www.informationbuilders.com/business-intelligence/">business intelligence</a>  and <a href="http://www.informationbuilders.com/products/business-analytics/">analytics</a>, <a href="http://www.informationbuilders.com/products/integrity/">information integrity,</a> and <a href="http://www.informationbuilders.com/products/integration/">integration </a>solutions.  He co-founded Information Builders in 1975 with the mission to develop a software product that would allow non-programmers to create their own information systems. Building on this foundation for the past 37 years, Information Builders solutions have been used to construct BI systems for thousands of leading companies, universities, and government agencies around the world. ',$
ENDSTYLE
END

On the output, the word integration is highlighted as the cursor hovers over the hyperlink and the associated URL is displayed at the bottom of the page:


Top of page

x
Support for Vertical Alignment of Text Markup in PDF Report Output

As of Release 7.7 Version 05/Release 8.0 Version 01, text can be vertically aligned within Text Objects in PDF format. This feature supports top, middle, and bottom alignment options to explicitly place text within the defined position and dimensions. The vertical alignment tags can be used in conjunction with the horizontal alignment tags (left, right, full) to control the placement of any text object.

Syntax:

Top Alignment:

<top>text</top>

Middle Alignment:

<mid>text</mid>

Bottom Alignment:

<bottom>text</bottom>


Example: Vertically Aligning Text Markup in PDF Report Output

The following request creates three boxes and places a text string object within each of the boxes:

SET PAGE-NUM=OFF
TABLE FILE GGSALES
BY REGION NOPRINT
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
ttype=report, font=arial, size=10, $
object=box, position=(1 1), dimension=(6 1), $
object=line, position=(3 1), endpoint=(3 2), $
object=line, position=(5 1), endpoint=(5 2), $ 
object=string, text='<top>Vertically aligned text within a text object using top alignment.</top>', position=(1.05 1), dimension=(2 1), linespacing=exact(.15), markup=on, wrap=on, $
object=string, text='<mid>Vertically aligned text within a text object using middle alignment.</mid>', position=(3.05 1), dimension=(2 1), linespacing=exact(.15), markup=on, wrap=on, $
object=string, text='<bottom>Vertically aligned text within a text object using bottom alignment.</bottom>', position=(5.05 .9), dimension=(2 1),linespacing=exact(.15), markup=on, wrap=on, $ 
END

The output is:


Top of page

x
Controlling Display of an ACROSS Title for a Single Field

How to:

By default, when there is only one field displayed for an ACROSS sort group, no column title displays above that field. Using the SET ACRSVRBTITL command, you can control the display of ACROSS column titles when there is one displayed field for an ACROSS group. The field count that determines whether the ACROSS title displays is affected by certain components in the report request, such as calculated fields or fields in headings.

The output is unaffected by SET commands that adjust underlines with titles, adjust the space between columns, or eliminate unpopulated ACROSS columns on the page. The size of the title and the number of lines it occupies also do not affect the output.



x
Syntax: How to Control Display of an ACROSS Title for a Single Field
SET ACRSVRBTITL = {OFF|ON|HIDEONE} 
ON TABLE SET ACRSVRBTITL {OFF|ON|HIDEONE}

where:

OFF

Suppresses the title when there is only one display field.

ON

Displays the title when there is only one display field.

HIDEONE

Suppresses the title when there is only one display field, but the request contains one or more of the following components:

  • Fields in a heading or footing.
  • Fields whose display is suppressed with the NOPRINT phrase.
  • Reformatted fields (which are normally counted twice).
  • A COMPUTE command referencing multiple fields.


Example: Displaying or Suppressing a Single ACROSS Column Title

The following request against the GGSALES data source has one display field, DOLLARS. The ACRSVRBTITL parameter is OFF (the default):

SET ACRSVRBTITL=OFF
TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

On the output, there is no column title that indicates what the number in each column represents:

Region                                              
Midwest      Northeast    Southeast    West         
----------------------------------------------------
11514345     11494543     11781285     11674908     

The following is the same request with ACRSVRBTITL=ON:

SET ACRSVRBTITL=ON
TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

On the output, the title Sales appears above each field value in the ACROSS group:

Region                                              
Midwest      Northeast    Southeast    West         
   Sales        Sales        Sales        Sales     
----------------------------------------------------
11514345     11494543     11781285     11674908     


Example: Hiding an ACROSS Title With HIDEONE

The following request against the GGSALES data source has a display field in the heading:

SET ACRSVRBTITL=OFF         
TABLE FILE GGSALES          
HEADING               
"Sales Report for <CATEGORY with ACRSVRBTITL=OFF"
" "                         
SUM DOLLARS AS Sales        
BY CATEGORY                 
ACROSS REGION               
WHERE CATEGORY EQ 'Food'    
ON TABLE SET PAGE NOPAGE    
END                         

With the default setting ACRSVRBTITL=OFF, the field in the heading counts and the ACROSS title Sales is not suppressed:

Sales Report for Food with ACRSVRBTITL=OFF                   
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category        Sales       Sales       Sales       Sales    
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Changing ACRSVRBTITL to ON produces the same report:

Sales Report for Food with ACRSVRBTITL=ON                    
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category        Sales       Sales       Sales       Sales    
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Changing ACRSVRBTITL to HIDEONE suppresses the ACROSS title Sales:

Sales Report for Food with ACRSVRBTITL=HIDEONE               
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category                                                     
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Top of page

x
512-Character Field Names

The following attributes can be a maximum of 512 characters long:


Top of page

x
Zero Removal in Date-Time Formats

The components in date-time formats can be displayed using a number of options. For example, when a day number is less than 10, you can display it with zero suppression. Zero suppression replaces the zero with a blank space, leaving the remaining digit in the same position it occupied when the zero was displayed.

There are now zero removal options for month and day numbers. Zero removal eliminates the zero and moves the remaining portion of the field one position to the left.

The option for zero removal of a month number is the lowercase letter o. The option for zero removal of the day number is the lowercase letter e. Use of these options also requires a date separator. The N option to remove the separator is not supported with zero removal.

Using the o option for the month forces the day component to use the e option, even if d or D is specified for the day component in the format.



Example: Using Zero Removal for Date-Time Month and Day Numbers

The following request creates the date-time value 01/01/2013. It then displays this value using:

DEFINE FILE GGSALES           
DATE1A/HMDYY = DT(01/01/2013);
DATE1B/HoeYY = DATE1A;        
DATE1C/HodYY = DATE1A;        
DATE1D/HMeYY = DATE1A;        
END                           
TABLE FILE GGSALES            
SUM DOLLARS NOPRINT           
DATE1A AS 'HMDYY'             
DATE1B AS 'HoeYY'             
DATE1C AS 'HodYY'             
DATE1D AS 'HMeYY'             
ON TABLE SET PAGE NOPAGE      
END                           

The output is:

HMDYY       HoeYY       HodYY       HMeYY    
-----       -----       -----       -----    
01/01/2013  1/1/2013    1/1/2013    01/1/2013


Example: Comparing Zero Suppression With Zero Removal

The following request creates two dates with date-time formats in which the date component has a leading zero (01). In the first date, the day component is the first component and displays on the left. In the second date, the day component is the second component and displays in the middle. The request prints these dates:

DEFINE FILE GGSALES           
DATE1A/HDMYY = DT(01/12/2012);
DATE2A/HMDYY = DT(12/01/2012);
DATE1B/HdMYY = DATE1A;        
DATE2B/HMdYY = DATE2A;        
DATE1C/HeMYY = DATE1A;        
DATE2C/HMeYY = DATE2A;        
END                           
TABLE FILE GGSALES            
SUM DOLLARS NOPRINT           
DATE1A AS 'HDMYY'             
DATE2A AS ''      OVER        
DATE1B AS 'HdMYY'             
DATE2B AS ''      OVER        
DATE1C AS 'HeMYY'             
DATE2C AS ''                  
ON TABLE SET PAGE NOPAGE      

On the output, the first row shows the date with all zeros displayed. The second row shows zero suppression of the day number, where the zero has been replaced by a blank space so that all the components are aligned with the components on row 1. The last row shows zero removal, where the zero has been removed from the day number, and all of the remaining characters have been shifted over to the left:

HDMYY  01/12/2012    12/01/2012
HdMYY   1/12/2012    12/ 1/2012
HeMYY  1/12/2012     12/1/2012 

WebFOCUS