Creating dependent relational selections

In this example, you select a region from a multidimensional database. This populates a report variable. The report variable is referenced in a SQL Select statement in the Relational List Designer. If the region held in the report variable exists in the relational database table, data from the relational database is displayed.

The example uses the Region dimension of the Analysis cube of the Best Practices sample database, and the DimSalesTerritory table of the AdventureWorks sample database from Microsoft. Only some of the regions in the Region dimension are also in the DimSalesTerritory table.

  1. Drag the Region dimension to a cell to create a hyperblock.
  2. Create a report variable and name it, for example, rv_country.
  3. Right-click the key output cell of the hyperblock and select Define Action.
    See Actions.
  4. Select Set Parameters as the type and Hyperlink as the mode.
  5. In the Parameters section of the Define Action dialog, double-click <New Parameter> and select rv_country from the list of report variables.
  6. In the Value field, replace =TRUE with an ROA formula in this format:=ROA("Alias","Cube","[Dimension]","attribute_name",reference of cell which contains the action).
    For example, =ROA("Best Practices OLAP", "Analysis", "[Region]","Att1_name",C6).

    See ROA.

  7. Click OK.
  8. Right-click the relational database alias and select New > Relational List.
  9. Drag the relational list into the spreadsheet to create a hyperblock.
  10. Click the browse button in the relational hyperblock.
    The Object Properties dialog is displayed.
  11. Click List Designer to open the Relational List Designer.
  12. In the Relational List Designer, enter a SQL statement in this format:="select * from DimSalesTerritory where SalesTerritoryCountry='"&ReportVariables.rv_country.text&"'"
    SeeUsing variables in SQL queries.
  13. In View Mode, select a country from the list of regions that is also listed in the SalesTerritoryCountry field of the Relational database.
    The first row of the table is displayed.
  14. Create multiple output cells to display further fields from the table of the relational database.

    See Multiple output cells.

    Note: If you select a region that is not in the SalesTerritoryCountry field, no data from the relational database is displayed.
Parent topic: Creating dependent relational selections with a hyperblock