Guest Blog Post: Allow Users to Export Their Filtered Selections to Excel

 

The following is a guest blog post from Ethan Hahn.  If you don't know Ethan, he is incredibly gifted in SQL and equally gifted in Tableau.  He's also HILARIOUS!  You should absolutely follow him on Twitter!

If you’ve ever tried to give your users access to the underlying data in a viz, you know the pain – sending them through the tooltip to “View Data” / “Full Data” / “Show All Columns” / ”Download all rows as a text file” isn’t the easiest for users with varying technical abilities.  If you’re on 2020.3 you can use the new Export as Crosstab feature (you can read about it here), but until Kevin’s idea on the Ideas Forum to let developers hard-wire the button to a specific sheet gets enough votes (please upvote it), that technique requires users to figure out which view to choose, which can be confusing:

 

My favorite solution has long been Andy Kriebel’s “Greatest Tableau Tip EVER”.   All you have to do is append “.csv” to a specially formatted version of the URL, and Tableau will generate a CSV of whichever sheet is alphabetically first in the dashboard.  So you create a sheet with all the fields you need, name it something like “@Item List” then follow Andy’s steps.  All your user needs to do is click on a button, their browser opens a tab, and it downloads your crosstab.  It’s great!  (Jeff Shaffer has figured out exactly what format the URL needs to take, depending on whether you’re on Server or Public – you can reference that in his blog post.) This is such an awesome trick - thanks Andy! 

 

But it does have a shortcoming – your link opens a new session of your workbook, which has no knowledge of any filters you may have applied.  So if you only want to export Ohio, Indiana and Kentucky, or just want to see Tables and Chairs, you need a way to tell the new session what filters to apply.  How do we do that?

 

This technique leverages three clever tricks – first, Andy’s .csv trick; second, something I first heard about from Jeff, which is using URL parameters to pass values to a published dashboard (see here for one application); and third, a really slick use of table calculations I learned from a forum post by Deepak Rai on the forums.  The original poster asked how to combine information across rows, and Deepak walked him through how to use PREVIOUS_VALUE() to generate a comma-separated list of values, and LAST() to isolate the final, completed list.  We will take Deepak’s comma-separated lists, add them to Jeff’s URL string as URL parameters (which will ultimately filter the view), and pipe them into Andy’s CSV.  So that instead of having a workbook with no knowledge of what filters you’ve applied, we will apply those same filters and generate a file with just the rows you want!

So let's put it all together.  Here's a sample dashboard you can download and follow along with: Exporting Made Simple and Flexible.

(OK, as Jonathan Drummey pointed out when reviewing this technique, it’s not exactly simple from the developer’s point of view.  But for the user, it couldn’t be easier!)

The dashboard utilizes Super Store data, with a state map colored by SUM([Sales]), a bar chart with Category / Sub-Category on rows and SUM([Sales]) on columns, and a table called @Item List with a bunch of fields I’m interested in exporting.  Note that I floated that off to the right of the canvas using the Layout tab so that it’s there, but you can’t see it (a trick I learned from Kevin and Jeff!)  I added filter actions on both displayed sheets so a user can select or multi-select states or Sub-Categories.  Then I built out another sheet called “Downloader for Demo” which builds out the URL we’ll need.

 

First, let’s start with building out @Item List.  As I mentioned above, Andy’s trick will automatically download the alphabetically first sheet on your dashboard.  So I built a table that I called @Item List (using the @ symbol to ensure it is first alphabetically).  You can add whatever fields you like, but I built mine with quite a bit of detail relating to the order such as Customer Name, Category, Segment, City, etc.  Next, I floated that sheet onto my dashboard then used the Layout tab’s X and Y positions to push it off the viewable canvas:

A couple notes here:

1) If you need to add a measure, drag it to rows, then right-click and convert it to Discrete

2) Don’t worry about what order the fields appear in – the .csv trick automatically puts the fields in alphabetical order.  If field order is super important, my suggestion would be to duplicate each field and rename it with “01) Last Name”, “02) First Name”.

3) This is a good time to publish your dashboard to a development environment so you can have a URL to test against.

 

Now let’s work on that URL.  First, I have created a calculated field called [@Delimited State] (you’ll note that I often precede my calculated fields with @ symbols).  This calculation simply bookends the state with pipe delimiters. 

 

          @Delimited State

                   "|" + [State] + "|"

 

I also created a similar calculation for Sub-Category:

 

          @Delimited Sub-Category

                   "|" + [Sub-Category] + "|"

 

I’ve placed both of these on rows.  When you put them together, the result is sort of a cross-join of states and sub-categories (don’t confuse this with a 1=1 Cartesian join - it was just more of a frame of speech):

To make things more manageable for this demonstration, I've filtered down to just Iowa, Kansas, and Missouri along with just Accessories, Copiers and Phones.

 

Next up, we have a field called [@List of States], which will allow you to build a delimited list of states:

 

          @List of States

                   PREVIOUS_VALUE(ATTR([@Delimited State]))

+

IF CONTAINS(PREVIOUS_VALUE(ATTR([@Delimited State])), ATTR([@Delimited State]))

THEN ""

ELSE ATTR([@Delimited State]) END

 

This calculation will be placed on rows.   It is very important that you set it to calculate on Table (down)!

PREVIOUS_VALUE() is sort of self-referential – it returns whatever value this field had in the prior row.  If you’re on the first row, then it substitutes the expression in the parentheses.  So on the first row, we give it [@Delimited State], which is “|Iowa|”.  Here’s what Tableau sees on that first row:

…which evaluates to: “|Iowa|”

 

On the next row, here’s what Tableau sees:

…which evaluates to: “|Iowa||Kansas|”

 

The next row sees this:

…which evaluates to: “|Iowa||Kansas|”

 

Here’s the calc in action:

Now we do the same thing with Sub-Category, also calculating on Table (down):

 

          @List of Sub-Categories

                   PREVIOUS_VALUE(ATTR([@Delimited Sub-Category]))

+

IF CONTAINS(PREVIOUS_VALUE(ATTR([@Delimited Sub-Category])), ATTR([@Delimited Sub-Category]))

THEN ""

ELSE ATTR([@Delimited Sub-Category]) END

We’ve got our list of states and sub-categories, so we just need to put them together into a calculated field that I called Construct URL. 

 

Construct URL

"https://public.tableau.com/views/ExportingMadeSimple-AndFlexible/ExportingDashboard.csv?:showVizHome=no"

+ "&State=" + REPLACE(REPLACE([@List of States],"||",","),"|","")

+ "&Sub-Category=" + REPLACE(REPLACE([@List of Sub-Categories],"||",","),"|","")

 

This calculated field contains the URL of our dashboard as well as a delimited list of states and sub-categories.  Together, this will provide a link to our downloadable table, while filtering the data based on those listed states and sub-categories. 

In order to get the URL, I first had to publish the dashboard.  I then used the URL format according to Jeff’s instructions.  From there, I added the URL parameter for state with “&State=” and then I just cleaned up my list a little.  Next I need to replace my pipes with commas.  This is done with the first REPLACE() function.  And finally, I cleaned up the first and last pipelines.  I then replicated that with Sub-Category. 

 

I placed this calculation onto the Detail card. 

 

A note here on constructing URLs – this can be tricky sometimes.  Jonathan Drummey suggests starting in a text editor, creating your sample URL string there, logging into Server/Online, and pasting it into the address bar to test it.  If it works, you can note that in your text editor, then add the next complication; if it doesn’t work, note that as well and tweak it until you get it right.  It’s a lot easier to iterate your URL construction in a text editor/browser than it is in a complicated Tableau dashboard action!

 

Okay, we are getting close.  I am still working with the “Downloader for Demo” worksheet.  You should see something like this:

Now I didn’t need all of these rows.  The process builds upon the values prior to it, so the last row is a compilation of all rows.  For that reason, I simply trimmed the data down to just the last row using a calculation I called “Is Last Row”:

 

Is Last Row

          LAST()=0

 

The LAST function returns the numbers of rows from the current row to the last row in the partition.  So when I say LAST() = 0, that means it will only be TRUE when it is the last row. 

 

I placed that calculation on the filter shelf and set it to only TRUE.  As Ken Flerlage recently wrote about, Table Calc Filters happen very late in the Order Operations.  So our lists and our URL aren’t affected by LAST() = 0 filtering out everything but that last row – we get all the table calc magic behind the scenes, and just end up with the last row:

 

The next step was to modify this view to show none of these fields.  To do this, I clicked on each pill and unchecked Show Header.  Please Note: in my accompanying Tableau Public example, the Downloader for Demo worksheet is as shown above and is in the workbook just for demonstration.  The actual worksheet used in the dashboard is labeled as "Downloader" and it is a direct copy of the Downloader for Demo worksheet but with the headers hidden and some additional details applies (please read on).

(Note: as long as you’ve got @Delimited State and @Delimited Sub-Category on rows, you don’t actually need the list calculations on rows as  they’re incorporated by reference in Construct URL.  You just need to make sure Construct URL is on the Detail card and that it’s calculated on Table (down)).

 

 

The next step was to provide the user with something to click on in order to download the data.  In this example, I downloaded a “download to Excel” icon:

I added that icon to my Shapes folder, changed the mark type to Shape and selected this image.

Next, I added this sheet to the dashboard and I added a dashboard URL action set with the source sheet as Downloader, running on Select, and for the URL, I passed in A

I mentioned this earlier, but I also added in some filter actions so that users can utilize the charts to filter down to certain selections.  I created one for State and one for Sub-Category.

And that was it!  So how does it work for the end user?  Well, when a user selects a sub-category or state (or multi-selects), it build a list of states and/or sub-categories and feeds it into the URL.  Then when a user has those selections in place, they can click the Export icon to employ a URL action to go to that predetermined URL that now contains parameters to filter the view.  In fact, the user can hover over Excel export button and it shows the URL I’m passing in (though I normally turn that tooltip off for a better user experience):

And in the end, when the users clicks the icon, their browser will automatically download a .csv with data filtered to their selections. 

 

A few final thoughts:

This works great when the values you’re filtering on are at a relatively small scale, because your Downloader sheet essentially has to join every combination of every dimension you’re using.  If I’m looking at natural disaster data in the last 50 years, say, and wanted to use State, Year and Month, that would be 50 X 50 X 12 = 30K rows.  Add in a list of 20 Disaster Types and we’re up to 600K rows that Tableau has to run these table calcs on.  You can see how quickly this could get out of hand.

Also keep in mind that URLs are generally limited to 2,048 characters.  That means this is not a good technique for, say, generating a list of 500 Account IDs (unless your Account IDs are all four digits or fewer!).  You’re better off passing the filters that created the list of 500 records, and not the Account IDs themselves (though for complicated filters that create relatively small lists of IDs, this may be an option).

You can also pass values to Tableau parameters using the URL’s parameters – I’ve used that to filter on “the last 120 days”- type filters.  Used in combination with parameter actions, that could be a powerful technique.

Another word of warning from Jonathan Drummey: he cautions to be careful using this technique if there’s a chance you might pass special characters.  Say you’re passing the name of a law firm using this method, and you add a new one named “Dewey, Cheatem & Howe”.  If you aren’t careful escaping the comma and the ampersand, Tableau will see one law firm named “Dewey”, and another name “Cheatem”; then the “&” will make it think the list is terminated, and it’s time to look for a new field to filter on!  Furthermore, while Tableau’s worksheet actions normally handle this kind of encoding for you, we’re bypassing that with our calculation – that means it’s on you to know your data and prepare for any curveballs!  Jonathan passed along this Knowledge Base article to help with escaping special characters in URLs.

I should note that this technique is also useful for addressing the dreaded * in a tooltip.  Let's say you want to list the account numbers associated with one client where a client may have multiple accounts. You could use this technique to create a delimited list and show that list in the tooltip.  

Many thanks to my colleagues Dinushki DeLivera and Jennifer Dawes for their help as sounding boards for me and Kevin Flerlage as we worked through this approach.  And a special thanks to Andy Kriebel, Jeff Shaffer and Deepak Rai for their brilliant tricks that underlie all this fun.  I’d also like to say thank you to Jonathan Drummey for generously taking the time to look over my dashboard and offer his thoughts (and, as always, ideas to improve and extend the basic idea).  I should note that while I learned the table calcs used to create the delimited lists from Deepak Rai on the forums, Jonathan was doing this stuff at least seven years ago!  Do yourself a favor and check this out!  

  

Thanks for reading,

Ethan

Kevin Flerlage, November 16, 2020

Twitter | LinkedIn | Tableau Public