Insert calculated rows

The Inforiver Premium Table provides an option to insert calculated rows using an intuitive formula editor. The Excel-like formula engine supports 50+ functions (logical, boolean, math functions, and more). The formula editor provides syntax, examples, and features such as autocomplete, multi-line support, and more to help users create and troubleshoot formulas.

Insert Rows in Table

Refer to formula syntax for a detailed list of functions, operators, and identifiers that can be used for calculations.

Let us take this example, where we have sales data by category and subcategory. Let us add a new row above the 'Computer' category, which is calculated as the minimum of all of its subcategories.

Select the row above which we need to insert the new row. In the Insert tab, click on the Insert Row dropdown. Select the Calculated Row option.

Inserting a calculated row

A blank row gets inserted, and a side panel opens up as shown.

Create calculated row

1. Formula editor

1. Rename the title and click on the formula editor. You can see a list of available functions in the Functions tab.

Enter the title

1. As you start typing, Inforiver's IntelliSense narrows down the list of functions to match the entered text. Click on MIN.

Select function

1. The formula editor displays the syntax for the function. Click on the arrow highlighted.

Formula syntax

1. The Premium Table also displays a description of the function and a detailed explanation of the arguments with examples.

Function description and examples

1. To insert a reference to a particular row, click on the required cell references when the cursor is placed in the formula editor.

Referencing rows by selectingt them

1. The other way is to use the References tab. To access Desktops, you need to type Computers_Desktops. As you start typing 'Com..', the references get narrowed down. Select the required rows from the pop-up.

Inserting a reference from the dropdown

1. If no further changes are required, click on Create after entering your formula. The calculated row gets created as shown.

Calculated row is inserted

To view the underlying logic for calculated data, click on the result cell to preview the formula used, in the formula bar.

Note:

Any errors in the formula can be easily located with clear and concise error messages. In the maximized formula editor view, you will also notice line numbers, allowing you to quickly locate and fix errors.

Maximized formula editor

2. Manage Inserted Rows

The inserted row can be customized using a few options based on our needs. To access these options, go to Insert > Manage Rows and select the required row.

Manage Rows

Let's take a look at all of the options below:

3. Include in total

Select the Include in total checkbox to include the calculated row in the grand total. Click Update. The grand total is updated.

Including calculated row in total

4. Include in Charts

1. To include the calculated row when using row-level charts, check the Include in Charts checkbox. Click Update.

The 'Include in Charts' checkbox is enabled only when the 'Include in total' option is unchecked.

Include the row in charts

1. Now, let's add a bar chart to Actual Sales. Click on the column and then select the highlighted bar chart from the 'Home' tab.

Adding a chart

1. Note that the calculated row, 'Other Accessories,' is also visualized using a bar chart.

Bar chart shown for calculated row

1. When you uncheck the 'Include in Charts' checkbox and click on 'Update', you will see that the calculated row is no longer displayed with the chart.

Calculated row shown as number without chart

5. Evaluate column before rows

When inserting calculated rows, you may want the results of the calculated columns to take precedence over the calculated row results or vice versa. This behavior can be configured using the 'Evaluate column before rows' option.

Let's consider an example where we are inserting a calculated column for calculating the variance between the 'Actual Sales' and 'Forecast'.

Calculated Column

Let's insert a calculated row using the steps mentioned above:

Calculated Row

You can see that the calculated column value is empty for the calculated row.

When 'Evaluate columns before rows' is enabled, the variance between the configured columns is calculated, overriding the calculated row's result.

Evaluating columns before rows

6. Scaling factor

In some cases, the values in calculated rows may not match the number scaling specified at the report level. You can use a custom scaling factor depending on the nature of the calculation.

Scaling Factor

7. Bind for Cross filter/RLS

You can enable the Bind for Cross filter/RLS option – this ensures that cross-filter selections and RLS settings apply to calculated/manual input rows that reference other rows.

For example, if this option is not enabled, a manager in charge of a specific product (say, cell phones) will see all manually inserted rows referencing data from other products (such as audio, cameras, and so on) that were visually created.

Calculated row in Audio visible to other category person

To avoid this, you can bind inserted rows to a reference row or a dimension category by selecting the appropriate one.

• Binding to a reference row

To select a particular reference row, choose Row from the SelectionType dropdown and choose the required row. Click on Update.

Binding to a reference row

• Binding to a Dimension category

To bind the inserted row to a particular dimension category, choose Dimension member from the SelectionType dropdown and select the dimension. Click on Update.

Binding to a dimension category

Notice how the inserted row is hidden for other users but visible for 'Audio' category users.

Inserted row hidden for other roles

Inserted row visible for the relevant role