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.

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.

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

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.

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

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

1. The Premium Table also displays a description of the function and a detailed explanation of the arguments with 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.

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.

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

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.

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.

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.

4. Include in Charts

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

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.

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

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.

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'.

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

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.

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.

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.

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 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.

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