Dynamic Columns

Has a user ever requested that they can create their own report for exporting purposes and they want to be able to adjust the order of the columns?


One way to have dynamic columns is to use Parameters. In my Tableau Public dashboard here, I created Parameters for you to adjust the first two columns of a worksheet.


How to Create This View


I am using the Sales - Superstore dataset that is packaged with Tableau.

You will need to create two Parameters for your dashboard. (I have these pictured on the second dashboard view. You can use the Build Icon at the upper right-hand corner to navigate to this page. )

I entered all of the drop-down options I wanted for the user for the first column as Parameter 1. For the second Parameter, notice I have a None option. I included this just in case your user wants to have just an option for the first column.

I created the following Calculated Fields;


Column 1

IF [Parameter 1] = "Category"

Then [Category]

ELSEIF [Parameter 1] = "Region"

Then [Region]

END


Column 2

IF [Parameter 1] = "Region" AND [Parameter 2] = "Region"

THEN 'Duplicate Selection'

ELSEIF [Parameter 1] = "Category" AND [Parameter 2] = "Category"

THEN 'Duplicate Selection'

ELSEIF [Parameter 2] = "Region"

Then [Region]

ELSEIF [Parameter 2] = "Category"

then [Category]

ELSEIF [Parameter 2] = "Sub-Category"

then [Sub-Category]

ELSEIF [Parameter 2] = "None"

Then '●'

END


The first two calculations will display the options selected from each Parameter. I have a circle icon for None but you can leave this out if needed.

I also have the text "Duplicate Selection" enabled if the user selects duplicate options for Parameter 2. So if they select Region for Parameter 1, it will not display any values if they also select Region for Parameter 2.


Sales 2

If [Column 2] = "Duplicate Selection"

then 0

ELSE [Sales]

END


Profit 2

If [Column 2] = "Duplicate Selection"

then 0

ELSE [Profit]

END


The calculations above will display "0" values if the user selects the duplicate Parameter values.


(I have these calculations listed on the third view of this dashboard.)


Calculation1

IF [Column 2] = "Duplicate Selection" then "True"

Else "False"

END


Calculation1 above is simply used for the Color Marks card. For 0 values I have the text as red, for all other values I have the normal font color.


Set up the worksheet as shown below;


Disadvantages


There are some disadvantages of creating this view. Notice if you pick Region for the first column and then pick Sub-Category for the second column; the data extends past one page. You would need to educate your users that if they wanted to download this view, they would have to pick "Specific Sheets from this dashboard" for the download object buttons.



This export would only work well with the PDF option and not the PowerPoint option. In the Image below, you can see the PowerPoint export squeezed the data into one view, which is hard to see.



Another way to view the data would be to have the Download as a Crosstab option if they wanted an Excel spreadsheet format. This will also display any calculated fields you created for color and so on. This can be confusing for your client.


361 views0 comments