New Method for Embedding Web Edit from Madhav Kannan

Madhav Kannan has been a superstar in Tableau’s APAC region for a few years now, but he’s really outdone himself with an improved technique for embedding the Web Edit functionality of Tableau Server smoothly into another web application, without requiring anything additional be added to the Tableau Server. I highly recommend you check out his blog post if you are looking to embed Web Edit!

Flexible Trellis Views in Tableau

Tableau does small multiples really well, but if you really only have one dimension, the only way to display things is in a straight list. With a couple of simple calculations, you can transform that list into a trellis, making 2 dimensions out of one!

Trellis Pies

If you look at the screenshot, you’ll notice that the Columns and Rows are both calculations, Table Calculations to be precise.  These are generic calculations that can be added to any workbook.

[Number of Columns] is an Integer type Parameter, necessary for the modulo and division calculations..

Column Calc

( INDEX() – 1 ) % [Number of Columns]

Row Calc

INT ( ( INDEX() – 1) / [Number of Columns] )

How do these calculations work? The Column Calc is using the Modulo operator (%), which only returns the remainder from division. The remainder will be the same for each multiple in a list — i.e. 3, 5, 7 all have 1 as a remainder when divided by 2. This allows for grouping in the columns.

The Row Calc is basically cutting the Index into as many slices as there are columns.

You set both of these calculations to be Discrete (blue pills) and they will create individual “slots” in a trellis.

In the screenshot, the “listing” element is Sub-Category. So the Compute Using option of both calculations should be set to Sub-Category. This makes the INDEX() function calculate down the Sub-Category field. This field must be on the Marks card somewhere — at minimum in the Detail. You can add in additional fields to make more interesting displays, like the pies. It’s also a great way to display multiple text elements without them being straight lists.

Deselect “Show Headers” and the trellis layout calcs disappear to the end user.

Trellis Text

Developing and Deploying Tableau Content

When you use Tableau in an embedded solution, the “Tableau method” of Desktop users publishing in a free-form method to the Tableau Server on a single site often doesn’t cover the requirements for controls and QA that a software development effort requires. While the classic “dev-test-qa-prod” is not as necessary with Tableau,  any SaaS customer that is embedding Tableau will need a process for deploying Tableau to their customer’s sites from templates.

In this post, I’ll go over the deployment methodology my team recommends to customers. We’ll be looking at the two separate phases, which are often split into different teams: (1) Development (2) Deployment.

Many thanks to Tyler Dugal who helped develop much of this content for our presentation at TC16.


Exporting Scrolling Worksheets in a Dashboard to PDF

Let’s say you have a Dashboard in Tableau, where at least one Worksheet is a list long enough to have a vertical scrollbar. If you export to PDF in Tableau Server, you will only get an single page, with an image of the scrollable sheet at the top of its scrolling. If you want to give your users the ability to get a PDF with ALL of the data included, you’ll need to set up your workbook in a particular fashion, based on the following rule:

Worksheets that scroll will export out as multiple pages, showing all of the data. Dashboards export out as a single page always.

With this in mind, you can build your Workbooks in the following way:

  1. Put your Dashboard tab first
  2. Put any sheets that scroll in a dashboard directly after the Dashboard tab
  3. Hide any sheets that do not scroll in a dashboard


The user needs to choose “Sheets in Workbook” to get both the Dashboard and the Sheets with all the details, but you can choose which sheets you want in the selector at the bottom.


Ad Hoc Tableau Server Reports with Default Formatting

Prior to Tableau 10, all formatting was done on a worksheet by worksheet basis. Tableau Desktop now has a concept of “workbook formatting” which become the default for any new worksheet. Each version of Tableau is bringing more formatting options into the scope of workbook formatting (for example, line formatting is being added in 10.2), but not everything is available yet.

Make a Template Worksheet and Dashboard

For any option available in workbook formatting, you should set it there (in the Format->Workbook menu at the top). For other things, like background shading, you should create both a template worksheet and a template workbook.

For the template worksheet, I suggest creating a Calculated Field with a simple message indicating that it is a template to be copied. Drag the field onto Text and change the fill type selector at the top from “Standard” to “Entire View” to make your message show up.  Use the Format->Worksheet menu to set everything you’d like.

Next make a template dashboard the same way, and drag out the Template Worksheet. In the end, it will look something like this:


Now publish it to Tableau Server. Rather than having users start their ad hoc analysis from a Data Source, have them start from the Ad Hoc Template Workbook. When they Save As to a new workbook, they can delete the templates sheet and dashboard because the new sheets will have that formatting which they can duplicate from.

Using Wildcard Pattern-Based UNION in Tableau 10.1 instead of WDC

Tableau has had UNION for Excel and text files for several versions now, but Tableau 10.1 delivers a huge expansion of the feature with Wildcard Pattern-Based UNION. Now, you can set up a match pattern to bring in all the files you have into the UNION automatically.

To see how it works, check out the ever excellent Tableau Online Help.

Not only is this an immensely helpful feature for saving time the first time you are doing analysis, it also can save a ton of development time and effort when you are dealing with data sources that aren’t already in a database.


Assigning Colors via XML Modification

In Tableau, colors are assigned to a given dimension across the workbook (as opposed to just per sheet). This assignment actually happens in the XML, which means you can substitute in different color assignments if you are publishing from templates. Even better, in Tableau 10.0, if you assign colors from one of the included color palettes, new items will automatically be assigned a color from that palette.


Complex Fiscal Calendars in Tableau

Tableau has the option to specify the month that a fiscal calendar starts on, and the day of the week that a calendar starts on. This adjust the Year and Quarter that a given date belongs to in the date hierarchy. If your fiscal calendar is more complex,  you’ll need to do some adjustments to your dates to make everything work smoothly in Tableau.

Sometimes I put myself a note to write up a topic and lo and behold! there is already great content out there.

The standard solution, much like the currency conversion example, is to have a fiscal calendar mapping table, which you JOIN to your data. In essence, you have a row for each “real” date in the calendar,  with the information about where that day falls in the fiscal calendar (year, day, month, etc.) in additional columns. You’d base your time filters in Tableau on the fiscal date columns. There’s already a great example of this method on the Tableau Community. The example uses an Excel table, but obviously you would want that table in your database if you are bringing data from a database. Cross-database JOINing in Tableau 10 may let you keep the two separate.

The toughest trick with a complex fiscal calendar is how much it can diverge from the “real” calendar (although — is time really real, man?) Particularly for continuous date filters, the fiscal date might be really far off. If FY 2016 Month 1 Day 1 is actually July 15th, 2015, you don’t really want to show the “real” date, even if you want a continuous filter. I recommend creating a continuous integer that reflects the fiscal calendar, but not converting it into a date field: 20160101 will still be understandable and work efficiently for continuous filtering, but it won’t be confused for the “real” date.

InterWorks also has some great write-ups of solutions using calculations, that don’t require the mapping table:

Creating Dynamic Fiscal Calendars in Tableau

Creating Custom Fiscal Year Calendars in Tableau

One last thing to note, when publishing templates where the fiscal calendar start month or week start day varies from customer to customer: The “Date Properties…” menu in Desktop simply adds the following XML to the very end of the datasource XML, where it can easily be modified:

<date-options fiscal-year-start='august' start-of-week='friday' />


Currency Conversion and Exchange Rates in Tableau

Currency conversion comes up relatively frequently in conversations about Tableau, but there’s not a ton written about it specifically for Tableau, because the solution is actually a generic solution for currency conversion with relational databases.

The main thing to remember with exchange rates is that they change over time, and therefore need to be stored with a Date field. Any join to the fact data you have must JOIN on the originating currency and the date of the transaction. Some organizations may have specific rules on the exchange rates they use over a specific date range — I’ll be showing a daily example, but obviously if you calculate your exchange rates monthly for reporting purposes, the same concepts will work.


Adding Web Fonts to Tableau Server

Note: this is unsupported and adds things to the Tableau Server files, which also means you need to have a process to put it back in place with every upgrade. See CSS magic tricks with Embedded Tableau Server views and Disabling Browser and Apache Caching to See Your Changes to Tableau Server  which outline the necessary steps to make these types of CSS edits work.

You’ve got great design standards and awesome fonts, but you publish a beautiful viz to your Tableau Server and your end users end up seeing the default sans-serif or serif fonts that you’ve worked so hard to overcome. Tableau Server doesn’t have any in-built functionality for Web Fonts, but since it is just standard Apache serving up regular HTML5, JavaScript and CSS, you can easily add in anything you  want your users to have access to.

The secret is appending your web font CSS to vqlweb.css. I’ve already discussed how to edit the vqlweb.css file in the previous post mentioned in the warning at the top, and all the same rules apply. To get your font to display, you just need to add the appropriate Web Font css at the end. For example, Jeremy here built a kicking-rad viz with ‘Outrun future’.

Here’s the CSS I added to vqlweb.css:

@font-face {font-family: 'Outrun future';src: url("Outrun future.otf") format("opentype");}

The only other thing I had to do was copy the font file ‘Outrun future.otf’ to the /css/ directory (the same place where vqlweb.css lives).

Now go and add all the Web Fonts your heart desires (but make sure you can replicate your setup on each upgrade!)