Introduction

Tableau Export allows one to specify one or more Tableau reports to be exported using tabcmd. The specification for each report, which can be thought of as a row in a spreadsheet or a database, defines the contents and format of the report to be exported as well as the email address(es) of the recipient(s).

Tableau Export is controlled by a JSON configuration that is passed in on the command line or provided via the Mitto user interface. The JSON configuration contains a basic configuration for the application as well as a configuration for the reports specifications.

Configuration

Conceptually, the configuration comprises two major parts. The first part contains basic Tableau server information as well as SMTP information. The second part contains the specifications for one or more reports to be exported and the email addresses to which they should be mailed.

Minimal Basic Configuration

A minimal basic configuration containing only mandatory items might look something like this:

{
	"email": null,
	"smtp": {
		"port": "0",
		"require_tls": "false",
		"server": "localhost",
		"username": "mitto@akiri.co"
	},
	"tabcmd": {
		"no_certcheck": "true",
		"password": "userpass",
		"server": "https://tableau.zuar.com",
		"user": "username"
	}
}

Note: the email section is described later.

All Basic Configuration Options

All of the possible configuration options are shown here:

{
	"email": null,
	"smtp": {
		"password": null,
		"port": null,
		"server": null,
		"username": null,

		"require_tls": null
	},
	"tabcmd": {
		"password": null,
		"server": null,
		"user": null,

		"cmdpath": null,
		"no_certcheck": null,
		"proxy": null,
		"retries": null,
		"site": null,
		"timeout": null,

		"body": null,
		"from": null,
		"height": null,
		"pagelayout": null,
		"pagesize": null,
		"report_name": null,
		"report_type": null,
		"subject": null,
		"view": null,
		"width": null
	}
}

smtp Configuration

The smtp configuration in the previous section is the one to use if you wish to send mail using the postfix server on the Mitto instance. If you wish to send email using a different SMTP server, you will likely need to provide all of the information shown in the smpt section. "require_tls": "true" directs the client to use START_TLS to establish a secure connection. If require_tls is used, port should be the standard non-TLS port of the SMTP server -- do not use the standard TLS port.

On a default Mitto instance, the following SMTP configuration can be used:

"smtp": {
	"port": "0",
	"server": "localhost",
	"require_tls": "false"
},

tabcmd Configuration

There are three groups of key/value pairs in the tabcmd section above. The first group (password, server, and user) are mandatory; the second and third groups are optional. Brief descriptions follow:

cmdpath specifies a non-default tabcmd to use for the job.

no_certcheck, proxy, and site have the meanings shown on the tabcmd main page. Although optional, you will likely need to use "no_certcheck": "true".

tabcmd sometimes fails due to a lack of resources. At other times, it may run for longer than desired lengths of time. The timeout parameter is the number of seconds that tabcmd will be allowed to run before the system kills it. The default is 30 seconds. The retries parameter is the number of times that a failed tabcmd will be retried before moving on to the next report specification. These are useful on a heavily loaded system.

The third group of key/value pairs (body, from, height, pagelayout, pagesize, report_name, report_type, subject, view, and width) are optional and can be used to provide defaults for report specifications. Their use is discussed in the next section.

Report Specifications

A report specification defines the contents of a report as well as its recipients. Report specifications are provided to Tableau Export in one three ways: a JSON string, a path to a CSV file, or a dbo and a SQL query. Regardless of how the specifications are provided, they contain the same information, which this section describes.

Contents of a Report Specification

Twelve attributes can be used to define a report specification. As previously mentioned, default values can be provided for many of the attributes.

body (optional)

String containing HTML which will be used as the body of the email.

Example: <html><head>CEO Report</head><body>Report attached.</body></head></html>

bcc, cc (optional)

Same as to.

filters (optional)

filters is a string containing one or more key/value pairs, with each key/value pair separated by ampersands (&). Both keys and values may contain spaces and commas. E.g., the following value for filters contains three key/value pairs:

Key1=one&Key2=two&Key3=three

Do not manually encode spaces as %20 -- Tableau Export does the necessary encoding itself. Some browsers (at least Firefox) display spaces in the URL in the address bar. However, if one copies the contents of the address bar and pastes it into a document, the pasted value contains spaces encoded as %20. Be careful when cutting and pasting -- be sure your filter contains actual spaces rather than encoded spaces (%20).

For example, if URL is:

https://tableau.zuar.com/#/views/Superstore/Overview?State=Texas&Segment=Home Office&:iid=5

then filters = State=Texas&Segment=Home Office

Rules for creating filters:

  1. If a key or a value contains an ampersand, the ampersand must be escaped by a backslash.
  2. If multiple values are provided for a key, the values must be comma separated.

There are two special cases concerning values in 'filters':

  1. A value can contain the & character. Such embedded ampersands must be escaped by a backslash (\) in order to distinguish them from an & used to separate key/value pairs. E.g., CompanyName=Foo \& Bar Company.
  2. Keys can have multiple values. Multiple values must be separated by a comma. Note that a value can contain a comma. When a value contains an embedded comma, it must be escaped by a backslash. E.g., KeyWithMultiVals=one,two,three or SingleValueWithCommas=Foo\, Bar\, Baz. Inc..

Of course, they can be combined: HasBoth=Foo\, Bar\, \& Co.,Gnu\, Inc.&foo=bar.

from (optional)

A string containing an email address. The address will appear in the From: field of the outgoing email.

height, width (optional)

Only valid if report_type is png. The height and width, in pixels, of the exported image.

report_type (optional)

One of the following strings: pdf | csv | png | fullpdf.

report_name (optional)

This is the name given to the exported report when it is attached to the email. When the recipient saves the attachment, it will be saved using this name. Do not provide an extension (e.g., use ceo_report instead of ceo_report.pdf; the correct extension will be provided by Tableau Export.

subject (optional)

A string used as the subject of the outgoing mail.

to (required)

A string containing one or more comma-separated email addresses. The report will be emailed to the address(es).

Example: alice@foo.com, bob@foo.com,ted@bar.com

view (optional)

String containing the Tableau view to use when creating the report. Views may contain spaces. If a report specification does not specify a view, a default view must be provided in the tabcmd section.

Example:

If URL = https://tableau.zuar.com/#/views/Superstore/Overview?:iid=1

Then view = Superstore/Overview

Providing Report Specifications via CSV

To provide report specifications entirely from the JSON config, create an email section that looks something like the following:

"email": {
	"type": "json",
	"reports": [
		{"to": "ke@east.fm"
		},
		{"subject": "Daily CEO Report",
		 "to": "ke@east.fm",
		 "report_type": "fullpdf"
		},
		{"view": "Superstore/Product",
		 "subject": "Daily Home Office / Office Supplies Report",
		 "to": "ke@east.fm",
		 "filter": "Category=Office Supplies&Segment=Home Office"
		 "report_type": "pdf",
		},
		{"view": "Superstore/Overview",
		 "subject": "Daily Texas Data CSV",
		 "to": "ke@east.fm,KHEAustin@gmail.com",
		 "filter": "State=Texas",
		 "report_type": "csv",
		}
	]
}

Notes For Developers

Installing tbcmd

Only tableau-tabcmd-2018-3-2_all.deb has been tested. It is available here.

sudo dpkg -i tableau-tabcmd-2018-3-2_all.deb

Installing Java

if java is not present on the system, or if the existing java has problems (including certificate problems):

sudo apt install openjdk-8.jre-headless

Adding a Tableau Export Job to Mitto

Before a job can be run, it must first be created on Mitto.

  1. Navigate to https://server.zuarbase.net/#!/api
  2. In the URL text box, enter: /api/jobs. This is the Mitto API endpoint that will be used to create the job.
  3. Add the following JSON to the Data tab:
{
  "name": "tableau_export_job",
  "title": "Tableau Export Job",
  "type": "tableau_export"
}

name is used to identify the job internally within Mitto.
title is the text that will be displayed in the list of jobs.
type is the linkage to the python script that implements the job (in this case, job_tableau_export.py)

  1. Click POST. This posts the data entered above to the URL, thereby creating the job within Mitto.

Configuring and Running the Tableau Export Job

  1. Navigate to https://server.zuarbase.net.
  2. Click on Jobs.
  3. In the search box, enter the name of the job you created (e.g., Export Job).
  4. Click on the job. If the job does not appear in the list, there was likely an error in creating the job.
  5. Click on Edit. An empty JSON tab should appear. The entire configuration for the job goes here. The remainder of this document describes the configuration.
  6. When the configuration is complete, click Save.
  7. Click Start to run the job.

Blank Templates

Tableau Export can be used to create blank templates for the three types of report configurations. The following command will create the files job_tableau_export_example-json.json, job_tableau_export_example-csv.json, job_tableau_export_example-sql.json, and job_tableau_export_example-csv.csv in ~/jte.

mkdir ~/jte
python3 jobs/job_tabcmd_export.py --name tabcmd --write-examples ~/jte

These can be useful as starting points for any of the configurations presented.

Commandline Usage

Assuming that a JSON configuration exists in ~/jte/job_tableau_export.json, reports can be run via command line via:

python3 jobs/job_tableau_export.py --name tabcmd ~/jte/job_tableau_export.json