Issues using Tableau for Tabular Reports
I’ve been working on some projects where tabular reports were required using Tableau. What I mean by “tabular reports” are reports that look like simple reports you might get out of Crystal, SSRS, etc. As you can imagine, there were a number of issues making this happen. Luckily, a lot of generous folks in the Tableau community have provided solutions to many of them.
For my own sanity, I figured I’d catalog them to have them ready for the next time. I also figured that, if I publish them, I could see if any readers have uncovered other issues or better solutions.
By the way, I do know tabular reporting is a somewhat off-brand use of Tableau. But, I’m in the camp of “If the client knows what problems there are and they still want it, give them the best job you can”.
So here are the problems I’ve run across so far:
Data is displayed in a crosstab-style format:
To create textual output in Tableau, you use a Text Table. If you were showing sales by Region, Ship Date and Customer Name, you’d get something like the following:
This shows data hierarchically, from left to right. That means, Customer Name within Ship Date and Ship Date within Region.
As you can see, there is only 1 label for Central for Region and only one for 3/4/2013 for Ship Date, even though there are multiple customers being reported for each. It makes sense for it to be this way for data which is aggregated by these 3 dimensions. But it won’t satisfy everyone who wants a standard tabular report.
The solutions I’ve seen for this involve generating (and probably hiding) a column on the right whose value is unique for each row. The strategies for making this column include creating "Combined Fields" or using unique IDs which you would provide in your data source at the desired level of granularity.
Data is sorted hierarchically:
Data can only be sorted in the displayed hierarchy. In the table above, it is only possible to sort by Region, then by Ship Date within Region and Customer Name within Ship Date, etc. You cannot sort solely by Ship Date or Customer Name.
The obvious solution to this is to arrange your data so that your sorted data is in order, starting on the left-hand side. If you’re using the Combined Fields approach, mentioned above, you can arrange your combined fields to make it sort in the way you like. This only works for a single sort order per report, though.
When you don't have any measures, there is an “Abc” Column instead:
If you don’t have any measures in your text table, Tableau, by default shows a measures column, with no label, whose value is “Abc”.
There are several solutions for this problem which are described step-by-step in this Tableau Knowledge Base article.
A report with a single measure has no header:
If your report has a single measure, which you drag to the measure column, it will not have a header.
There are several solutions to this. The one I like the best uses the Measure Names/Measure Values approach. This is the same approach used to get multiple measures on your visualization. To do this, you drag the “Measure Names” dimension to Columns. Then click the Measure Names’ pill’s dropdown and filter the names to select the the measure you want. Then drag “Measure Values” from Measures to the Text shelf.
Limitations on numbers of columns displayed:
By default, you can show 6 discrete fields on the Rows shelf. After that, dimensions are concatenated into one of the columns. You can increase this to 16 dimensions via the menu: Analysis->Table Layout->Advanced which has a “Maximum level of row labels” setting. Using this, you can show up to 16 dimensions.
I thought you were out of luck, if you want more than 16 discrete columns in a report. Then a client sent me this recent blog entry. It’s complicated, I’d probably never suggest this indiscriminately to every business user I met. But it will do the trick.
Scrolling limitations when table is too wide:
You can use the scroll bar on the bottom of the measures to scroll through measures, as long as the dimensions don’t occupy your entire display.
Once the dimensions take up the full width of, or extend beyond the edges of the display, you cannot see the measures, or any dimensions beyond the right margin. (Note: I haven’t tried this on Tableau Server, but this is how it works in Tableau Desktop).
There may be solutions to this. I believe the solution to another problem shown here is a possibility. When I saw it, I was working on a project for reports authored by end users. So I didn’t pursue it as a solution.
Tableau changes visualizations away from text table, depending on columns used:
When you add some types of fields to the text table, we found Tableau changed the visualization, including changing the ways in which various dimensions and measures were arranged into rows, columns and text. For example, if your table looks like this:
Then you add a date, and change the date to a day or ExactDay, it will no longer be strictly tabular. And the measures may overlap.
You can fix this by clicking the down arrow on the date pill and changing the value to Discrete, then clicking Format in the same date pill menu and selecting the date format you like. You can also solve this by using a second date which is just a string of the date part. This doesn’t involve multiple steps to add to the workbook but having 2 of the same dates may be confusing.
That's my current list of the problems we encountered doing tabular reports. Let me know if you ran across others and how you resolved them.