Insert rows from a Lookup visual

How to use Inforiver Lookup visuals to fetch row categories and hierachies from semantic models. Insert custom rows based on the fetched categories.

What is a Lookup visual?

The Inforiver Lookup visual is the best way to source data from other tables in your semantic models. You can seamlessly integrate row categories and hierarchies from different semantic models into your reports. The Lookup visual can connect to dimensions in the semantic model and retrieve categories. The procedure is similar to sourcing dropdown values from a lookup visual.

Step-by-step guide to importing data from semantic models with Lookup visuals

You can fetch the row categories from a semantic model via the Lookup visual. In the Insert Rows window, you will be able to see the data from the table in your Semantic model as dropdown options. Let's see how.

STEP 1: Import the Inforiver Lookup visual into your report. Assign the dimension categories that you will use to source row categories. In this example, we'll insert rows from the Category and SubCategory dimensions.

Assigning dimensions to fetch categories from a lookup visual

The row dimensions in our report are Product Line and Product SubLine.

Row dimensions used in the report

STEP 2: We'll use the Lookup visual to source row categories for the Product Line dimension in the report. To pick and upload row categories from the Lookup visual, navigate to Insert > Manage Rows > Insert Row Settings > Create Row Configuration > Manage. Select Options list from Lookup Visual from the Product Line dropdown.

Select the Options list from Lookup Visual option

STEP 3: The Add options from Lookup Visual window opens. Select the required visual ID from the dropdown. In this case, the Category dimension from the Lookup will be used to populate rows under the Product Line row dimension in the report. Since Category maps to Product Line, we select the Visual Name for the Category dimension.

Select the visual name for the Category dimension to map it to the Product Line

STEP 4: Next, select the Column Label field – the row categories will be sourced from this field. If you have a primary key or ID field associated with the label field, you can assign the ID field to the Column ID. If the column labels are updated in the source, the ID field mapping ensures that the updated value will be reflected in your reports.

Note: If your lookup table doesn't have an ID field, use the same label field (in this example, Category) as Column ID.

Select the column to source row categories from

STEP 5: Let's ignore the Filter options for now and click Add. Notice how the Lookup visual details have been populated in the Product Line dropdown.

Lookup visual details populated in the dropdown

STEP 6: Repeat the same steps to map the Product SubLine to the Sub-Category dimension from the Lookup visual.

Map the Sub-Category dimension from the Lookup visual to the Product SubLine row dimension

The final configuration looks like this:

Lookup visual configuration for inserting rows

STEP 7: After we configure the insert row settings, navigate to Insert Row > Data Input > Create Row(s).

When we click on the Product Line textbox, the dimension categories from the Lookup visual are populated. Let's select 'Furniture'.

Select a product line

Similarly, we can select a SubLine category from the respective Lookup visual. Let's select 'Bookcases'.

Selecting a row category for the SubLine dimension

STEP 8: The Furniture > Bookcases hierarchy has been created. We can enter values for the Margin and Revenue measures.

Row categories from data model inserted

Enforcing data integrity with filters

In the product category–product subcategory hierarchy used in the previous section, only subcategories mapped to the selected category should appear when inserting rows.

In this image, notice how unrelated subcategories like 'Accessories', 'Appliances', 'Art', etc, show up under the 'Furniture' category. Ideally, only furniture-related products like 'Chairs', 'Tables' should be displayed.

Parent-child relationships not maintained when options are displayed

We can apply filters to preserve relationships within the data. In this example, we want to make sure that only the sublines under a particular product line are shown when we insert rows.

The Product Line mapping remains the same as demonstrated in the previous section:

Product line mapping

For the product subline, we've added a filter that maps the visual row dimension - Category - with the lookup field - Product Line.

Adding a filter for the parent category while configuring child categories

After configuring filters, notice how only the sub-categories related to a particular product category are displayed when we try to insert rows now:

Related categories are displayed after setting up filters

To set up filters on related dimension categories, like the example above, both dimensions should be available in the lookup visual.

Assign related dimensions to the same lookup visual