Hierarchy settings

The general display settings are used to customize the appearance of the report.

In the Inforiver toolbar, navigate to the Home ribbon. The Settings option is available in the 'Actions' section. Click Settings > Display Settings to open up the Display side panel.

Click on the Hierarchy tab in this panel to view the hierarchy settings.

Hierarchy settings

Let's explore the options to customize hierarchies in your reports.

1. Header and Column

1.1. Category highlight

This drop-down lets you show or hide the category highlight. This option is set to 'Off' by default. When this option is enabled, a color picker is displayed. You can select a color to highlight the category hierarchy.

Cateory highlight

1.2. Responsive columns

If this option is enabled, when the report canvas is resized to a small size, only columns that fit into the canvas will be displayed and others will be hidden.

Responsive columns

In the image below, after resizing the canvas to a much smaller size, out of the 3 measures added in the report, only 2 measures that fit into the view are displayed. This is because the responsive columns option is enabled.

Report with responsive columns enabled

1.3. Ignore measure in null suppression

This setting is only available in Reporting Matrix

Rows with null values for most columns can reduce the accuracy and quality of your reports. The Ignore Measure in Null Suppression option allows you to choose measures that are not relevant for analysis - if only these measures have values and all the other measures are null, the row will not be displayed.

Notice how the rows highlighted in yellow are suppressed when the 'EOD Position' and 'Shares Outstanding' measures are selected from the null suppression dropdown.

The Hide Blanks option should be set to visible values for null suppression to work.

Ignore measure in null suppression

1.4. Show totals/subtotals values for non-numeric measures

You can add non-numeric measures like dates and text fields and display them in your reports. The first value is displayed in the total/subtotal cell by default, which may be irrelevant in an aggregated row. You can choose to leave the totals and subtotals blank for non-numeric fields by enabling this property.

Non-numeric fields displayed as totals/subtotals

Totals/subtotals left blank for non-numeric fields

1.5. Combine column headers

Enabling this option will combine the column headers and measure labels to display compact headers. As you can see from the below image, when this option is turned off, the column headers and measure labels are displayed separately.

Combine column headers

When this option is enabled, the column headers and measure labels are combined and displayed compactly. If the Combine Column Header option is enabled, you can set a custom separator using the Column header separator option.

Combine column headers

If the 'Combine column headers' option is enabled, you won't be able to reorder the columns.

Column header separator

This option lets you specify the separator that will get inserted between the column header and the measure label.

1.6. Ragged hierarchy

In the case of ragged/unbalanced hierarchies where the number of levels is uneven, you can hide the blank category rows.

Ragged hierarchy option

Notice how the blank categories under 'IT Expenses' and 'Other Expenses' have been hidden after enabling the Ragged Hierarchy option.

Ragged hierarchy sub-options

On enabling ragged hierarchy, additional options are displayed:

1.6.1. Hide blanks

Using this option, you can choose to hide the rows if the category is blank, values are blank, or both the value and category are blank.

If your reports contain rows that only have an Others measure but no actuals, you can choose to suppress that row. Navigate to Hierarchy Settings and set the Hide Blanks dropdown to ‘Visible Values’. When you select Ignore Measure in Null Suppression from the column gripper, notice how the highlighted row is hidden.

Null suppression

1.6.2. Suppress zeros

If you enable this option, then the rows containing zeros will be suppressed.

This option becomes available only if the option chosen in the Hide Blanks is 'Value' or 'Value+Category' in the Writeback matrix

In the Reporting Matrix, you can suppress rows with zeros in the table layout as well. Suppression is done only when all the columns in a row have zeroes.

To do this, Enable the Suppress Zeros toggle to suppress the rows with zeros.

Suppress Zeros toggle is disabled

Suppress Zeros toggle is enabled

You can now suppress both native measures and visual columns with zeros.

In the image below, the Sum of Discount is a native column with zero values and Loss is visual column with values as zero, which are visible as the Supress Column with Zeros and Suppress Visual Columns with zeros toggle are disabled. Enable the toggles to suppress them.

Suppress columns with zeros and suppress visual column with zeros toggle are disabled.

1.6.3. Suppress null data input measures

Data input measures with no assigned values offer no meaningful contribution to reports. You can opt to suppress them by enabling the Suppress Null DI (Number) Measures option in Display Settings.

Suppress null data input option disabled

In this sample report, a budget has not been allocated to the Mineral Water category. We can hide this column by enabling the suppress null option for data input measures.

Null data input measure has been suppressed

1.6.4. Suppress null columns

Columns containing null values will be suppressed if this option is enabled.

1.6.5. Hide blank label columns

Columns with blank labels will be suppressed if this option is enabled.

1.7. Hide blank dimensions in column hierarchy

When you have hierarchical column dimensions, there may be cases where the entire dimension category is blank as shown in the report below.

Blank categories in the column dimension

Since these categories do not add value to the report, you can suppress them by enabling the Hide blank dimensions in column hierarchy option.

Suppress blank categories in the column dimension

1.8. Row hierarchy icon

You can select custom icon styles to represent the row hierarchy i.e. choose the expand/collapse icons for hierarchical categories. If 'None' is selected, an icon will not be displayed.

Row hierarchy icon

1.9. Column hierarchy icon

You can select custom icon styles to represent the column hierarchy as well.

Change the expand and collapse icons

2. Expand to level

If your dataset contains hierarchies with multiple levels, you can choose to expand the hierarchy to a specific level at the initial load. This setting will be retained even after applying a filter/slicer.

Expand to level

3. Mixed layout

You can display a chosen field as a separate column similar to the stepped layout. By default, it is set to 'None'. In the below image, the 'Sub Category' field has been selected and it gets displayed as a separate column in the visual.

This option is available only in the Hierarchy layout when there are 3 or more levels in the row dimensions.

Mixed layout

4. Child

4.1. Indent from parent

This option lets you set the amount of indentation that has to be applied to the child rows from the parent row.

The minimum and maximum indentation values are 1 and 25 respectively.

In the image below, every child row has been indented from its parent row by the specified amount.

Indent from parent

4.2. Single child

Using this option you can select which nodes to display when a parent hierarchy has only one child. In this example, we have a multi-level account hierarchy with several single-child rows and blank rows.

The sales and marketing expenses row has only one child. The personnel cost row's child rows have blank rows. Let's see how this report is displayed for each of the options.

• All - Displays all the rows i.e. the parent rows and the single child rows. There are blank rows at levels 5 and 6.

Single child - All

• Parent only - Displays the parent nodes of the single child. The blank rows at level 6 have all been hidden as shown below. The blank rows at level 5 are still shown.

Single child - Parent only

• Parent (Multi-level) - Displays only the highest level row of the multi-level hierarchy. The blank rows at both levels 5 and 6 are not shown.

Single child - Parent multi-level

• Single child level as parent - Displays the single child as the parent of its level.

Let's take a different scenario to explain the Single Child Level As Parent and Single Child As Parent options. As you can see below, under Operating expenses we have filtered only IT expenses. You can see blanks at levels 5 and 6.

Single child at level 4

On selecting Single Child Level As Parent, you can see that the level 6 blank row is shown as the child for the level 4 row.

Single child level as parent

• Single child as parent - Displays only the single child at the lowest level. The blank row at level 6 is shown as the child for the level 3 row.

Single child as parent

5. Data window

Max columns

Inforiver supports wide table use cases through the dynamic columns feature. You can fetch up to 1000 columns as a maximum; however, the performance would be compromised.

Every fetch from Power BI is 30,000 cells (number of rows x number of columns). This means you can load data chunks of different sizes dynamically - 500 Rows by 60 Columns (or) 30 Rows by 1000 Columns (or) 10,000 Rows by 3 Columns.

If you're working on a dataset with more rows and limited columns, you can reduce the column count using the 'Max Columns' option accordingly, so as to accommodate more rows in a single fetch and vice versa.

You can view the current column count by hovering over the 'i' icon and modify the column count if needed.

Column count

Limit Data Rows

When working with a huge dataset, with more rows and columns, you might need to increase the 'Max Columns' to more than 60, up to 1000. In such cases, Power BI executes continuous segment calls in chunks of 30,000 data points until the entire dataset is loaded onto the visual.

Adjusting the column count to load more data & data fetched in segments

For larger datasets, to avoid excessive data loading and for optimistic performance, we have introduced an internal limit to the number of segments/chunks Power BI can totally fetch. This is called the segment limit, and this value varies depending on the maximum columns chosen.

How does it work?

If 'Max Columns' is set to 60 or less, the segment limit is 100; if 'Max Columns' is between 61 and 120, the segment limit is 50; and so on. Ideally, the segment limit decreases as the number of columns increases, thereby limiting the total amount of data (rows) loaded.

However, if you want to load the entire dataset with all rows (with no limit on segment calls, despite selecting more columns), you can turn off the Limit Data Rows option in the 'Miscellaneous' section, as shown below. This works in tandem with the Max Columns option.

Limit Data Rows

In addition to the 'Max Columns' option, which allows you to adjust the number of rows and columns to fit 30,000 data points in a chunk, this additional option allows you to load all data or just a subset of it by enabling/disabling this toggle accordingly.

Let us demonstrate this with the following example, in which the column count is set to 1000 and there are more rows. The segment limit applies here automatically, which restricts the number of rows to 324. However, disabling the 'Limit Data Rows' option allows more rows to be loaded into the visual.

Learn more about dynamically setting the data chunk size using Power BI's fetchMoreData API.

6. Row totals

You can customize the totals and subtotals by adding child count, padding, border, editing titles, and more. The available options are explained below.

6.1. Show child count

Enabling this option will display the count of child rows next to the name of the parent row in brackets.

Show child count

6.2. Show descendants count

You can also display the total number of children including the leaf nodes under a parent category.

Prerequisite: the Show Child Count option should be enabled.

Show descendants count

6.3. Top padding

Using this option, you can configure the spacing/padding between row hierarchies.

Top padding

6.4. Expand/collapse

This option lets you specify when the expand/collapse icons for the hierarchies have to be displayed.

• Always present - Expand/collapse icon will be always present

• Only on hover - The expand/collapse icons are displayed only when you hover over the rows

Expand/Collapse icons

6.5. Hide hierarchy icon

With this option, you can specify the fields for which you want to hide the hierarchy icon. All the fields are selected by default, and unselecting any field will hide the hierarchy icon for that field. In the below image, the 'Sub Category' field has been unselected, therefore the hierarchy icon for that field has been hidden.

Hide hierarchy icon

6.6. Grand total title

The options related to Grand total can be seen only if the Row/Column Grand Total value has not been set to ‘Off’ under the ‘Totals’ option.

You can set a custom title for the grand total in the textbox.

Grand total title

6.7. Grand total height

The value that you specify here will override the default height of the grand total row.

The minimum and maximum values for grand total height are 5 and 45 respectively.

Grand Total Height

7. Row subtotals

With Inforiver Matrix, while using outline or stepped layouts, you can customize the title for the subtotal rows in two ways:

• By adding the parent row dimension category as a suffix or prefix

• Customizing the 'Totals' text

In the example, the row dimension category is added as the prefix and "Aggregated" is added as the text for each sub total row - E.g., "Bookcases Aggregated" or "Chairs Aggregated".

8. Total API

Inforiver pre-calculates the totals and subtotals for your data while loading the report. If your dataset is large and you do not require totals in your reports, you can substantially reduce the loading time of your report and boost its performance with the Total API option.

You can disable the total and sub-total calculations for rows and columns separately.

Total API

8.1. Row total/subtotal

Disable this to stop row-level total and sub-total calculations while loading your report.

8.2. Column Total/Subtotal

Disable this to stop column-level total and sub-total calculations while loading your report.

Notice the time taken when Inforiver pre-calculates the totals and subtotals:

When Totals are pre-calculated

When the total calculation is disabled, even large datasets can be fetched in milliseconds as shown in the screengrab below.

After Disabling the Total API

Resources

Implementing tables with 100+ columns (wide tables) in Power BI

In this section, we covered the hierarchy display settings. Navigate to the next section to learn more about advanced number formatting.