Creating HOLD Files

Topics:

A HOLD file is the output of a report request stored in a file that you can use as input to another WebFOCUS procedure.

You can then create new report requests that extract data from the HOLD file, resulting in multi-step report.

HOLD files can be created to use in a report, chart, document, or visualization.

Valuable Applications of HOLD Files

A HOLD file is valuable when you want to do the following:

  • Extract fields from a large data source for faster and more efficient retrieval in subsequent requests.
  • Store virtual field values or summary values calculated in one request for further processing in another request.

Storing HOLD Files

HOLD files can be created for immediate use and saved temporarily or they can be stored for future and repeated use.

Output Formats for Reports

You can save a HOLD file for a report in the following formats:

  • Binary (*.ftm)
  • FOCUS (*.foc). For more information, see FOCUS Format Index Fields.
  • Comma Delimited with Titles (*.csv)
  • Plain Text (*.ftm)
  • Tab Delimited (*.tab)
  • Tab Delimited with Titles (*.tab)
  • Database Table (*.sql)

    Note: The Database Table output is only available when working against an SQL database.

  • SQL Script (*.sql)
  • Hyperstage (*.bht)

    Note: The Hyperstage output is only available when the reporting server has a Hyperstage adapter configuration.

  • XML (*.xml)
  • Visual Discovery AE (*.txt)

    Note: The Visual Discovery AE output option is only available with a Visual Discovery Analyst Edition license.

Creating Hold Files

Topics:

How to:

This section contains examples of how you would use a HOLD file.

Note:
  • Across fields are not allowed in HOLD files.
  • Using the Change Title option in the Query pane, you can change the title of a field prior to creating a HOLD file. A blank space in the title will be replaced by an underscore. This functionality enables you to control the names used for the fields included in the HOLD file, and makes it easier to find the fields when creating report.
  • When creating a HOLD file, the Temporary dialog box displays only those reporting server applications to which you have access.
  • When a report contains a HOLD procedure, dots or periods (.) in the AS name of the Define field are automatically converted to an underscore (_).
  • You can use Auto Linking when working with HOLD files. For more information, see Using the Auto Linking Feature to Link Content.

Procedure: How to Create a Tabular Report From a HOLD File

To create a tabular report from a HOLD file, begin by creating a report.

  1. In the Open dialog box, select the wf_retail_lite Master File.
  2. Add the following measure fields to the report:
    • Cost of Goods
    • Discount
    • Gross Profit
    • Quantity,Sold
    • Revenue
  3. Add the following dimension fields to the report:
    • Product Category
    • Product,SubCategory
    • Sale,Year
  4. On the Home tab, in the Format group, click File.

    The Temporary dialog box opens.

  5. In the Temporary dialog box, name the HOLD file, keep the default file type, Binary (*.ftm), and click Save.
  6. At the bottom of the canvas, click Create Report.

    The custom database structure displays in the Resources panel. The canvas is returned to a default blank state, enabling the development of a new report using the HOLD file.

  7. From the HOLD file, drag Quantity,Sold to the canvas.
  8. On the canvas, select the Quantity,Sold column heading.
  9. On the Field tab, in the Display group, click Aggregation and then click First Value.

    Note: The heading changes to FST Quantity Sold.

  10. Select the FST Quantity Sold heading.
  11. On the Field tab, in the Display group, click Hide Field to hide Quantity,Sold, as it will be used in a subsequent calculation.
  12. On the Data tab, in the Calculation group, click Summary (Compute).
  13. In the Summary Field (COMPUTE) dialog box, do the following:
    • In the Format field, type D8.2%.
    • Double-click the Quantity,Sold field to add it to the formula box.
    • Add / 100 after the Quantity Sold field to calculate the percentage.
  14. Click OK to close the dialog box.
  15. Drag Product,Category to the By Query field container.
  16. In the Query pane, select the Product,Category field.
  17. On the Field tab, in the Break group, select Subtotal to create Subtotals on Product,Category.
  18. Drag Product, Subcategory to the By Query field container.
  19. Drag Sale,Year to Across.
  20. On the Home tab, in the Report group, click Row Totals.

    The final report displays.

Procedure: How to Rearrange HOLD File Components

The following procedure describes how to rearrange file components in a HOLD file.

Note: This procedure creates a binary HOLD file and a subquery to illustrate how to rearrange HOLD files. It also shows the result of this rearrangement.

  1. Create a new document, using the wf_retail_lite Master File for the data source.
  2. On the Insert tab, in the Reports group, click Report. Populate the report with the following fields from the Data pane:
    • Gross Profit
    • Quantity,Sold
    • Revenue
    • Product,Category
    • Product,Subcategory
  3. On the Home tab, in the Format group, click File to create a HOLD file.

    In the Temporary dialog box, enter a name for the file. For example, File1_binary.

  4. Click Save.
  5. Create a report using the HOLD file, specifying Product,Category, Product,Subcategory, and Quantity,Sold.
  6. Next, using the following steps, add a subquery SQL script for use as a filter on the first report.
    1. On the Data tab, in the Data Source group, click Switch.

      Select the original master file (wf_retail_lite.mas).

    2. Locate and double-click the Product,Category dimension field.

      This creates a second report, which you can drag and resize as needed on the Document canvas.

    3. Create a filter on Product,Category, where the product category is equal to Televisions.
  7. With the new component selected, click the Home tab and in the Format group, click File.

    In the File name field, enter File2_subquery and select the SQL Script (*.sql) format from the file types menu.

  8. Click Save.
  9. Rearrange the order of the HOLD files so that the File2_subquery is positioned above the File1_binary HOLD file using the following steps:
    1. Right-click Files in the Query pane and click Arrange Files, as shown in the following image.

      The Arrange Files dialog box opens.

    2. Using the Arrange Files dialog box that displays, select File2_subquery and click Move Up to move the file above File1_binary.
    3. Click OK.
  10. Edit the first report and create a filter using the subquery.
  11. Click OK to exit the Create a filtering condition dialog box.

    Your report is refreshed to reflect the filtering you have applied.

FOCUS Format Index Fields

FOCUS is the only format that supports an index field. The maximum number of fields to index is four. If the file format is FOCUS, then Index appears on the Query pane.

Creating a Subquery Filter Using a HOLD File

How to:

You can create a subquery using a HOLD file. A subquery is a nested query that is added to the Where clause of an SQL statement. A subquery is valuable because it is highly reusable.

Procedure: How to Create a Subquery Filter Using a HOLD File

This procedure describes how to create a subquery filter using a HOLD file created in the previous procedure.

  1. Build a report.
  2. On the Data tab, in the Filter group, click Filter.

    The Filter dialog box opens.

  3. In the Filter dialog box, from the Type drop-down menu, select Subquery as the filter type for the left-most part of the expression.
  4. From the Subquery drop-down menu, select In list as the comparison operator.
  5. From the list of subqueries, select the subquery that was created (in this example, File1) for the right-most part of the expression.
  6. Click OK.

    The report is filtered by the subquery that you created.

    Note: To view the SQL statements generated by the request, go to the Quick Access Toolbar, open the Run drop-down menu, and select SQL Trace.

WebFOCUS

Feedback