Custom Grid & Table Headers For Dynamic Sorting

Consider this simple grid. It shows a ranking of the top 10 products by Sales Amount. We are going to add our own headers to this grid allowing us to create our own column names as well as implement custom functionality for dynamic ranking.

A simple XLCubed grid showing a ranking of products

Creating Custom Headers

First, we need to hide the grid’s current labels. This can be done by simply hiding the Excel rows that you don’t want to see, or you can change the grid’s properties. In this example, I have turned off the following options:

  • Hide header hierarchies
  • Hide column labels
  • Hide row labels

I kept column members visible so that I could use in them the formulae that I will create.

We then need to create our custom headers. These can be any text, entered straight into the cells, and can be formatted with any standard Excel formatting. This can be a great technique when the hierarchy member names are not readable or user friendly.

Since we are just entering into an excel range, we can even drive these headers with formulae. For this example, they need to be XL3Links that will output the name of the selected column to a different cell so that we can then rank by that member.

The grid now has hidden headers replaced with custom XL3Links

In the grid’s setup, we can rank the products hierarchy by the measure outputted to the cell J5.

Screenshot showing XLCubed ranking form for the grid

You can then apply any formatting that you require. In this example, I have applied conditional formatting so that the selected column is highlighted.

To finish, hide and rows or columns that no longer should be visible.

Now, when we click on a column header, the ranking is reapplied with the selected measure.

XLCubed grid with headers driving dynamic sorting

Creating Dynamic Named Ranges

In order to display our table elsewhere, i.e. using an XLCubed viewport, it is usually best practice to create a named range of the grid or table. This is less critical in this example as we are always returning 10 rows, however it is not uncommon to have a grid that can return a variable number of rows.

First, go to the grid’s properties and, under the Interaction tab, create a new named range based of the grid’s members and data.

Form for setting a dynamic named range in a grid

We also want any viewport that references our grid to also display the custom headers that we have made. To achieve this, first define a standard Excel named range that contains the grid headers.

The custom headers have been given a named range

We can then create a single named range that combines both the headers and the data, e.g.

=table_header:table_data
Excel named range form combining table and header ranges

We can then use this range as the ‘range to display’ in any viewport, such as on a dashboard sheet:

Xlcubed viewport on a dashboard sheet

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.