Definition of relational list hyperblocks

Hyperblocks are specialized for OLAP. This is how you make the best use of relational lists in hyperblocks.

When you create a hyperblock, these cells are displayed:

  • An output cell with the Unique name as a value and a Caption as a display value.

    For OLAP lists, the output cell returns elements' unique names as the values and captions as the display values. You can use formulas that query OLAP data, such as CELLGET or ROA, to reference the unique name value. The display value is displayed when you apply the number format to a cell.

    For relational lists, unique names and captions are not available.In a hyperblock, the first column in the query result set serves as the value and the display value.

  • A value cell. When you use OLAP lists, you can specify a CELLGET or similar data‑retrieving formula in the value cell. When you use relational data, you can use an RDB or a similar data-retrieval formula. Create additional output cells as the preferred approach.

Showing columns of a relational query result

  1. Open the report in Design mode.
  2. Select Insert > Hyperblock or click Hyperblock in the Objects toolbar.
  3. In the Create Hyperblock dialog box, select a relational list.
  4. In the List Type section, select Local list or Global list as a scope for the list and click OK.
  5. In the List Designer pane, expand the Input section.
  6. In the Formula specify a SELECT statement
    For example, select these columns: 
    =" SELECT ProductName, QuantityPerUnit , UnitPrice , Discontinued FROM
		Products "
  7. Resize the hyperblock to accommodate additional output cells.
  8. In the Accessories pane, expand the hyperblock.
  9. Drag required columns into the cells of the hyperblock. The hyperblock shows additional gray output cells.
    Note: The hyperblock doesn't show column names but only values. When the query is fixed, specify column names in the cells above the corresponding output cells.
  10. Right‑click the hb_ hyperblock and select Format Hyperblock.
  11. In the Format Hyperblock dialog box, click the Output Cells tab.
  12. In the Properties section, clear these check-boxes for all output cells:
    • Allow expand collapse
    • Begin expansion at level
    • Indent labels

Creating a hyperblock with a dynamic list of columns

  1. Open the report in Design mode.
  2. Select Insert > Hyperblock or click Hyperblock in the Objects toolbar.
  3. In the Create Hyperblock dialog box, select the relational list.
  4. In the List Type section, select Local list or Global list as a scope for the list and click OK.
  5. In the List Designer pane, expand the Input section.
  6. In the Formula field specify a SELECT statement
    For example, select these columns: 
    =" SELECT ProductName, QuantityPerUnit , UnitPrice , Discontinued FROM
		Products "
  7. Right‑click the hyperblock and select Format Hyperblock.
  8. In the Format Hyperblock dialog box, specify hb_relational as a name.
  9. Drag and drop the Formula Lists folder from the Accessories pane into the report, so that it creates a new hyperblock that intersects with the first hyperblock.
  10. In the List Designer pane, expand the Input section.
  11. In the Formula field, specify =ReportObjects.hb_relational.Columns.
  12. In the Type section, specify XML.
  13. Right‑click the hyperblock and select Format Hyperblock.
  14. In the Format Hyperblock dialog box, specify hb_columns as a name.
  15. Intersect the two hyperblocks in this way:
    • Use the hb_columns as horizontal hyperblock that returns columns.
    • Use the hb_relational as vertical hyperblock. The output cell of hb_relational hyperblock is in the intersection of the two hyperblocks.
  16. Right-click the hb_relational hyperblock and select Format Hyperblock.
  17. In the Format Hyperblock dialog box, click the Output Cells tab.
  18. In the Value field of the output cell, specify =ReportObjects.hb_columns.Text.
  19. In the Display Value section, select Same as Value.
  20. In the Properties section, clear these check-boxes:
    • Allow expand collapse
    • Begin expansion at level
    • Indent labels

Using the properties of relational list hyperblocks

Use standard worksheet expressions to reference relational-list fields and attributes in a relational list of a hyperblock or combo box.

For example, you can use these standard worksheet expressions:

  • =HyperblockN.Text: Returns the first column that is provided by the relational list, for example, ProductNames.
  • =HyperblockN.Attribute2: Returns the third column that is provided by the relational list, for example, UnitPrice.
  • =HyperblockN.Columns: In a formula list, returns a list of columns that are provided by the relational list.
Related topics