Creating a Drill Down Procedure

In this section:

How to:

The drill down feature enables you to add one or more layers of detail to a report by embedding procedures into the report. A drill down procedure can be:

After the base (summary) report appears in the browser, you can drill down to selected data or to a URL or script (or procedure). For example, if you run a report on the country and title of all cars imported into the United States, and you have embedded the appropriate procedures in the report and defined the correct parameters, you can drill down on:

You can also define conditions that determine when to run an embedded drill down procedure. For example, suppose you have embedded a procedure in the country column of the summary report, but you want your drill down report to appear only for England. You can define a condition that enables the embedded drill down only when country equals England.

Note: To apply a customized report style, you must do so before you define any drill down attributes or those attributes will be overwritten. For additional information on style sheets, see the Creating Reports With WebFOCUS Language manual.


Top of page

x
Procedure: How to Execute a Drill Down Procedure

When a report with drill down capability appears in the browser, the hyperlinked values are underlined.

To view a drill down procedure:

  1. Run the summary report.
  2. Move the cursor over the underlined hyperlinks.
  3. Click the hyperlink you wish to drill down on and click the left mouse button. The detail procedure is executed and the output appears in the browser.

Note: The browser can be programmed to open a new window for each drill down report. However, at present, if you are viewing an HTML report, the detailed report replaces the summary report in the browser. You can click the Back button on the toolbar to recall the summary report.


Top of page

x
Creating a Hyperlink in an HTML or PDF Report

From a hyperlink in an HTML or PDF report, you can access other reports, documents, or websites, or perform other actions. The browser supports two kinds of hyperlinks:

Note: You can also use coded Style Sheets for this task. For more information, see the Creating Reports With WebFOCUS Language manual.


Top of page

x
Attaching or Opening a Drill Down Procedure in Report Painter

How to:

To drill down to a more detailed level of information in a report, you attach a procedure to one or more columns or to another object area in a report, while you are in Report Painter. When you run the report, the values in the chosen column become hot spots that, when selected, run the underlying procedure or link to a specified URL.

You attach drill down (detail) procedures with the Drill Down tab in the Report Options dialog box. You can also open a detail procedure, for viewing or modification, from this tab. For more information about the Drill Down tab, see Field Properties Drill Down Tab.

For HTML reports, when defining a link from a report component to a report procedure or URL, you can specify that the results of the drill-down link display in a target frame on a web page.



x
Procedure: How to Attach a Detail Procedure to a Column or Other Object Area

To attach a detail procedure to a column or columns:

  1. Right-click a column (or columns) in Report Painter and click Options from the context menu. The Field Properties dialog box opens.
  2. Click the Drill Down tab.
  3. From the active object area drop-down list, select the column component (Column Title and Data, Column Data, or Column Title) from which you want to drill down.
  4. From the Drill Down Type drop-down list, click Execute Procedure.
  5. In the Procedure Name field, type the name of an existing detail procedure, or select a procedure from the drop-down list.
  6. Select a target frame. This is optional and is for HTML reports only.
  7. Click OK.

To attach a detail procedure to another object area (for example, a heading or footing):

  1. Highlight the object area, right-click and click Drill Down from the context menu. The Drill Down dialog box opens.
  2. Select the Execute Procedure option button as the drill down type.
  3. In the Procedure Name field, type the name of an existing detail procedure, or select a procedure from the drop-down list.
  4. Select a target frame. This is optional and is for HTML reports only.
  5. Click OK.


x
Procedure: How to Open a Detail Procedure
  1. Right-click a column in Report Painter and click Options from the context menu. The Field Properties dialog box opens.
  2. Click the Drill Down tab.
  3. From the Drill Down Type drop-down list, click Execute Procedure.
  4. In the Procedure Name field, type the name of an existing detail procedure, or select a procedure from the drop-down list.
  5. Click the Open button.
  6. Developer Studio asks if you want to save changes to the current summary procedure. Click Yes.

    The detail procedure opens in the Procedure window, where you can open a component for viewing or modification in a new instance of Report Painter. When you are finished, you return to the summary procedure in the previous instance of Report Painter.


Top of page

x
Creating a Parameter

How to:

Reference:

Parameters allow you to specify criteria and conditions for the drill down report. By defining parameters, you can control the amount and type of information to retrieve for the drill down report.

For example, you can create parameters in the drill down report as amper (&) variables that prompt you for a value at run time. When the main report is run, you supply a value for the parameter using the drill down feature.

Note: To avoid conflicts, do not name variables beginning with Date, IBI, or WF, as variables beginning with these values are reserved for Information Builders use.



x
Procedure: How to Create a Drill Down Parameter

From the drill down report (the report that provides the detail information):

  1. Click the Where button from the Where/If drop-down menu.

    The Expression Builder opens.

  2. Create an expression that compares the parameter against a report field that appears in both reports.
  3. Click OK.

Note: If the drill down report contains a -DEFAULT statement that sets a default value to the same variable passed from the main report, the variable value in the -DEFAULT statement overwrites the variable value passed from the main report. Therefore, the output in the drill down report reflects the default value passed from the -DEFAULT statement. It does not reflect the value passed from the main report.



x
Procedure: How to Pass a Parameter From the Main Report

To add the ability to call the drill-down report and pass parameters, do the following from the main report:

  1. In the Report Painter window, right-click the object or objects that you want to drill down on and click Options from the context menu.

    The Field Properties dialog box opens.

  2. Click the Drill Down tab.
  3. Click Execute Procedure from the Drill Down Type drop-down list, and select the appropriate procedure from the Procedure Name drop-down list. For more information, see Attaching or Opening a Drill Down Procedure in Report Painter.
  4. Click the Add button in the With Parameters area.

    The Drill Down Parameter dialog box opens.

  5. Select the name of the parameter you created in the drill down report from the Parameter name drop-down list.

    Note:

    • When passing parameters to a drill down procedure, you must use local variables (&variables). Global variables (&&variables) cannot be used as drill down parameters.
    • The Drill Down Parameter dialog box displays all parameters that are unresolved, are set to a value, or have a default, as shown in the following image.

    When you pass the parameter to the drill down report, you must set a value for it in the Drill Down Parameter dialog box:

    • Field picks up the value of the selected field from the point at which the user clicks the hyperlink. Click the arrow and select a report field from the list.
    • Constant value sets the parameter to a specified value. Type a value in the entry field.
    • Variable enables you to enter the name of a Dialogue Manager variable. The variable will automatically be postfixed with the .QUOTEDSTRING operator. This allows the variable to contain both single and multi-select values.

    Note: If the drill down report contains a -DEFAULT statement that sets a default value to the same variable passed from the main report, the variable value in the -DEFAULT statement will overwrite the variable value passed from the main report. Therefore, the output in the drill down report will reflect the default value passed from the -DEFAULT statement and will not reflect the value passed from the main report.

  6. After a value has been supplied, click OK to return to the Drill Down tab on the Field Properties dialog box.

    The parameter is added to the With Parameters list box.



x
Reference: Drill Down Parameter Dialog Box

You use the Drill Down Parameter dialog box to supply parameters that the drill down report or Servlet script might require, as shown in the following image.

Parameter name

Use the drop-down list to select the name of the parameter that the linked FOCUS procedure or linked Servlet script expects.

Note: The Drill Down Parameter dialog box displays all parameters that are unresolved, are set to a value, or have a default.

Parameter value

Assign a value to the parameter:

Field picks up the value of the selected field from the point at which the user clicks the hyperlink. Click the arrow and select a report field from the list.

Constant value sets the parameter to a specified value. Type a value in the entry field.

Variable enables you to enter the name of a Dialogue Manager variable. The variable will automatically be postfixed with the .QUOTEDSTRING operator. This allows the variable to contain both single-select and multi-select values.

Note: If the drill down report contains a -DEFAULT statement that sets a default value to the same variable passed from the main report, the variable value in the -DEFAULT statement overwrites the variable value passed from the main report. Therefore, the output in the drill down report reflects the default value passed from the -DEFAULT statement. It does not reflect the value passed from the main report.


Top of page

x
Sample Drill Down Procedures

The following examples illustrate how to drill down to a detail report by passing parameters and how to hyperlink to a URL by passing parameters.



Example: Creating Drill Down Reports

Suppose you want to create a summary report that allows for drill down to details from the summary values. The following example shows how to create a main report of total sales and sales quotas for all Gotham Grinds stores by state. The State column in the main report will provide hyperlinks to a detail report of sales and quota figures.

Part I: Create the detail report (the report to drill down to)

  1. Open the GGSALES Master File in Report Painter.
  2. Identify and format the columns for the detail report.
    1. Double-click or drag ST, DOLLARS, and BUDDOLLARS from the Fields tab of the Object Inspector into Report Painter.
    2. Click State and click By on the Report Painter toolbar.
    3. Format the Dollar Sales and Budget Dollars columns. Right-click Dollar Sales in the Report Painter window and select Format. Click Decimal in the Format Types box. Click Floating dollar -- M from the Edit Options list. Click OK. Repeat the same steps for Budget Dollars.
  3. Create a parameter:
    1. Click the Where button from the Where/If drop-down menu.
    2. Double-click ST from the Data section.
    3. Click equals from the Logical Relation column.
    4. Click Parameter from the Compare Type drop-down list.
    5. Double-click the Compare Value field. The Variable Editor opens.
    6. Enter PARAMETER in the Name input field.
    7. Click OK. Your new expression is added to the Expression Builder.
    8. Click OK to return to the Report Painter window.
  4. Close Report Painter and save the detail report.

Part II: Create the main report and link it to the detail report

  1. Open the GGSALES data source in Report Painter.
  2. Identify and format the summary columns for the main report.
    1. Click By on the Report Painter toolbar and double-click or drag ST from the Fields tab of the Object Inspector into Report Painter.
    2. Move the insertion point after the State field and click Sum on the Report Painter toolbar. Double-click or drag DOLLARS and BUDDOLLARS from the Fields tab of the Object Inspector.
    3. Format the Dollar Sales and Budget Dollars columns. Right-click Dollar Sales in the Report Painter window and click Format. Click Decimal in the Format Types box. Click Floating dollar -- M from the Edit Options list. Click OK. Repeat the same steps for Budget Dollars.
  3. Link the detail report to the main report:
    1. Right-click State in the Report Painter window and click Options from the context menu to open the Field Properties dialog box.
    2. Click the Drill Down tab.
    3. Click Execute Procedure from the Drill Down Type drop-down list.
    4. Select your detail report from the Procedure Name drop-down list.
    5. Click Add to open the Drill Down Parameter dialog box.
    6. Type PARAMETER in the Parameter name entry field. This passes a parameter to the detail report from the main report.

      Note: By default, the Field option button is selected and the field ST appears in the drop down list box located in the Parameter value box.

    7. Click OK. Note that the parameter, &PARAMETER = ST, appears in the With Parameters list box. This means the value of state that the user drills down on will be passed to the drill down procedure in the variable &PARAMETER.
    8. Click OK to return to Report Painter.
  4. Run the report by clicking the Run button on the toolbar. The main report appears in the browser, as shown in the following image.

  5. To run the detail report, place your cursor over CA in the State column to reveal the drill down icon (a hand). Click CA.

    The detail report lists sales and quota figures for California, as shown in the following image.



Example: Creating a URL Hyperlink With Parameters

The following example shows how to link a report that lists the name of each company employee to a Servlet that looks up his or her phone number and displays it in your browser.

  1. Open the EMPLOYEE data source in Report Painter.
  2. Double-click or drag LAST_NAME and FIRST_NAME from the Fields tab of the Object Inspector.
  3. Add a report footing by typing the following:

    Click on a last name to look up a phone number

  4. Right-click the field LAST_NAME in the Report Painter window and click Options.
  5. Click the Drill Down tab on the Field Properties dialog box.
  6. Click URL from the Drill Down Type drop-down list.
  7. Enter the following URL of the Servlet, that provides the phone numbers:

    http://LOCALHOST:8080/ibi_Apps/WFServlet/corphone.exe?

    Note: You must include a question mark (?) at the end of the URL when you are executing Servlets that require parameters.

  8. Click the Add button.

    The Drill Down Parameter dialog box appears.

  9. Enter the name of the parameter that the Servlet, expects and supply a value, which in this case, is the field LAST_NAME.
  10. Click OK to return to the Drill Down tab in the Field Properties dialog box.
  11. Click OK to return to the Report Painter window.
  12. Run the report by clicking the Run button on the toolbar.

    The report appears in the browser, as shown in the following image.


Top of page

x
Drilling Down to a Maintain Procedure

How to:

Reference:

You can attach a Maintain procedure to a field or fields in a report from the Drill Down dialog box.

Drilling down to a Maintain procedure is only supported when the procedure is executed from a web browser, such as Netscape® or Internet Explorer.

 

 



x
Procedure: How to Drill Down to a Maintain Procedure

To access the Drill Down dialog box:

  1. Right-click the fields in the Report Painter window and click Options from the context menu.

    The Field Properties dialog box opens.

  2. Click the Drill Down tab.
  3. Click Maintain Procedure from the Drill Down Type drop-down list.

    or

    If the procedure is compiled, select Maintain Procedure Compiled from the check box. (Consult your Maintain developer to determine if your procedure is compiled.)

  4. Type the name of the drill down procedure in the Procedure name field.
  5. Click OK.


x
Reference: Drilling Down to a Maintain Procedure on a Secured Maintain Server

If the Maintain procedure you are drilling down to is on a secured Maintain Server, in order to pass data from the report to the Maintain procedure you need to define additional parameters for name and password. For details on creating drill down parameters, see How to Create a Drill Down Parameter.

For security concerns, it is suggested that a fictitious name and password is used because the entire path to the CGI (including name and password) is disclosed in your browsers status window.

The following is a list of parameters that must be defined when drilling down to a Maintain Procedure on a secured Maintain Server.

Parameter

Value

IBIC_user

Represents the user name and in most cases it is case-sensitive.

IBIC_pass

Represents the user password and in most cases it is case-sensitive.

For related information, see Maintain Syntax for CGI Coding.



x
Reference: Drilling Down to a Maintain Procedure in a Multi-Server Environment

If you are drilling down to a Maintain procedure on a specific server, in order to pass data from the report to the Maintain procedure you must define additional parameters for the server and outbound communications. For details on creating drill down parameters, see How to Create a Drill Down Parameter.

The following is a list of parameters that must be defined when drilling down to a specific Maintain Server.

Parameter

Value

IBIC_server

Represents the node name in the ODIN.CFG.

IBIS_outbound

Represents whether server communication is turned on or off. This value should be set to on.

For related information, see Maintain Syntax for CGI Coding.



x
Reference: Maintain Syntax for CGI Coding

The Maintain Development Environment (MDE) provides design tools that encapsulate the necessary CGI code for many of its FORM operations. It does not usually require additional CGI programming by developers.

For example, Maintain FORMS uses the CGI to output HTML to the client. In turn, the client uses the CGI to send the response back to Maintain.

For development situations that go beyond the normal use, CGI parameter data can be retrieved implicitly from within a Maintain Procedure.

For syntax illustrations, see Using Drill Downs in Maintain Procedures.



Example: Using Drill Downs in Maintain Procedures

The following example retrieves CGI Parameter data within a Maintain Procedure:

MAINTAIN
MODULE IMPORT (WEBBASE2 ERRORS);
Case Top
Declare FoundKeyName/i1 = IWC.FindAppCgiValue (KeyName, KeyValue);
EndCase
END
$$KeyName   Name of CGI Parameter
$$KeyValue Maintain Variable to receive Parameter Data
$$FoundKey Name   0 = Not Found / 1 = Found

The following example calls Maintain using the URL:

http://www/cgi-ibi/ibi_cgi/ibiweb.exe?IBIF_cmd=MNTCON%20EX%20cgivars&
IBIS_passthru=on

The following example calls Maintain using HTML forms:

<HTML>
<BODY onLoad='document.form.submit();'>
<FORM NAME='form' ACTION='/ibi_cgi/ibiweb.exe' METHOD='POST'>
<input TYPE='hidden' name='IBIF_cmd' value='MNTCON EX name_of_fex'>
<...>
</BODY>

Top of page

x
Creating a Drill Through Report

How to:

Drill Through provides a way to easily relate the data in separate reports within a PDF formatted document. In the Document Composer you can internally link reports using the Drill Through Destination property of reports that were created with the Drill Through property enabled in Report Painter.

For more information on how to create a PDF document with multiple reports that can be navigated using Drill Through, see the Creating Compound Reports With Document Composer manual.



x
Procedure: How to Create a Drill Through Enabled Report
  1. Right-click a column in Report Painter and select Options from the context menu. The Field Properties dialog box opens.
  2. Click the Drill Down tab.
  3. From the Drill Down Type drop-down list, select Drill Through, as shown in the following image.

  4. From the Drill Through Direction radio buttons, select the direction of the drill through (Down or First).

    Down is used to link to the following report, and First is used to link to the first report in the drill-through sequence.

  5. Click the Link Fields button.

    The Link Fields dialog box appears, as shown in the following image.

    Note: For Down, a Source column and a Target column are available. For First, only a Source column is available.

  6. Click the Add new item button to link the source and target fields (for Down) or just the source field (for First).

    These are the fields that will be linked between the target report and the source report. The first target and source field pair is the primary sort field for the reports.

  7. Click OK.

WebFOCUS