Creating Parameters

How to:

If your drill-down report depends on a specific data value in the base report, you must create a parameter (or parameters) that can pass one or more values to the report you are drilling down to.

Parameters are useful when you want to create a dynamic link. For example, your first report is a summary report that lists the total number of products ordered by a company on a specific date. You can drill down from a specific product in that report to a more detailed report that shows the name of the product's vendor and the individual number of units ordered by order number. With a dynamic link, you create only one drill-down report that uses the value passed from the first report to determine what information to display, instead of several static reports.

You can create multiple parameters. The entire string of parameters must be enclosed in parentheses, separated from each other by a blank space, and cannot exceed 2400 characters.

You can use any combination of the following methods to create parameters in your StyleSheet declaration. You can specify:

Syntax: How to Create Parameters

parameter=value

where:

parameter
Is the name of the variable in the linked procedure.

Note: To avoid conflicts, do not name variables beginning with Date, IBI, or WF. Variable names beginning with these values are reserved for internal use.

value
Identifies the value to be passed. Values can be any of the following:

'constant_value' identifies an actual value to be passed. The value must be enclosed in single quotation marks.

field identifies the field in the report whose value is to be passed to the procedure. You can identify the field using either the field name or the field position. For details on field position, see Identifying a Report Component in a WebFOCUS StyleSheet.

'&variable' identifies an amper variable whose value is to be passed to the procedure. The name of the amper variable must be enclosed in single quotation marks. You can use amper variables only in inline StyleSheets.

Note: The usual use of an amper variable is to pass a constant value. If the amper variable corresponds to an alphanumeric field, the amper variable would have to be embedded in single quotation marks, for example:

'&ABC'.

The entire string of parameter names and values must be enclosed in parentheses. Each parameter=value pair must be separated by a blank space. You can include multiple parameters in your request but the entire string cannot exceed 2400 characters.

Note: If the drill-down report contains a -DEFAULTS statement that sets a default value to the same amper variable passed from the main report, the amper variable value passed down overwrites the -DEFAULTS statement in the target procedure.

Example: Creating Parameters by Specifying a Constant Value

The following example illustrates how to create parameters by specifying a constant value. The relevant StyleSheet declarations are highlighted in the request.

Main report:

SET LOOKGRAPH BAR
SET 3D=OFF
GRAPH FILE SHORT
HEADING
"Sum of Balance Across Short Date"
"Click Any Bar For a Report on Projected Returns Since June 29, 1998 "
SUM BALANCE
ACROSS SHORT_DATE
ON GRAPH SET STYLE * 
TYPE=DATA, ACROSSCOLUMN=N1,FOCEXEC=PROJRET(Short_Date='06291998'),$ 
ENDSTYLE
END

Drill-down report (PROJRET):

TABLE FILE SHORT
HEADING
"Projected Returns Since June 29, 1998 "
SUM PROJECTED_RETURN
BY SHORT_DATE
BY REGION
WHERE SHORT_DATE GE '&Short_Date';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The output for the main report is:

When you click a bar the output is:

Projected Returns Since June 29, 1998
Date of
 Projected
Statement
Region
Annualized
Return
06/29/1998
CENTRAL AMERICA
     1.360
EASTERN EUROPE
     2.300
FAR EAST
     1.300
MIDDLE EAST
     1.140
NORTH AMERICA
     1.780
SOUTH AMERICA
     1.200
WESTERN EUROPE
     1.140
06/30/1998
CENTRAL AMERICA
     1.360
EASTERN EUROPE
     2.350
FAR EAST
     1.300
MIDDLE EAST
     1.140
NORTH AMERICA
     1.780
SOUTH AMERICA
     1.200
WESTERN EUROPE
     1.140
07/01/1998
CENTRAL AMERICA
     1.360
EASTERN EUROPE
     2.300
FAR EAST
     1.300
MIDDLE 
EAST
     1.140
NORTH AMERICA
     1.780
SOUTH AMERICA
     1.200
WESTERN EUROPE
     1.140

Example: Creating Parameters By Specifying a Field

The following example illustrates how to create a parameter by specifying a field, in this case CATEGORY. The SALES drill-down report (the report that is linked to the main report) sets the CATEGORY field equal to &TYPE. In the base report, TYPE is set to equal the field CATEGORY.

When you run the report, the values for the field CATEGORY (Coffee, Food, Gifts) are linked to a report that contains the product and regional breakdowns for the respective value.

Main report:

TABLE FILE GGSALES
SUM UNITS DOLLARS
BY CATEGORY
HEADING 
"* Click category to see product and regional breakdowns."
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $ 
TYPE=DATA, COLUMN=CATEGORY, FOCEXEC=SALES (TYPE=CATEGORY), $ 
ENDSTYLE
FOOTING
"This report was created on &DATE ."
END

Drill-down report (SALES):

TABLE FILE GGSALES
ON TABLE SET PAGE-NUM OFF
SUM UNITS DOLLARS
BY CATEGORY BY PRODUCT
ACROSS REGION
WHERE CATEGORY = '&TYPE';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The output for the main report is:

Click Coffee and the product and regional breakdown for Coffee displays:

Example: Creating Parameters by Specifying an Amper Variable

The following request illustrates how to create a parameter by specifying an amper variable. The relevant StyleSheet declarations are highlighted in the request.

Main report:

SET3D=OFF
GRAPH FILE EMPLOYEE
HEADING
"Salary Report Per Employee ID"
"Click A Bar For The List of Employees in the '&DEPARTMENT' Department"
SUM SALARY
ACROSS EMP_ID AS 'EMPLOYEE ID'
ON GRAPH SET STYLE * 
TYPE=DATA, ACROSSCOLUMN=SALARY,
FOCEXEC=EMPBYDEP(DEPARTMENT='&DEPARTMENT'), $ 
ENDSTYLE
END

Linked report (EMPBYDEP):

TABLE FILE EMPLOYEE
HEADING
"List Of Employees in the '&DEPARTMENT' Department "
PRINT FIRST_NAME LAST_NAME
BY DEPARTMENT
WHERE DEPARTMENT EQ '&DEPARTMENT';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

When the main report request is run, the following prompt opens:

Enter MIS and click Submit. The output is:

When you click a bar on the graph, the output is:

List Of Employees in the 'MIS' Department
DEPARTMENT
FIRST NAME
LAST NAME
MIS
MARY
  SMITH
DIANE
  JONES
JOHN
  MCCOY
ROSEMARIE
  BLACKWOOD
MARY
  GREENSPAN
BARBARA
  CROSS

Example: Using DRILLMETHOD in a Drill-Down Request

The following example illustrates how to use the DRILLMETHOD parameter to control the method used in a drill-down request.

  • When DRILLMETHOD is set to POST, parameters and values are not included in the URL or stored in the logs, which makes this method more secure.
  • When DRILLMETHOD is set to GET, parameters and values are included in the URL and stored in the logs.

The following drill-down request against the GGSALES data source includes a SET DRILLMETHOD=POST command and two parameters, one for CATEGORY and one for PRODUCT.

SET DRILLMETHOD=POST
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY CATEGORY
BY PRODUCT
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE * 
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=DATA, COLUMN=N1, TARGET='_blank', FOCEXEC=IBFS:/WFC/Repository/My_Workspace/~admin/child_report.fex(PARA1=CATEGORY PARA2=PRODUCT), $ 
ENDSTYLE 
END
-RUN

Note: You can also use DRILLMETHOD in a StyleSheet command for each drill down, for example:

TYPE=DATA, COLUMN=N1, DRILLMETHOD='POST', FOCEXEC=drilldown.fex, $

The following request is the child report.

TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
BY CATEGORY
BY PRODUCT
WHERE CATEGORY EQ &PARA1.QUOTEDSTRING AND PRODUCT EQ &PARA2.QUOTEDSTRING; 
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ 
ENDSTYLE
END

When you run the drill-down request with DRILLMETHOD=POST and select a category, for example, Coffee, the parameters and values are not included in the URL, as shown in the following image.

If you run the drill-down request with DRILLMETHOD=GET, and select a category, for example, Coffee, the parameters and values are included in the URL, as shown in the following image.

Note: For more information about the SET DRILLMETHOD command, see Customizing Your Environment in the TIBCO WebFOCUS® Developing Reporting Applications manual.

Example: Using Multiple Parameters

When using multiple parameters, the entire string must be enclosed in parentheses and separated from each other by a blank space. The relevant StyleSheet declarations are highlighted in the request.

Main report:

SET 3D=OFF
GRAPH FILE EMPLOYEE
SUM CURR_SAL
ACROSS DEPARTMENT
ON GRAPH SET STYLE * 
TYPE=DATA, ACROSSCOLUMN=CURR_SAL,
FOCEXEC=REPORT2 (DEPARTMENT='&DEPARTMENT' LAST_NAME='SMITH'), $ 
ENDSTYLE
END

Drill-down report (REPORT2):

TABLE FILE EMPLOYEE
PRINT SALARY
BY DEPARTMENT
BY FIRST_NAME
BY LAST_NAME
WHERE DEPARTMENT EQ '&DEPARTMENT'
WHERE LAST_NAME EQ '&LAST_NAME'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

When the main report request is run, the following prompt opens:

Enter MIS and click Submit. The output is:

When you click the MIS bar, the output is:

DEPARTMENT
FIRST NAME
LAST NAME
SALARY
MIS
MARY
SMITH
$13,200.00