Reporting Language > Coding a User Interface > Defining Parameter-Based Filters

Defining Parameter-Based Filters

Topics:

The following topics describe how to define parameter-based filters to be displayed in an Autoprompt form.

Adding a Simple Filter to an Autoprompt Form

How to:

You can add a simple filter to prompt the user for the value of a variable.

Syntax: How to Add a Simple Filter 

'&variable'

where:

&variable

Is the variable name, including the ampersand (&), for which the value is being prompted. If the variable value will be compared to an alphanumeric field, you must enclose the variable in single quotation marks ('). Character strings must be enclosed in single quotation marks to be handled by most database engines.

If the variable value contains a single quotation mark ('), use the QUOTEDSTRING suffix on the variable, instead of enclosing the value in single quotation marks.

Example: Adding a Simple Filter

The following request prompts the user for a Quantity in Stock value. The variable &QTYSTOCK is not enclosed within single quotation marks (') because the QTY_IN_STOCK field is a numeric format. 

TABLE FILE ibinccen/centord
SUM CENTORD.INVSEG.QTY_IN_STOCK
BY CENTORD.INVSEG.PRODNAME
WHERE CENTORD.INVSEG.QTY_IN_STOCK LT &QTYSTOCK
ON TABLE PCHOLD FORMAT HTML
END

Adding a Variable Description to a Filter

How to:

You can add a description for a variable that will replace the variable name in the form that prompts the user for a value. The description is appended to the variable name in the filter (WHERE) expression of the report request.

Syntax: How to Add a Variable Description to the Filter

How to: 

'&variable.description.'

where:

&variable

Is the variable name, including the ampersand (&), for which the value is being prompted. If the variable value will be compared to an alphanumeric field, you must enclose the variable in single quotation marks ('). Character strings must be enclosed in single quotation marks to be handled by most database engines.

If the variable value contains a single quotation mark ('), use the QUOTEDSTRING suffix on the variable, instead of enclosing the value in single quotation marks.

description

Is a description of the variable that replaces the variable name in the prompt.

Note: The following are usage limitations for description values:

  • The ampersand (&) character is supported within the description for a variable by specifying the pipe character (|) immediately after the & character in the description.
  • The period (.) cannot be used in a description because a period is the delimiter to specify the beginning and end of the description value.
  • The open parenthesis character must have a space before it.

Example: Adding a Variable Description

The following request provides a more descriptive name for the QTYSTOCK field within the Autoprompt form. The variable description will precede the field controls on the Autoprompt form. The variable &QTYSTOCK is not enclosed within single quotation marks (') because the QTY_IN_STOCK field is a numeric format. 

TABLE FILE ibinccen/centord
SUM CENTORD.INVSEG.QTY_IN_STOCK
BY CENTORD.INVSEG.PRODNAME
WHERE CENTORD.INVSEG.QTY_IN_STOCK LT &QTYSTOCK.Quantity In Stock:.;
ON TABLE PCHOLD FORMAT HTML
END

Syntax: How to Add a Range of Values List 

&variable.(FROM Range1 TO Range2).

where:

&variable

Is the numeric variable, including the ampersand (&), for which you are supplying a list of values.

Range1

Is the starting numeric value of the range of the list of values.

Range2

Is the ending numeric value of the range of the list of values.

Example: Adding a Range of Values List

The following request provides a list of numeric values that are valid for the QTY_IN_STOCK field. This list is populated with values from the CENTORD data source that fall within the range. 

TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE QTY_IN_STOCK GT &STOCK.(FROM 5000 TO 10000).
END

Specifying a Format for a Variable

How to:

You can specify a format for a variable to specify how to evaluate the variable for validation and sorting.

Syntax: How to Specify a Format 

&variable.(|FORMAT=format)

where:

&variable

Is the variable name, including the ampersand (&), for which the value is being prompted. If the variable value will be compared to an alphanumeric field, you must enclose the variable in single quotation marks ('). Character strings must be enclosed in single quotation marks to be handled by most database engines.

If the variable value contains a single quotation mark ('), use the QUOTEDSTRING suffix on the variable, instead of enclosing the value in single quotation marks.

format

Is the format of the field. The default value is D12.2. For information on field formats, see Describing Data With WebFOCUS Language.

Example: Specifying a Format

The following request specifies a seven digit integer format (I7) for the QTYSTOCK field. 

TABLE FILE ibinccen/centord
SUM CENTORD.INVSEG.QTY_IN_STOCK
BY CENTORD.INVSEG.PRODNAME
WHERE CENTORD.INVSEG.QTY_IN_STOCK LT &QTYSTOCK.(|FORMAT=I7).Quantity In Stock:;
ON TABLE PCHOLD FORMAT HTML
END

Note: If a non-numeric value is typed, a validation message displays.

Setting a Default Variable Value

How to:

You can set a default variable value. This value will be used in the report if one is not supplied by the user. The code that specifies a default variable value must be added before the report request.

Syntax: How to Set a Default Variable Value 

-DEFAULT &variable=value

where:

&variable

Is the variable name, including the ampersand (&), for which the value is being prompted. If the variable value will be compared to an alphanumeric field, you must enclose the variable in single quotation marks ('). Character strings must be enclosed in single quotation marks to be handled by most database engines.

If the variable value contains a single quotation mark ('), use the QUOTEDSTRING suffix on the variable, instead of enclosing the value in single quotation marks.

value

Is the default value for the variable. Embedded single quotation marks are indicated by two contiguous single quotation marks (' '). Quotation marks are required around variables containing delimiters, which include spaces and commas (,).

To specify multiple default values for a variable that will be used with a multiselect filter, each value must be enclosed in two single quotation marks (' ') and include the operation used in the filter the variable is referenced in (for example, OR) between each value. In addition, the entire string must be enclosed in single quotation marks ('). For example, in the following example, each of the values within the string are enclosed in two single quotation marks (' ') and the entire string is enclosed in quotation marks, such that the beginning and ending of the string has three (3) quotation marks. 

-DEFAULT &parm1 = '''value1'' OR ''value2'' OR ''value3''';

Example: Setting a Default Variable Value

The following sets a default value of NY for the STATE field. Note that there must be an ampersand in front of the field name in the -DEFAULT command for the variable to contain a default attribute in the Autoprompt form. 

-DEFAULT &STATE=NY
TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE STATE EQ '&STATE.2 letters for US State.'
WHERE STORENAME EQ '&STORENAME.Store Name.'
WHERE PRODNAME EQ '&PRODNAME.Product Name.'
END

Setting a Hidden Variable

How to:

Dialogue Manager variables can be given default values using the -DEFAULT command. These variables are returned in the XML describe information used for WebFOCUS parameter prompting features (HTML Autoprompt, HTML Canvas, and ReportCaster Scheduling).

You can initialize a variable value and prevent it from being used for WebFOCUS parameter prompting by using the -DEFAULTH command. Variables initialized with -DEFAULTH are not used for parameter prompting. Since these variables are not displayed by the parameter prompting features, they are hidden from the user.

The code that specifies a default variable value must be added before the report request.

Syntax: How to Define and Initialize a Hidden Variable 

-DEFAULTH &variable=value

where:

&variable

Is the name of the hidden variable, including the ampersand (&), for which the value is being prompted. If the variable value will be compared to an alphanumeric field, you must enclose the variable in single quotation marks ('). Character strings must be enclosed in single quotation marks to be handled by most database engines.

If the variable value contains a single quotation mark ('), use the QUOTEDSTRING suffix on the variable, instead of enclosing the value in single quotation marks.

value

Is the initial value for the variable. Embedded single quotation marks are indicated by two contiguous single quotation marks (' '). Quotation marks are required around variables containing delimiters, which include spaces and commas (,).

To specify multiple default values for a variable that will be used with a multiselect filter, each value must be enclosed in two single quotation marks (' ') and include the operation used in the filter the variable is referenced in (for example, OR) between each value. In addition, the entire string must be enclosed in single quotation marks ('). For example, in the following example, each of the values within the string are enclosed in two single quotation marks (' ') and the entire string is enclosed in quotation marks, such that the beginning and ending of the string has three (3) quotation marks. 

-DEFAULTH &parm1 = '''value1'' OR ''value2'' OR ''value3''';

Adding a Single-Select List of Values

How to:

You can add a single-select list of values. The values in the list can be static or dynamic.

Syntax: How to Add a Static Single-Select List of Values 

'&variable.(value,value2[,value3][,value4]... 
[|FORMAT=format] [,WITHIN=within]).[description.]'

where:

&variable

Is the variable, including the ampersand (&), for which you are supplying a list of values.

value, value2, value3, value4...

Are the values comprising the list of selectable variable values.

format

Specifies how to evaluate the variable for validation and sorting. The FORMAT attribute is for all filter types.

within

The WITHIN attribute is for chaining supported with the Responsive Autoprompt template. The dynamic or static filter type is required for the first field in a chained grouping that is created for fields within the same dimension hierarchy. The second and subsequent fields in the chained group must be a dynamic filter The filters should be in the order that the fields are referenced in the Master File in a dimension hierarchy. The parameter names must be the same name as the field name in the Master File.

description

Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.

Note: A static value cannot contain the comma character (,) when specified within the WHERE statement syntax because the comma character (,) is the delimiter character for specifying a display value. When there is a comma (,) in one or more static values, put the values in a file and use the dynamic list (FIND) functionality.

Example: Adding a Static Single-Select List of Values

The following provides a list of values that are valid for the STORENAME field. The user can select only one value from the list. 

-DEFAULT &STATE=NY
TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE STATE EQ '&STATE.2 letters for US State.'
WHERE STORENAME EQ '&STORENAME.(eMart,TV City,Web Sales).Store Name.'
WHERE PRODNAME EQ '&PRODNAME.Product Name.'
END

Syntax: How to Add a Dynamic Single-Select List of Values 

'&variable.(FIND return_fieldname [,display_fieldname] IN datasource
[|SORT=sortoption] [|FORMAT=format] [,WITHIN=within]).[description.]'

where:

&variable

Is the variable, including the ampersand (&), for which you are supplying a list of values.

return_fieldname

Is the name of the field containing the possible variable values that are returned to the FOCEXEC.

display_fieldname

Is the name of the field containing the possible variable values that are displayed in the Autoprompt form.

Note: Both return_fieldname and display_fieldname can be a DEFINE field in a Master File but not a DEFINE field in a procedure.

datasource

Is the data source that contains the fields specified in return_fieldname and display_fieldname. If the fields reside in a cross-reference file of a data source used in a join, use the data source name that contains the fields.

Note:
  • For dynamic lists, the WebFOCUS Client constructs the request to obtain the values using the specified data source. All environmental commands, such as SET commands, needed to obtain the values from the specified data source must be issued in the WebFOCUS Server profile, user profile, or WebFOCUS Client profile.
  • Dynamic list values that display in Responsive Autoprompt are organized in a case-insensitive sort order. Dynamic list values that display in HTML Autoprompt are organized in the sort order returned by the Reporting Server, which is determined by the operating system of the machine.
sortoption

Provides the ability to specify how to sort the values returned for a dynamic list. Valid values are:

  • ASCENDING, which sorts in low to high order. This is the default value when sort processing is not specified.
  • DESCENDING, which sorts in high to low order.
format

Specifies how to evaluate the variable for validation and sorting when the sort option is not specified. The FORMAT attribute is for all filter types.

within

The WITHIN attribute is for chaining supported with the Responsive Autoprompt template. The dynamic or static filter type is required for the first field in a chained grouping that is created for fields within the same dimension hierarchy. The second and subsequent fields in the chained group must be a dynamic filter. The filters should be in the order that the fields are referenced in the Master File in a dimension hierarchy. The parameter names must be the same name as the field name in the Master File.

description

Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.

Example: Adding a Dynamic Single-Select List of Values

The following provides a list of values that are valid for the product name field. This list is populated with values from the CENTORD data source. The user can select only one value from the list. 

-DEFAULT &STATE=NY
TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PROD_NUM
ON TABLE SUBHEAD
"Inventory Report"
WHERE STATE EQ '&STATE.2 letters for US State.'
WHERE STORENAME EQ '&STORENAME.(eMart,TV City,Web Sales).Store Name.'
WHERE PROD_NUM EQ '&PROD_NUM.(FIND PROD_NUM,PRODNAME IN CENTORD).Product Name.'
END

Example: Adding a Dynamic Single-Select List of Values in Descending Sort Order

The following provides a list of values that are valid for the product name field. This list is populated with values from the CENTORD data source. The user can select only one value from the list. 

-DEFAULT &STATE=NY
TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PROD_NUM
ON TABLE SUBHEAD
"Inventory Report"
WHERE STATE EQ '&STATE.2 letters for US State.'
WHERE STORENAME EQ '&STORENAME.(eMart,TV City,Web Sales).Store Name.'
WHERE PROD_NUM EQ '&PROD_NUM.(FIND PROD_NUM,PRODNAME IN CENTORD|SORT=DESCENDING).Product Name.'
END

Example: Chaining Values

Chained fields limit the values that are available to select from, based on the fields referenced in the Master File dimension hierarchy. The WITHIN attribute specifies to chain the filters. The first field in a chained group must be a dynamic or static list and the second and subsequent filters in the chain must be a dynamic filter. The following provides a list of chained values for the Product Type, Product Category, and Product Name fields. For example, the values listed for the Product Category field are those for the values selected for the Product Type field. 

TABLE FILE ibinccen/centord
SUM CENTORD.INVSEG.QTY_IN_STOCK
CENTORD.INVSEG.PRICE
BY CENTORD.INVSEG.PRODTYPE
BY CENTORD.INVSEG.PRODCAT
BY CENTORD.INVSEG.PRODNAME
WHERE CENTORD.INVSEG.PRODTYPE EQ &PRODTYPE.(OR(FIND CENTORD.INVSEG.PRODTYPE IN ibinccen/CENTORD |FORMAT=A19)).PRODTYPE:.;
WHERE CENTORD.INVSEG.PRODCAT EQ &PRODCAT.(OR(FIND CENTORD.INVSEG.PRODCAT IN ibinccen/CENTORD |FORMAT=A22 ,WITHIN=PRODTYPE)).PRODCAT:.;
WHERE CENTORD.INVSEG.PRODNAME EQ &PRODNAME.(OR(FIND CENTORD.INVSEG.PRODNAME IN ibinccen/CENTORD |FORMAT=A30 ,WITHIN=PRODCAT)).Product Name:.;
END

Adding a Multiselect List of Values

How to:

Reference:

You can add a multiselect list of values. The values in the list can be static or dynamic.

Syntax: How to Add a Static Multiselect List of Values 

&variable.(operation (<displayvalue1,value1>, <displayvalue2,value2>,...<displayvalueN,valueN>)
[|FORMAT=format] [,WITHIN=within]).[description.]

where:

&variable

Is the variable, including the ampersand (&), for which you are supplying a list of values.

operation

Specifies how to evaluate multiple values. May contain the value OR, AND, or a comma (,). If omitted, the default value is OR.

displayvalue, displayvalue2, displayvalue3, displayvalue4...

Are the values comprising the list of selectable variable values that you can select.

value, value2, value3, value4...

Are the values comprising the list of selectable variable values passed to the reporting server.

format

Specifies how to evaluate the variable for validation and sorting. The FORMAT attribute is for all filter types.

within

The WITHIN attribute is for chaining supported with the Responsive Autoprompt template. The dynamic or static filter type is required for the first field in a chained grouping that is created for fields within the same dimension hierarchy. The second and subsequent fields in the chained group must be a dynamic filter The filters should be in the order that the fields are referenced in the Master File in a dimension hierarchy. The parameter names must be the same name as the field name in the Master File.

description

Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.

Note: A static value cannot contain the comma character (,) when specified within the WHERE statement syntax because the comma character (,) is the delimiter character for specifying a display value. When there is a comma (,) in one or more static values, put the values in a file and use the dynamic list (FIND) functionality.

Example: Adding a Static Multiselect List of Values

The following request provides a list of values that are valid for the STORENAME field. The user can select more than one value from the list. 

-DEFAULT &STATE=NY
TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STATE BY STORENAME BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE STATE EQ '&STATE.2 letters for US State.'
WHERE STORENAME EQ &STORENAME.(OR(eMart,TV City,Web Sales)).Store Name.
WHERE PRODNAME EQ '&PRODNAME.Product Name.'
END

Syntax: How to Add a Dynamic Multiselect List of Values 

&variable.(operation (FIND return_fieldname [,display_fieldname] IN datasource[|SORT=sortoption] [|FORMAT=format] [,WITHIN=within])).[description.]

where:

&variable

Is the variable, including the ampersand (&), for which you are supplying a list of values.

operation

Specifies how to evaluate multiple values. May contain the value OR, AND, or a comma (,). If omitted, the default value is OR.

return_fieldname

Is the name of the field containing the possible variable values that are returned to the FOCEXEC.

display_fieldname

Is the name of the field containing the possible variable values that are displayed in the Autoprompt form.

Note: Both return_fieldname and display_fieldname can be a DEFINE field in a Master File but not a DEFINE field in a procedure.

datasource

Is the data source that contains the fields specified in return_fieldname and display_fieldname. If the fields reside in a cross-reference file of a data source used in a join, use the data source name that contains the fields.

Note:
  • For dynamic lists, the WebFOCUS Client constructs the request to obtain the values using the specified data source. All environmental commands, such as SET commands, needed to obtain the values from the specified data source must be issued in the WebFOCUS Server profile, user profile, or WebFOCUS Client profile.
  • Dynamic list values that display in Responsive Autoprompt are organized in a case-insensitive sort order. Dynamic list values that display in HTML Autoprompt are organized in the sort order returned by the Reporting Server, which is determined by the operating system of the machine.
sortoption

Provides the ability to specify how to sort the values returned for a dynamic list. Valid values are:

  • ASCENDING, which sorts in low to high order. This is the default value when sort processing is not specified.
  • DESCENDING, which sorts in high to low order.
format

Specifies how to evaluate the variable for validation and sorting when the sort option is not specified. The FORMAT attribute is for all filter types.

within

The WITHIN attribute is for chaining supported with the Responsive Autoprompt template. The dynamic or static filter type is required for the first field in a chained grouping that is created for fields within the same dimension hierarchy. The second and subsequent fields in the chained group must be a dynamic filter. The filters should be in the order that the fields are referenced in the Master File in a dimension hierarchy. The parameter names must be the same name as the field name in the Master File.

description

Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.

Example: Adding a Dynamic Multiselect List of Values

The following request provides a list of values that are valid for the state field. This list is populated with values from the CENTORD data source. The user can select more than one value from the list. 

TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STORE_CODE BY STATE BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE STORE_CODE EQ &STORE_CODE.(OR(FIND STORE_CODE,STORENAME IN CENTORD)).Store Name.
WHERE STATE EQ &STATE.(OR(CA,IL,MA,NY,NJ,FL,TX)).2 letters for US State.
WHERE PRODNAME EQ '&PRODNAME.(FIND PRODNAME IN CENTORD).Product Name.'
END

Example: Adding a Dynamic Multiselect List of Values in Descending Sort Order

The following request provides a list of values that are valid for the state field. This list is populated with values from the CENTORD data source. The user can select more than one value from the list. 

TABLE FILE CENTORD
SUM QTY_IN_STOCK BY STORE_CODE BY STATE BY PRODNAME
ON TABLE SUBHEAD
"Inventory Report"
WHERE STORE_CODE EQ &STORE_CODE.(OR(FIND STORE_CODE,STORENAME IN CENTORD|SORT=DESCENDING)).Store Name.
WHERE STATE EQ &STATE.(OR(CA,IL,MA,NY,NJ,FL,TX)).2 letters for US State.
WHERE PRODNAME EQ '&PRODNAME.(FIND PRODNAME IN CENTORD).Product Name.'
END

Reference: Rules for a Multiselect List of Values

  • When creating a multiselect list of values, do not add single quotation marks (') around individual values. WebFOCUS automatically encloses each value in single quotation marks (') for multiselect lists. If you add your own single quotation marks ('), an error will result. For example, WHERE COUNTRY EQ '&COUNTRY' will generate an error. Use &COUNTRY without quotation marks.
  • When using the result of a multiselected list of values as a parameter in a drill down, use QUOTEDSTRING to handle the embedded blanks that are added as separators between the parameters.

    The following example is correct:

    FOCEXEC=DRILLDOWN(COUNTRY=&COUNTRY.QUOTEDSTRING OTHER=xxx)

    The next example is not correct, and the embedded blanks within the &COUNTRY parameter will terminate the drill down. 

    FOCEXEC=DRILLDOWN(COUNTRY=&COUNTRY OTHER=xxx)

    For more information, see Creating a Standard Quote-Delimited String.

Reference: Selecting Values for Multiselect Variables

  • If your report contains a static multiselect list of values and an HTML Autoprompt template is configured, you can select the Select All option to select all values in the parameters list. Internally, selecting the Select All option results in the parameter being set to all the values in the multiselect list separated by the qualifier, AND, OR, or the comma character (,) in the WHERE statement. For more information, see Internal Processing of Select All.
  • If your report contains a dynamic multiselect list of values, you can choose the All Values option to not select any values from the parameter list. The All Values option is available with the Responsive and HTML Autoprompt templates. Internally, either the _FOC_NULL value or the FOC_NONE value is passed for the dynamic multiselect list All Values option. By default, _FOC_NULL is passed. However, you can control which of these values is passed for the All Values option with the IBIF_describe_null variable that configures the value the WebFOCUS Client will send.
  • Individual phrases and expressions that contain the value _FOC_NULL are removed from the procedure prior to the procedure being run by the reporting server. For more information about _FOC_NULL, see Internal Processing of _FOC_NULL.
  • Non-Dialogue manager lines (do not begin with a '-' dash) that contain the value FOC_NONE are removed from the procedure prior to the procedure being run by the reporting server. For more information about FOC_NONE, see Internal Processing of _FOC_NONE.

Reference: Internal Processing of _FOC_NULL

When you select or manually code the All Values option within a multiselect list, the parameter value sent to the WebFOCUS Server is _FOC_NULL, by default. Internal processing is then performed by the WebFOCUS Server Describe layer to search the procedure and selectively remove WebFOCUS phrases (such as BY and WHERE) or expressions that contain _FOC_NULL. The procedure is then passed to the core WebFOCUS engine to run the request.

The following is a WHERE example for selecting or manually coding the All Values option that generates the _FOC_NULL value for dynamic multiselect lists: 

WHERE CATEGORY EQ &CATEGORY.(OR(FIND CATEGORY IN MOVIES)).CATEGORY. AND DIRECTOR EQ &DIRECTOR.(OR(FIND DIRECTOR IN MOVIES)).DIRECTOR.;

If All Values is returned for CATEGORY, the test for DIRECTOR will still be processed.

The following is a WHERE example for selecting or manually coding the All Values option that generates the _FOC_NULL value for a static multiselect list and a dynamic multiselect list: 

WHERE COPIES EQ 
&COPIES.(<All Values,_FOC_NULL>,<1,1>,<2,2>,<3,3>).COPIES. 
AND DIRECTOR EQ &DIRECTOR.(OR(FIND DIRECTOR IN MOVIES)).DIRECTOR.;

Each list must be coded completely on a single line in the procedure.

When coding a HEADING, FOOTING, SUBHEAD, or SUBFOOT, you should have a minimum of one line that does not contain a parameter that could be assigned the value of _FOC_NULL.

Reference: Internal Processing of FOC_NONE

When you select or manually code the All Values option within a multiselect list, the parameter value that may be sent to the WebFOCUS Server is FOC_NONE. Internal processing is then performed by the WebFOCUS Server Describe layer to search the procedure and remove all non-Dialogue Manager lines (do not begin with a dash ‘-’) that contain FOC_NONE. The procedure is then passed to the core WebFOCUS engine to run the request.

The following are WHERE examples for selecting or manually coding the All Values option that generates the FOC_NONE value:

  • Dynamic multiselect
    WHERE CATEGORY EQ &CATEGORY.(OR(FIND CATEGORY IN MOVIES)).CATEGORY.;
  • Static multiselect with FOC_NONE (coded on a single line in the procedure (FEX))
    WHERE COPIES EQ &COPIES.(<All Values,FOC_NONE>,<1,1>,<2,2>,<3,3>).
COPIES.;

The following are FOC_NONE coding considerations:

  • When coding a HEADING, FOOTING, SUBHEAD, or SUBFOOT, you should have a minimum of one line that does not contain a parameter that could be assigned the value of FOC_NONE.
  • If the All Values option is used within a dynamic multiselect list, or if a manually coded WHERE statement passes the FOC_NONE parameter value:
    • Code a WHERE statement that passes the FOC_NONE value on a single line in the procedure (FOCEXEC). This prevents the WHERE statement from being partially removed from the request and avoids a FOC002 error.
    • Do not code a complex WHERE statement on a single line when FOC_NONE is passed for a field. This prevents the entire complex WHERE statement from being removed from the request. Note that a complex WHERE statement has selection tests for more than one field using AND or OR operators.

Reference: Internal Processing of Select All

When you run a request with a static multiselect list, the HTML Autoprompt facility dynamically adds the Select All option to the list of values that, when selected, assigns all the values in the static multiselect list to the parameter. When the Select All and individual values are selected, the Select All value is ignored.

Internal processing is then done by the Autoprompt facility to assign the parameter the select list values separated by the qualifier (for example, AND or OR) specified in the WHERE statement that references the parameter.

The following example shows a WHERE statement using an OR qualifier with a static multiselect list for the parameter &RATING. The parameter &RATING is referenced in the WHERE statement and in the HEADING. The HEADING shows how the parameter value is set to all values in the multiselect list separated by the OR qualifier. 

TABLE FILE MOVIES
HEADING
" Ratings selected: "
" &RATING "
PRINT
     'MOVIES.MOVINFO.TITLE'
     'MOVIES.MOVINFO.COPIES'
BY 'MOVIES.MOVINFO.RATING'
BY 'MOVIES.MOVINFO.CATEGORY'
HEADING
""
FOOTING
""
WHERE MOVIES.MOVINFO.RATING EQ &RATING.(OR(<General Audience,G>,<Not Rated,NR>,<Parental Guidance,PG>,<PG Over 13,PG13>,<R Over 18,R>)).Rating.;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
ENDSTYLE
END

The following image shows the HTML Autoprompt form and report when Select All is selected.



The WebFOCUS Client assigns &RATING the list of values by creating and adding the following syntax to the request sent to the Reporting Server: 

-SET &RATING='''G'' OR ''NR'' OR ''PG'' OR ''PG13'' OR ''R''';

Adding Static Lists of Display and Sort Fields

How to:

In a static list of fields, you specify the field names and display values in the select list for the display or sort command.

Syntax: How to Add a Static Single-Select List of Sort or Display Fields 

cmd &var.(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,valN>]).[desc.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, ADD, BY, and ACROSS.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

dsply1, dsply2, ...

Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding field.

val1, val2, ...

Are the field names passed to the Reporting Server. They can be qualified field names and can be omitted if the display value is identical to the field name.

desc

Is an optional description of the variable.

Note: The entire command, up to and including the description, if there is one, must appear on one line of the report request.

Example: Adding Single-Select Lists of Sum Fields and Sort Fields

The following request against the GGSALES data source enables the user to select one of the following from each list when running the procedure:

  • Sum: DOLLARS, BUDDOLLARS, UNITS.
  • BY fields: CATEGORY, PRODUCT.
  • ACROSS fields: REGION, ST, None.

    Note: For information about the All Values option, see Internal Processing of _FOC_NONE. 

TABLE FILE GGSALES
HEADING
"Sales Report Summary for: "
"Fields: &SumFlds"
"Sort: &SortBy"
"Across: &Acrs"
" "
SUM &SumFlds.(<Sales,DOLLARS>,<Budget,BUDDOLLARS>,<Units,UNITS>).Sum.
BY &SortBy.(<Category,GGSALES.SALES01.CATEGORY>,<PRODUCT>).By.
ACROSS &Acrs.(<Area,REGION>,<State,ST>,<None,FOC_NONE>).Across.
WHERE REGION NE 'West'
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=ARIAL,SIZE=10,SQUEEZE=ON,$
TYPE=HEADING, FONT=ARIAL, STYLE=BOLD, JUSTIFY=LEFT,$
TYPE=HEADING, LINE=1, FONT=ARIAL, STYLE=BOLD, JUSTIFY=LEFT,$
TYPE=TITLE, BACKCOLOR=BLACK, COLOR=WHITE,$
TYPE=ACROSSTITLE, BACKCOLOR=LIGHT BLUE, COLOR=BLUE,$
TYPE=ACROSSVALUE, BACKCOLOR=LIGHT GRAY, COLOR=GREEN,$
END

Running the request opens the following Autoprompt form on which the selection for the REGION sort field displays as Area, the selection for the DOLLARS field displays as Sales, the selection for the CATEGORY sort field and the BUDDOLLARS and UNITS fields display in mixed-case, and the display value for PRODUCT is the same as the sort field name. Only one selection is allowed from each list.



Selecting Sales, Category, and None produces the following report:



Syntax: How to Add a Static Multiselect List of Sort Fields 

cmd &var.(cmd(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,srtN>])).[desc.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are BY and ACROSS. Adding the command around the select list creates a multiselect list.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

dsply1, dsply2, ...

Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding sort field.

val1, val2, ...

Are the field names passed to the Reporting Server. They can be qualified field names and can be omitted if the display value is identical to the field name.

desc

Is an optional description of the variable.

Note: The entire command, up to and including the description, if there is one, must appear on one line of the report request.

Syntax: How to Add a Static Multiselect List of Display Fields 

cmd &var.(AND(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,srtN>])).[desc.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, and ADD.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

AND

Is the connector that creates a multiselect list.

dsply1, dsply2, ...

Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding sort field.

val1, val2, ...

Are the field names passed to the Reporting Server. They can be qualified field names and can be omitted if the display value is identical to the field name.

desc

Is an optional description of the variable.

Note: The entire command, up to and including the description, if there is one, must appear on one line of the report request.

Example: Adding Multiselect Lists of Sum Fields and Sort Fields

The following request against the GGSALES data source enables the user to select one or more of the following when running the procedure:

  • Sum: DOLLARS, BUDDOLLARS, UNITS.
  • BY fields: CATEGORY, PRODUCT.
  • ACROSS fields: REGION, ST, None. 
TABLE FILE GGSALES
HEADING
"Sales Report Summary for: "
"Fields: &SumFlds"
"Sort: &SortBy"
"Across: &Acrs"
" "
SUM &SumFlds.(AND(<Sales,DOLLARS>,<Budget,BUDDOLLARS>,<Units,UNITS>)).Sum.
BY &SortBy.(BY(<Category,GGSALES.SALES01.CATEGORY>,<PRODUCT>)).By.
ACROSS &Acrs.(ACROSS(<Area,REGION>,<State,ST>,<None,FOC_NONE>)).Across.
WHERE REGION NE 'West'
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=ARIAL,SIZE=10,SQUEEZE=ON,$
TYPE=HEADING, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=HEADING, LINE=1, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TITLE, BACKCOLOR=BLACK, COLOR=WHITE,$
TYPE=ACROSSTITLE, BACKCOLOR=LIGHT BLUE, COLOR=BLUE,$
TYPE=ACROSSVALUE, BACKCOLOR=LIGHT GRAY, COLOR = GREEN,$
END

Running the request opens the following Autoprompt form on which the selection for the REGION sort field displays as Area, the selection for the DOLLARS field displays as Sales, the selection for the CATEGORY sort field and the BUDDOLLARS and UNITS fields display in mixed-case, and the display value for PRODUCT is the same as the sort field name:



Selecting Sales, Units, Category, and Area produces the following report:



Adding Dynamic Lists of Display and Sort Fields

How to:

Reference:

In a dynamic list of fields, you use the FIND command in the select list to retrieve field names and display values from a WebFOCUS data source.

Reference: Creating Data Sources Containing Field Names

The following examples create three (3) data sources to be used in the examples that create dynamic field lists:

  • GGDETFLD, which contains names of fields from the GGSALES data source that will be used as display fields in select lists.
  • GGBYFLD, which contains names of fields from the GGSALES data source that will be used as BY fields in select lists.
  • GGACRFLD, which contains names of fields from the GGSALES data source that will be used as ACROSS fields in select lists.

Example: Creating the GGDETFLD Data Source

The following request creates the FOCUS data source named GGDETFLD: 

APP HOLD IBISAMP
DEFINE FILE SYSCOLUM
FLDDESC/A20=IF NAME EQ 'DOLLARS' THEN 'Dollars' 
  ELSE IF NAME EQ 'BUDDOLLARS' THEN 'Budget' 
  ELSE IF NAME EQ 'UNITS' THEN 'Units' ELSE 'N/A';
END
TABLE FILE SYSCOLUM
PRINT FLDDESC
BY NAME
WHERE TBNAME EQ 'GGSALES'
WHERE NAME EQ 'DOLLARS' OR 'BUDDOLLARS' OR 'UNITS'
ON TABLE HOLD AS GGDETFLD FORMAT FOCUS
END

Example: Creating the GGBYFLD Data Source

The following request creates the FOCUS data source named GGBYFLD: 

APP HOLD IBISAMP
DEFINE FILE SYSCOLUM
FLDDESC/A20=IF NAME EQ 'CATEGORY' THEN 'Category' 
  ELSE IF NAME EQ 'PRODUCT' THEN 'Product' ELSE 'N/A';
END
TABLE FILE SYSCOLUM
PRINT FLDDESC
BY NAME
WHERE TBNAME EQ 'GGSALES'
WHERE NAME EQ 'CATEGORY' OR 'PRODUCT'
ON TABLE HOLD AS GGBYFLD FORMAT FOCUS
END

Example: Creating the GGACRFLD Data Source

The following request creates the FOCUS data source named GGACRFLD: 

APP HOLD IBISAMP
DEFINE FILE SYSCOLUM
FLDDESC/A20=IF NAME EQ 'REGION' THEN 'Region' 
  ELSE IF NAME EQ 'ST' THEN 'State' ELSE 'N/A';
END
TABLE FILE SYSCOLUM
PRINT FLDDESC
BY NAME
WHERE TBNAME EQ 'GGSALES'
WHERE NAME EQ 'REGION' OR 'ST'
ON TABLE HOLD AS GGACRFLD FORMAT FOCUS
END

Syntax: How to Add a Dynamic Single-Select List of Display or Sort Fields 

cmd &var.(FIND return_fieldname [,display_fieldname] IN datasource).[description.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, ADD, BY, and ACROSS.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

return_fieldname

Is the name of the field containing the possible variable values that are returned to the FOCEXEC.

display_fieldname

Is the name of the field containing the possible variable values that are displayed in the Autoprompt form.

Note: Both return_fieldname and display_fieldname can be a DEFINE field in a Master File, but not a DEFINE field in a procedure.

datasource

Is the data source that contains the fields specified in return_fieldname and display_fieldname. If the fields reside in a cross-reference file of a data source used in a join, use the data source name that contains the fields.

Note:
  • For dynamic lists, the WebFOCUS Client constructs the request to obtain the values using the specified data source. All environmental commands, such as SET commands, needed to obtain the values from the specified data source must be issued in the WebFOCUS Server profile, user profile, or WebFOCUS Client profile.
  • Dynamic list values that display in Responsive Autoprompt are organized in a case-insensitive sort order. Dynamic list values that display in HTML Autoprompt are organized in the sort order returned by the Reporting Server, which is determined by the operating system of the machine.
description

Is an optional description of the variable.

Example: Creating Dynamic Single-Select Lists of Display and Sort Fields

The following request against the GGSALES data source creates three (3) single-select dynamic lists by retrieving field names from the FOCUS data sources GGDETFLD, GGBYFLD, and GGACRFLD: 

TABLE FILE GGSALES
HEADING
"Sales Report Summary for: "
"Fields: &SumFlds"
"Sort: &SortBy"
"Across: &Acrs"
" "
SUM &SumFlds.(FIND NAME, FLDDESC IN GGDETFLD).Sum Fields.
BY &SortBy.(FIND NAME, FLDDESC IN GGBYFLD).By.
ACROSS &Acrs.(FIND NAME, FLDDESC IN GGACRFLD).Across.
WHERE REGION NE 'West'
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=ARIAL,SIZE=10,SQUEEZE=ON,$
TYPE=HEADING, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=HEADING, LINE=1, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TITLE, BACKCOLOR=BLACK, COLOR=WHITE,$
TYPE=ACROSSTITLE, BACKCOLOR=LIGHT BLUE, COLOR=BLUE,$
TYPE=ACROSSVALUE, BACKCOLOR=LIGHT GRAY, COLOR = GREEN,$
END

Running the request opens the following Autoprompt form with single-select lists for the SUM, BY, and ACROSS commands:



Selecting Dollars on the Sum Fields drop-down list, Category on the By drop-down list, and Region on the Across drop-down list produces the following report:



Syntax: How to Add a Dynamic Multiselect List of Sort Fields 

cmd &var.(cmd(FIND return_fieldname [,display_fieldname]
IN datasource)).[description.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are BY and ACROSS. Adding the command around the select list creates a multiselect list of values.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

return_fieldname

Is the name of the field containing the possible variable values that are returned to the FOCEXEC.

display_fieldname

Is the name of the field containing the possible variable values that are displayed in the Autoprompt form.

Note: Both return_fieldname and display_fieldname can be a DEFINE field in a Master File, but not a DEFINE field in a procedure.

datasource

Is the data source that contains the fields specified in return_fieldname and display_fieldname. If the fields reside in a cross-reference file of a data source used in a join, use the data source name that contains the fields.

Note:
  • For dynamic lists, the WebFOCUS Client constructs the request to obtain the values using the specified data source. All environmental commands, such as SET commands, needed to obtain the values from the specified data source must be issued in the WebFOCUS Server profile, user profile, or WebFOCUS Client profile.
  • Dynamic list values that display in Responsive Autoprompt are organized in a case-insensitive sort order. Dynamic list values that display in HTML Autoprompt are organized in the sort order returned by the Reporting Server, which is determined by the operating system of the machine.
description

Is an optional description of the variable.

Syntax: How to Add a Dynamic Multiselect List of Display Fields 

cmd &var.(AND(FIND return_fieldname [,display_fieldname]
IN datasource)).[description.]

where:

cmd

Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, and ADD.

&var

Is the variable, including the ampersand (&), for which you are supplying a list of field values.

AND

Is the connector that creates a multiselect list.

return_fieldname

Is the name of the field containing the possible variable values that are returned to the FOCEXEC.

display_fieldname

Is the name of the field containing the possible variable values that are displayed in the Autoprompt form.

Note: Both return_fieldname and display_fieldname can be a DEFINE field in a Master File, but not a DEFINE field in a procedure.

datasource

Is the data source that contains the fields specified in return_fieldname and display_fieldname. If the fields reside in a cross-reference file of a data source used in a join, use the data source name that contains the fields.

Note:
  • For dynamic lists, the WebFOCUS Client constructs the request to obtain the values using the specified data source. All environmental commands, such as SET commands, needed to obtain the values from the specified data source must be issued in the WebFOCUS Server profile, user profile, or WebFOCUS Client profile.
  • Dynamic list values that display in Responsive Autoprompt are organized in a case-insensitive sort order. Dynamic list values that display in HTML Autoprompt are organized in the sort order returned by the Reporting Server, which is determined by the operating system of the machine.
description

Is an optional description of the variable.

Example: Creating Dynamic Multiselect Lists of Display and Sort Fields

The following request against the GGSALES data source creates three multiselect dynamic lists by retrieving field names from the FOCUS data sources GGDETFLD, GGBYFLD, and GGACRFLD: 

TABLE FILE GGSALES
HEADING
"Sales Report Summary for: "
"Fields: &SumFlds"
"Sort: &SortBy"
"Across: &Acrs"
" "
SUM &SumFlds.(AND(FIND NAME, FLDDESC IN GGDETFLD)).Sum Fields.
BY &SortBy.(BY(FIND NAME, FLDDESC IN GGBYFLD)).By.
ACROSS &Acrs.(ACROSS(FIND NAME, FLDDESC IN GGACRFLD)).Across.
WHERE REGION NE 'West'
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=ARIAL,SIZE=10,SQUEEZE=ON,$
TYPE=HEADING, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=HEADING, LINE=1, FONT=ARIAL, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TITLE, BACKCOLOR=BLACK, COLOR=WHITE,$
TYPE=ACROSSTITLE, BACKCOLOR=LIGHT BLUE, COLOR=BLUE,$
TYPE=ACROSSVALUE, BACKCOLOR=LIGHT GRAY, COLOR = GREEN,$
END

Running the request opens the following Autoprompt form with multiselect lists for the SUM, BY, and ACROSS commands.



Selecting Dollars, Units, Category, and Region produces the following report:



WebFOCUS

Feedback