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.
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.
In the grid’s setup, we can rank the products hierarchy by the measure outputted to the cell J5.
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.
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.
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.
We can then create a single named range that combines both the headers and the data, e.g.
=table_header:table_data
We can then use this range as the ‘range to display’ in any viewport, such as on a dashboard sheet:
Hi Catherine,
Thank you for that. It’s always good to see how things can be handled in different ways. We’ve implemented something similar – more complex as we have included an icon (character) to show the “active” column with sort direction and a second click on the column header changes the sort direction. You can imagine that this results in some “if” statements.