Monday, September 29, 2014

More than 16 columns (row headers) in Tableau

As you well know if you are reading this post, Tableau only allows 16 row headers/labels in a single visualization before it starts concatenating the header values.  The default is actually 6 headers until it starts to concatenate but this can be increased if you go to the menu Analysis->Table Layout->Advanced

























But what if you want more than 16?

Note 1:  I first want to say that no good analysis can stem from a text report that has 16+ columns in it.  However I understand sometimes folks just want to show some drilldown detail that can be exported to csv/excel/etc.

Note 2:  If you are using this workaround to be able to display/export larger tables from Tableau, it is not a good idea to treat a Data Visualization application like an ETL tool.  You are using Tableau wrong.

Note 3: This is not supported.  At all.  If something breaks please dont email Tableau Support about it.  This hack comes 'as-is'.

Note 4:  I think this hack will work if you want to put a few extra columns (maybe like 20 total), but dont go crazy with it and try to put 50 columns out there.

The only way to do this is to hack the XML of the .twb (wookbook) file.  If you save your file as a .twb, then right-click on it and choose to open it in a text editor (I like Notepad++), you can search for the term attr='row-levels' and you will find this section of the XML:











If you change those numbers to whatever else you want, then save your file (be sure if you choose 'save as' that you save as the file type 'All files' in the dropdown at the bottom and add '.twb' to your file name - i.e. dont save it as '.txt').  When you open it in tableau next, you will be able to add that many dimensions to the Rows shelf before it starts to concatenate the headers.

Result (I typed in 21):









Note:  You can only open .twb files to see the XML in a text editor.  If you have already saved your file as a .twbx you can right-click on the icon and choose 'unpackage'.  This will create a .twb file and a folder with all the excel files/extracts/images/etc that were part of the packaged workbook.
 
Note:  If you go back into Analysis->Table Layout->Advanced it will still say the last numbers you entered in the UI, not the new numbers you manually entered into the XML, just ignore them.
Again, I do not condone this behavior, but sometimes you just need 'a fix'.

Note:  Dimension headers will never get a scroll bar in Tableau (only items in the 'pane' are scrollable).  Tableau always expect you are building a chart and doesnt expect you will display tons of text columns.  Therefore you must put your view on a dashboard and fix the size to be big enough to hold your entire view (width wise).  I like to set mine to 'Range' and then set a Min size but no Max (the height on the Min can be small, but the width must be large enough to fit all your fields).
























Workbook:  https://app.box.com/s/760tvsfnko1cdxj2ghsf

-The Don Data

8 comments:

  1. Hi,
    i have downloaded your report, in +16 columns sheet u have shown 21 columns in the same dashboard when i am creating a new sheet with more than 16 columns then it is getting concatenated.have done anything special to the +16 columns sheet??
    regards
    Chaitali

    ReplyDelete
  2. Did you open the workbook in a text editor and edit the settings mentioned in the post? You cant just do it in the UI.

    ReplyDelete
  3. I dont think this works in 9.x anymore. I couldn't find someplace to tweak the # of Columns.

    ReplyDelete
  4. Hi Garrett - you will probably need to edit it in the UI to increase it to something more than 6, then it will appear in the XML in the text editor. It doesnt add those settings into the XML until you change it to something other than the default (which is 6). Sorry if I didnt make that clear.

    ReplyDelete
    Replies
    1. Hm I tried this but I didn't see it. I'll double check though.

      Delete
    2. Looks like you were correct! Thanks for the FYI

      Delete
  5. Thanks for the help Jesse. Looks like I had to edit multiple sections of the XML that had 'row-levels' value='16' to '21' before it worked for me. Maybe because I have multiple worksheets.

    ReplyDelete
  6. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.


    Tableau Online Training|
    SAS Online Training |
    R Programming Online Training|

    ReplyDelete