Dynamic Headers for Dynamic Columns

As a follow up the previous post about Dynamic Columns, a user asked me if there was a way to display the parameter selected as the header.

There are a couple of methods of making a column header dynamic;

Date Range Formula for Line Graphs

In a previous article, I demonstrate how you can put a dynamic header for a line graph here.

Using a "Hidden" Grand Total Row

Andy Kriebel demonstrates how you can use Annotations in a Grand Total Row at the top of a stacked bar chart here. I tested this out and you can put the Parameter text (as shown in a Tooltip with the <> tags) in an Annotation.

Using Another Sheet as Your Header

I created a new worksheet in my workbook from the previous blog.

I placed the Column 1 and the Column 2 calculated fields onto the Rows Shelf.

I then replaced the Column 1 pill with Parameter 1 by dragging it over the pill on the Rows Shelf.

I then repeated these steps for the Column 2 calculated field.

Then I put the following calculated fields on the Rows Shelf;

Profit Header

TRIM(" Profit ")

Sales Header

TRIM(" Sales ")

In my dashboard, I hid the Title of the first worksheet displaying the dynamic columns.

I placed the newly created worksheet above the other worksheet.

I hid the title on this new worksheet.

I then formatted the text to be bold etc.

I also removed grid lines etc.

I moved the headers around by adjusting the column widths.

I moved the header line all the way over to hide the abc.

You can see the finalized result here.

92 views0 comments