Using Excel to Generate Tabcmd Syntax

Data

Using Excel to Generate Tabcmd Syntax

This post builds off of two great previous posts about Tabcmd by Kate Treadwell and Tanner Ladd. Please refer back to these posts if you have questions about setting up Tabcmd and distributing PDFs.

The goal of this post is to show you how to use Excel to generate the syntax to export PDFs from Tableau Server in cases where large numbers of PDFs will be created. In Excel, the majority of the work is done using the CONCATENATE and IF functions. If you are not familiar with these functions, follow the links and test them out in Excel before moving on.

One of the keys in using concatenate to create syntax is understanding how to insert quotation marks into the formula. A single quotation mark indicates the beginning or end of a string. To add a quotation mark in the middle of a string, enter two quotation marks. To add a quotation mark to the end of a text string, enter three quotation marks. To add a quotation mark by itself, enter four quotation marks. Below are examples of formulas and results:

Formulas and results

In order to use Excel to create functional Tabcmd syntax, we need to understand the components of an export command, specifically those that will remain constant and those that will vary. The sections below highlighted in gray will likely be static depending on your use case.

Example:

Syntax

Notes:

  • Spaces need to be replaced with “%20” in the quick filters and values.
  • Special characters used in the filters or values will prevent the PDF from being exported. 
  • Parameters can be activated in the same way as quick filters.
  • Quick filters do not need to be displayed in the view for Tabcmd to be able to adjust them.
  • More Tabcmd export options and examples can be found here.

Publish the attached workbook Tabcmd PDF to Tableau Server, update the save location in the syntax below and then, in PowerShell, log in to Tabcmd to test it out.

tabcmd export “TabcmdPDF/SalesReport?Region=Central&Customer%20Segment=Consumer&Year=2013” –pdf –pagelayout landscape -f “C:TableauPDFsCentral_Consumer_2013.pdf” –timeout 300

Once we have an export function that we know works, we can use Excel to scale up the syntax. Below is the basic structure I used to list the components for each export request.

Basic structure

The big leap is to then concatenate these inputs along with the static information to create the final syntax. 

Formula:

=IF(D7<>“”,CONCATENATE(“tabcmd export “””,A7,”?”,$D$6,”=”,D7,”&”,$E$6,”=”,E7,”&”,$F$6,”=”,F7,””” –pdf –pagelayout landscape -f “””,B7,D7,”_”,E7,”_”,F7,”.pdf”” –timeout 300 “),””)

Result:

tabcmd export “TabcmdPDF/SalesReport?Region=Central&Customer%20Segment=Consumer&Year=2013” –pdf –pagelayout landscape -f “C:TableauPDFsCentral_Consumer_2013.pdf” –timeout 300

Open up the attached Excel file to see how this formula was used to create syntax to pull PDFs for every filter combination in the example workbook. Use Ctrl+~ to reveal which cells contain formulas and which have static text. Once updated, all of the text highlighted in green can be pasted into PowerShell and run to export the 64 PDFs.

PDF Export Tips

When exporting large numbers of PDFs:

  • Always check the number of PDFs in a folder once done to be sure they were all created.
  • Sort the folder by file size and look for reports that are exceptionally small. If unacceptable filters were used, the PDF may have saved a blank dashboard.
  • If updating/replacing an existing set of PDFs, sort the folder by date to quickly see which PDFs failed to run.
  • If PDFs frequently fail to run, one workaround is to increase the timeout setting. The better solution is to go back and optimize the workbook so Tableau Server can load the report faster.

More About the Author

Kent Sloan

Analytics Consultant
Basket Analysis Using Parameter Actions and LOD Calculations The initial request that led me to create this dashboard came from a client working in the sales department of a manufacturing company. ...
The 2019 Gartner BI Magic Quadrant Visualized in Tableau Not too long ago, Gartner released their updated Magic BI Quadrant for 2019. This quadrant ranks the industries top BI platforms on ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072