APPLICABLE to CLASSIC only.
Here are some goodies for people that have a need to export the data table underlying charts, or export PIVOT TABLES to excel. I know people have only been asking for this since 2015, so better late than never.
First, EXPORT PIVOT TABLE TO EXCEL. this JS will do it for you - all you need is the view number. It inserts a DOWNLOAD button in the header. Press that, and you have an Excel file that mimics the pivot table as its displayed. Couldn’t be simpler.
/* ============================================================
Export Knack Pivot Table to Excel (.xlsx)
============================================================
Requires: SheetJS (loaded dynamically below — reuses the same
window.XLSX instance if the chart export script already loaded it)
You need to add your own view number where it says view_XXX)
Button is inserted right after the view's <h3 class="kn-title is-4">
============================================================ */
(function () {
var REPORT_VIEW_KEY = 'view_XXX';
function loadSheetJS(callback) {
if (window.XLSX) return callback();
var script = document.createElement('script');
script.src = 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js';
script.onload = callback;
document.head.appendChild(script);
}
function exportPivotData($view, titleText) {
loadSheetJS(function () {
var $table = $view.find('table').first();
if (!$table.length) {
alert('Could not find the pivot table for export.');
return;
}
// Clone so we don't touch the on-screen table. Header cells that were
// stacked onto two lines (e.g. "30<br>06" for narrower date columns)
// read back as "3006" with no separator when table_to_book extracts
// text — rejoin any <br>-split header cell with "/" before exporting.
var $exportTable = $table.clone();
$exportTable.find('th').each(function () {
var $th = $(this);
if ($th.find('br').length) {
var parts = $th.html().split(/<br\s*\/?>/i).map(function (p) {
return $('<div>').html(p).text().trim();
});
$th.text(parts.join('/'));
}
});
var wb = XLSX.utils.table_to_book($exportTable[0], { raw: true });
var fileName = (titleText || 'pivot-data').replace(/[^\w\- ]+/g, '') + '.xlsx';
XLSX.writeFile(wb, fileName);
});
}
$(document).on('knack-view-render.' + REPORT_VIEW_KEY, function (event, view) {
var $view = $('#' + REPORT_VIEW_KEY);
var $title = $view.find('h3.kn-title.is-4').first();
if (!$title.length) {
console.warn('[Knack pivot export] h3.kn-title.is-4 not found in ' + REPORT_VIEW_KEY);
return;
}
// Avoid duplicate button on re-render
if ($title.next('.pl-pivot-export-btn').length) return;
var titleText = $title.text().trim();
var $btn = $('<button type="button" class="pl-pivot-export-btn">Download</button>')
.css({
marginLeft: '10px',
padding: '3px 10px',
cursor: 'pointer',
fontSize: '13px',
verticalAlign: 'middle'
});
$title.after($btn);
$btn.on('click', function () {
exportPivotData($view, titleText);
});
});
})();
So here are 2 more goodies for Pivot tables.
The first adds a row sum column to the right end of the pivot table - its always annoyed me that isn’t native out of the box functionality. And if you have column totals switched on, then it will sum those up into a grand total for you. Looks like this:
/* ============================================================
Add Row Total column to Knack Pivot Table (view_XXX)
============================================================
WHAT THIS DOES:
- Appends a "Total" column to the far right of the pivot table
- Header row gets a "Total" <th>
- Each data row gets a <td> = sum of that row's numeric cells
ASSUMPTIONS:
1. First cell in each data row is the row label (not a number) —
skipped when summing. If there are TWO label columns (e.g.
grouped rows), adjust FIRST_DATA_COL_INDEX below.
2. Header has a single row. If it's multi-row (nested/grouped
column headers with rowspan), the "Total" header cell needs a
rowspan matching the header depth — see the ADJUST note in
addHeaderCell().
3. If there's already a totals ROW at the bottom (column sums),
that row also gets a row-total cell (sum of the column totals),
4. Numbers may contain commas/currency symbols — the parser strips
non-numeric characters before summing.
============================================================ */
(function () {
var REPORT_VIEW_KEY = 'view_XXX';
var FIRST_DATA_COL_INDEX = 1; // 0-based; how many leading label columns to skip when summing
function parseNumber(text) {
var cleaned = (text || '').replace(/[^0-9.\-]/g, '');
if (cleaned === '' || cleaned === '-') return null;
var n = parseFloat(cleaned);
return isNaN(n) ? null : n;
}
function addHeaderCell($table) {
var $headerRows = $table.find('thead tr');
if (!$headerRows.length) return;
// ADJUST: if there are multiple header rows (nested grouped headers),
// this puts "Total" on the LAST header row only, with no rowspan —
// it will look mis-aligned. In that case set rowspan to the header
// row count so it spans the full header depth instead:
// $th.attr('rowspan', $headerRows.length);
// and only append it to the FIRST header row.
var $lastHeaderRow = $headerRows.last();
var $th = $('<th class="pl-row-total-col" style="text-align:right;">Total</th>');
$lastHeaderRow.append($th);
}
function addRowTotals($table) {
$table.find('tbody tr').each(function () {
var $row = $(this);
var $cells = $row.find('td');
var sum = 0;
var hasNumber = false;
$cells.each(function (i) {
if (i < FIRST_DATA_COL_INDEX) return; // skip label column(s)
var n = parseNumber($(this).text());
if (n !== null) {
sum += n;
hasNumber = true;
}
});
var display = hasNumber ? sum.toLocaleString() : '';
$row.append('<td class="pl-row-total-col" style="text-align:right;"><strong>' + display + '</strong></td>');
});
}
$(document).on('knack-view-render.' + REPORT_VIEW_KEY, function (event, view) {
var $view = $('#' + REPORT_VIEW_KEY);
var $table = $view.find('table').first();
if (!$table.length) {
console.warn('[Knack row total] No table found in ' + REPORT_VIEW_KEY);
return;
}
// Avoid double-adding on re-render
if ($table.data('pl-row-total-added')) return;
$table.data('pl-row-total-added', true);
addHeaderCell($table);
addRowTotals($table);
});
})();
The second is a bit weird, take it or leave it. I had a 60 column pivot table with the column labels being dates like 27/06. The date was much wider than the numbers below, so the date is effectively forcing the pivot table to be wider than needed. So I collapsed the header to 27 over 06, and make the 06 not bold, which compresses the pivot table enough to fit on a 27" screen without scrolling. If you want to do the same, here it is. It is also taken care of in the export code - the export puts the dates back together the way they are supposed to be for the excel file …
Looks like this:
/* ============================================================
PIPELINE — Stack DD/MM date headers onto two lines (view_1364)
============================================================
Where to paste: same JS pane as the other pivot scripts.
WHAT THIS DOES:
- Finds header cells whose text matches DD/MM (e.g. "23/05")
- Rewrites them as two stacked lines ("23" over "05") via <br>
- Centers the text so the two-digit stack looks balanced
- Leaves any non-date headers (row label column, "Total") alone
With ~60 date columns, this should meaningfully narrow each
column since "23/05" (5 chars wide) becomes two 2-char lines.
ASSUMPTIONS TO VERIFY:
1. Date headers are formatted exactly as D/M or DD/MM (no year,
no leading zero requirement either way — regex handles both).
If Knack ever renders a stray header like "Total" or a
"Q1" grouping label, it just won't match and is left as-is.
2. If the header cells have a fixed CSS max-width or nowrap rule
elsewhere in the app's stylesheet, the wrap won't visually
narrow the column even though the HTML is stacked — the
style.whiteSpace / textAlign set below should override that,
but check DevTools if the columns don't visibly shrink.
============================================================ */
(function () {
var REPORT_VIEW_KEY = 'view_1364';
var DATE_PATTERN = /^(\d{1,2})\/(\d{1,2})$/;
function stackDateHeaders($table) {
$table.find('thead th').each(function () {
var $th = $(this);
if ($th.data('pl-date-stacked')) return;
var text = $th.text().trim();
var match = text.match(DATE_PATTERN);
if (!match) return;
$th.html('<strong>' + match[1] + '</strong><br><span style="font-weight:normal;">' + match[2] + '</span>');
$th.css({
whiteSpace: 'normal',
textAlign: 'center',
lineHeight: '1.2'
});
$th.data('pl-date-stacked', true);
});
}
$(document).on('knack-view-render.' + REPORT_VIEW_KEY, function (event, view) {
var $view = $('#' + REPORT_VIEW_KEY);
var $table = $view.find('table').first();
if (!$table.length) {
console.warn('[PIPELINE date stack] No table found in ' + REPORT_VIEW_KEY);
return;
}
stackDateHeaders($table);
});
})();
And my final gift today is for CHARTS. This code hijacks the Download link on the chart and turns it into an Excel export of the underlying data table. To use it you need to turn on the data table and export options for the chart.
Note though that the code also hides that ugly data table and the button that toggles it, so its a clean Print and Download only like below. If for some reason you actually WANT that data table and its toggle link, set the variable to show it in first few lines of the code.
/* ============================================================
Export Knack Report/Chart data table to Excel (.xlsx)
============================================================
Requires: SheetJS (loaded dynamically below, no manual install)
STATUS: REPORT_VIEW_KEY (view_1363) and NATIVE_EXPORT_SELECTOR
(.kn-report-download) are both confirmed against the live DOM —
no changes needed to those two.
APPROACH: rather than adding a second button, this clones the
native export link (which strips any click handlers Knack bound
to it) and swaps in our own handler — so the existing button
just does the right thing.
TABLE INITIALISATION: Knack doesn't build the data table until
"show data table" (.kn-report__data-table-toggle) has been
clicked at least once. On export click, we now: (1) mask the
table area with CSS so it can never be visibly seen regardless
of timing, (2) click the toggle to force Knack to build/reveal
it, (3) poll briefly until rows appear, (4) export, (5) click
the toggle again ONLY if we were the one who triggered it, to
restore whatever state the user had it in, (6) remove the mask.
============================================================ */
(function () {
var REPORT_VIEW_KEY = 'view_XXX';
var NATIVE_EXPORT_SELECTOR = '.kn-report-download';
var TABLE_TOGGLE_SELECTOR = '.kn-report__data-table-toggle';
var HIDE_DATA_TABLE_TOGGLE = true; // set to false to leave the native "show data table" link visible to users
function loadSheetJS(callback) {
if (window.XLSX) return callback();
var script = document.createElement('script');
script.src = 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js';
script.onload = callback;
document.head.appendChild(script);
}
// Ensures the data table exists and has rows, without ever letting it be
// visibly seen, then calls back with whether WE triggered the toggle
// (so the caller knows whether to toggle it back off afterward).
function ensureTableReady($view, callback) {
var $existingTable = $view.find('table').first();
var alreadyPopulated = $existingTable.length && $existingTable.find('tr').length > 0 && $existingTable.is(':visible');
if (alreadyPopulated) {
callback(false); // nothing to do, don't touch toggle state afterward
return;
}
var $toggle = $view.find(TABLE_TOGGLE_SELECTOR).first();
if (!$toggle.length) {
// No toggle found — proceed with whatever's already there.
callback(false);
return;
}
// Mask: force anything table-like in this view to stay invisible and
// out of layout flow no matter what Knack's own show/hide logic does,
// so there's no flash regardless of whether the build is sync or async.
var maskId = 'pl-export-mask-style';
if (!document.getElementById(maskId)) {
var style = document.createElement('style');
style.id = maskId;
style.textContent =
'.pl-export-masking table, .pl-export-masking .kn-table-wrapper ' +
'{ visibility: hidden !important; position: absolute !important; ' +
'left: -9999px !important; top: -9999px !important; }';
document.head.appendChild(style);
}
$view.addClass('pl-export-masking');
$toggle[0].click(); // trigger Knack's native build/show handler
var attempts = 0;
var maxAttempts = 30; // ~3s at 100ms polling
var poll = setInterval(function () {
attempts++;
var $t = $view.find('table').first();
var ready = $t.length && $t.find('tr').length > 0;
if (ready || attempts >= maxAttempts) {
clearInterval(poll);
callback(true); // we triggered it — caller should toggle back off
}
}, 100);
}
function restoreToggleState($view) {
var $toggle = $view.find(TABLE_TOGGLE_SELECTOR).first();
if ($toggle.length) {
$toggle[0].click(); // hide again, back to the user's original state
}
$view.removeClass('pl-export-masking');
}
function exportChartData($view) {
ensureTableReady($view, function (weTriggeredToggle) {
loadSheetJS(function () {
// ADJUST: if the view has more than one <table>, narrow this selector
// (e.g. '.chart-data-table table') to target the right one.
var $table = $view.find('table').first();
if (!$table.length) {
alert('Could not find the data table for this chart.');
if (weTriggeredToggle) restoreToggleState($view);
return;
}
var wb = XLSX.utils.table_to_book($table[0], { raw: true });
var title = $view.find('.view-header h2, h2.kn-title, h3.kn-title')
.first()
.text()
.trim() || 'chart-data';
XLSX.writeFile(wb, title.replace(/[^\w\- ]+/g, '') + '.xlsx');
if (weTriggeredToggle) restoreToggleState($view);
});
});
}
$(document).on('knack-view-render.' + REPORT_VIEW_KEY, function (event, view) {
var $view = $('#' + REPORT_VIEW_KEY);
// Hide the "show data table" toggle from users — it's now only
// triggered programmatically by the export, so there's no reason
// for people to see or click it themselves (unless HIDE_DATA_TABLE_TOGGLE
// is set to false).
if (HIDE_DATA_TABLE_TOGGLE) {
$view.find(TABLE_TOGGLE_SELECTOR).first().css('display', 'none');
}
var $native = $view.find(NATIVE_EXPORT_SELECTOR).first();
if (!$native.length) {
console.warn('[Knack export] Native export link not found — check NATIVE_EXPORT_SELECTOR.');
return;
}
// Skip if we've already hijacked this element on a previous render
if ($native.data('pl-hijacked')) return;
// Clone-and-replace strips out any click handlers Knack bound directly
// to the element (jQuery delegated-on-document handlers would survive
// a plain preventDefault, so this is the reliable way to fully own it).
var $clone = $native.clone(false, false); // no data/events copied
$clone.data('pl-hijacked', true);
$native.replaceWith($clone);
$native = $clone;
// Belt-and-braces: also stop the event outright in case anything
// above us in the DOM still has a delegated handler on this selector.
$native.on('click', function (e) {
e.preventDefault();
e.stopImmediatePropagation();
exportChartData($view);
return false;
});
});
})();
So that’s it from me for now - I hope someone else gets some value from this. Happy Knacking!
Leigh