The following topics describe how to define parameter-based filters to be displayed in an Autoprompt form.
How to: |
You can add a simple filter to prompt the user for the value of a variable.
'&variable'
where:
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.
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
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.
How to: |
'&variable.description.'
where:
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.
Is a description of the variable that replaces the variable name in the prompt.
Note: The following are usage limitations for description values:
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
&variable.(FROM Range1 TO Range2).
where:
Is the numeric variable, including the ampersand (&), for which you are supplying a list of values.
Is the starting numeric value of the range of the list of values.
Is the ending numeric value of the range of the list of values.
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
How to: |
You can specify a format for a variable to specify how to evaluate the variable for validation and sorting.
&variable.(|FORMAT=format)
where:
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.
Is the format of the field. The default value is D12.2. For information on field formats, see Describing Data With WebFOCUS Language.
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.
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.
-DEFAULT &variable=value
where:
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.
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''';
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
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.
-DEFAULTH &variable=value
where:
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.
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''';
How to: |
You can add a single-select list of values. The values in the list can be static or dynamic.
'&variable.(value,value2[,value3][,value4]... [|FORMAT=format] [,WITHIN=within]).[description.]'
where:
Is the variable, including the ampersand (&), for which you are supplying a list of values.
Are the values comprising the list of selectable variable values.
Specifies how to evaluate the variable for validation and sorting. The FORMAT attribute is for all filter types.
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.
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.
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
'&variable.(FIND return_fieldname [,display_fieldname] IN datasource [|SORT=sortoption] [|FORMAT=format] [,WITHIN=within]).[description.]'
where:
Is the variable, including the ampersand (&), for which you are supplying a list of values.
Is the name of the field containing the possible variable values that are returned to the FOCEXEC.
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.
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.
Provides the ability to specify how to sort the values returned for a dynamic list. Valid values are:
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.
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.
Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.
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
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
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
How to: |
Reference: |
You can add a multiselect list of values. The values in the list can be static or dynamic.
&variable.(operation (<displayvalue1,value1>, <displayvalue2,value2>,...<displayvalueN,valueN>) [|FORMAT=format] [,WITHIN=within]).[description.]
where:
Is the variable, including the ampersand (&), for which you are supplying a list of values.
Specifies how to evaluate multiple values. May contain the value OR, AND, or a comma (,). If omitted, the default value is OR.
Are the values comprising the list of selectable variable values that you can select.
Are the values comprising the list of selectable variable values passed to the reporting server.
Specifies how to evaluate the variable for validation and sorting. The FORMAT attribute is for all filter types.
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.
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.
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
&variable.(operation (FIND return_fieldname [,display_fieldname] IN datasource[|SORT=sortoption] [|FORMAT=format] [,WITHIN=within])).[description.]
where:
Is the variable, including the ampersand (&), for which you are supplying a list of values.
Specifies how to evaluate multiple values. May contain the value OR, AND, or a comma (,). If omitted, the default value is OR.
Is the name of the field containing the possible variable values that are returned to the FOCEXEC.
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.
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.
Provides the ability to specify how to sort the values returned for a dynamic list. Valid values are:
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.
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.
Is an optional description of the variable. For details and restrictions, see Adding a Variable Description to a Filter.
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
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
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.
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.
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:
WHERE CATEGORY EQ &CATEGORY.(OR(FIND CATEGORY IN MOVIES)).CATEGORY.;
WHERE COPIES EQ &COPIES.(<All Values,FOC_NONE>,<1,1>,<2,2>,<3,3>). COPIES.;
The following are FOC_NONE coding considerations:
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''';
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.
cmd &var.(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,valN>]).[desc.]
where:
Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, ADD, BY, and ACROSS.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding field.
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.
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.
The following request against the GGSALES data source enables the user to select one of the following from each list when running the procedure:
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:
cmd &var.(cmd(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,srtN>])).[desc.]
where:
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.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding sort field.
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.
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.
cmd &var.(AND(<dsply1[,val1>],<dsply2[,val2>],...<dsplyN[,srtN>])).[desc.]
where:
Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, and ADD.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Is the connector that creates a multiselect list.
Are the entries that display on the Autoprompt form. Selecting an entry automatically selects the corresponding sort field.
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.
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.
The following request against the GGSALES data source enables the user to select one or more of the following when running the procedure:
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:
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.
The following examples create three (3) data sources to be used in the examples that create dynamic field lists:
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
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
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
cmd &var.(FIND return_fieldname [,display_fieldname] IN datasource).[description.]
where:
Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, ADD, BY, and ACROSS.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Is the name of the field containing the possible variable values that are returned to the FOCEXEC.
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.
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.
Is an optional description of the variable.
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:
cmd &var.(cmd(FIND return_fieldname [,display_fieldname] IN datasource)).[description.]
where:
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.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Is the name of the field containing the possible variable values that are returned to the FOCEXEC.
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.
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.
Is an optional description of the variable.
cmd &var.(AND(FIND return_fieldname [,display_fieldname] IN datasource)).[description.]
where:
Is the command to which the list of fields will apply. Valid values are PRINT, COUNT, SUM, WRITE, and ADD.
Is the variable, including the ampersand (&), for which you are supplying a list of field values.
Is the connector that creates a multiselect list.
Is the name of the field containing the possible variable values that are returned to the FOCEXEC.
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.
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.
Is an optional description of the variable.
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 |