Tableau 10’s JavaScript API has a new getData interface which allows you to get summary and underlying data directly as JavaScript objects. I’ve already shown a use case to use these methods for simply grabbing values. But a wider use case is creating an HTML table from any Tableau visualization, particularly if 508 compliance requires that you make all data available via a screen reader. Even if you don’t need compliance, you may just want to control how Tableau displays its underlying data. The full working example is here on GitHub.

 Working with getData

getData (whether getSummaryDataAsync() or getUnderlyingDataAsync() ) returns a SheetData object which has the methods you’ll need to get to the actual data. There is SheetData.getData(), which returns a two-dimensional array of Marks objects without any column information, and there is SheetData.getColumns(), which is an ordered array of ColumnData objects.

To get a basic HTML table, or the kind of simple JSON array that will load most easily into tools like jQuery DataTables, we’ll need to run through these more complex objects and convert into simpler arrays, making a few choices along the way.

Getting the Column names

Each ColumnData object has a getFieldName() method. There’s also getDataType() and getIsReferenced(), which might be useful for creating an export of a certain type, or only showing underlying data that is referenced. To get a simple array of all the column names, the following will work:

// Returns a simple ordered array of the column / field names
function convertColumnsObjectToArrayOfNames(sheetDataObj){
col_obj = sheetDataObj.getColumns();
col_array = new Array();
for (var k=0; k < col_obj.length; k++){
col_array[k] = col_obj[k].getFieldName();
}
console.log(col_array);
return col_array;
}


DataTables has a more complex way of defining information about columns, but at the most basic level, you need to define a title property, so you make an array of objects:

// jQuery DataTables columns takes an array of objects, each with a title property
function convertColumnsObjectToDataTablesConfigOptionArray(sheetDataObj){
col_obj = sheetDataObj.getColumns();
col_array = new Array();
for (var k=0; k < col_obj.length; k++){
col_array[k] = { title : col_obj[k].getFieldName() };
}
console.log(col_array);
return col_array;
}


Getting the Data
As I mentioned earlier, the SheetData.getData() method returns an array (rows) of arrays (columns), which is a natural representation of a table. However, each ‘cell’ is actually a Marks object, and thus two properties to choose from: formattedValue and value. To convert to an array of pure values, we have to choose which of these we’ll want to keep.

/*  getData() returns an array (rows) of arrays (columns) of objects, which have a formattedValue and a value property.
This function lets you pick either 'formatted' or 'value' and then returns a pure array of arrays with
actual values in the columns array. Useable for pure HTML table or for jQuery DataTables
*/
function convertTableObjectToArray(sheetDataObj, formatted_or_value){
// This is an array of objects,
data = sheetDataObj.getData();
final_table = new Array();
console.log(data.length);
for(var i=0; i < data.length; i++){
final_table[i] = new Array();
for(var j=0; j < data[i].length; j++){
if (formatted_or_value.toLowerCase() === 'formatted'){
final_table[i][j] = data[i][j].formattedValue;
}
else if (formatted_or_value.toLowerCase() === 'value'){
final_table[i][j] = data[i][j].value;
}
}
}
return final_table;
}


Building a Simple HTML table
At this point, we’ve worked through the object(s) Tableau Server returns. Now let’s put it together into an HTML table. While the DataTables version built directly via JSON may be look nicer and have fancy features, if you are trying to achieve accessibility for those with visual impairments, the simpler table will likely present something more easily read by a screen reader or exported.
Creating an HTML table from an object isn’t rocket science and I won’t claim any credit for myself in this portion. I patterned the following off the solution at Stack Overflow , with the addition of the thead portion.

function create_html_table_element_from_tableau_object(sheetDataObj, formatted_or_value){
var column_header_array = convertColumnsObjectToArrayOfNames(sheetDataObj);
var table_body_array = convertTableObjectToArray(sheetDataObj, formatted_or_value);

var table = document.createElement('table');
var tableHead = document.createElement('thead');
var th = document.createElement('th');
for(var i=0; i < column_header_array.length; i++){
var td = document.createElement('td');
td.appendChild(document.createTextNode(column_header_array[i]));
th.appendChild(td);
}
tableHead.appendChild(th);
table.appendChild(tableHead);

var tableBody = document.createElement('tbody');

table_body_array.forEach(function(rowData) {
var row = document.createElement('tr');

rowData.forEach(function(cellData) {
var cell = document.createElement('td');
cell.appendChild(document.createTextNode(cellData));
row.appendChild(cell);
});

tableBody.appendChild(row);
});

table.appendChild(tableBody);
return table;

}


This returns a DOM table object, which we’ll need to place into the page somehow. This function wraps it all up:

function buildSimpleHTMLTable(sheetDataObj, formatted_or_value, element_id_to_insert_into){
var table = create_html_table_element_from_tableau_object(sheetDataObj, formatted_or_value);
table.id = 'myGreatTable';
// Add any other properties you'd like here
document.getElementById(element_id_to_insert_into).appendChild(table);
}


jQuery DataTables with JavaScript Objects
I’ve loved the DataTables plugin for jQuery for years — for pure HTML table goodness, nothing can match it. You can use DataTables to convert to an HTML table that is already inserted into the page, but an alternative method allows you to give the DataTables object the JavaScript objects that contain the info about the columns and the table data and have it build the whole thing for you.
To do this, you need to have an existing table tag in the page for DataTables to use; it should be totally empty.
<div id='dataTablesDiv'>
<h3>DataTables table</h3>
<table id="dtTable" class="display" cellspacing="0" width="100%"></table>
</div>

Here’s the JS function to build out the table:

function buildDataTablesTable(sheetDataObj, formatted_or_value, empty_table_id){
var column_header_array = convertColumnsObjectToDataTablesConfigOptionArray(sheetDataObj);
var table_body_array = convertTableObjectToArray(sheetDataObj, formatted_or_value);
try {
$ ('#' + empty_table_id).DataTable(
{
data: table_body_array,
columns: column_header_array
});
}
catch (err){
alert(err);
}
}


Extension Ideas
Once you have your simple table worked out, you could instead serialize it to JSON and POST it back to a process that does just about anything.
You might use something like jsPDF with AutoTables to throw a PDF export of all the underlying data right out at will. More on this to come…
Share this:
Twitter
Facebook
Like this:
Like Loading...


	
Related