Hello anyone interested in pivot tables.
I finally got this working myself with pivottable.js. Just a few tweaks (and some understanding) required in addition to the example above from chunyau (there was a rogue comma amongst other things).
My model looks like this (ignore the x_hello, am using for debugging):
Query 1 is a select from a SQL db with a formatDataAsArray in the transformer of the query:
Then this is the iframe. The problem I was making was not putting the pivotUI function within window.Retool.Subscribe function. I am sure there are other ways to overcome that but this is simple and works.
Obviously the rows, cols, vals are specific to my query.
<!DOCTYPE html>
<!-- saved from url=(0058)http://horner.github.io/pivottable/examples/simple_ui.html -->
<html><head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Pivot Demo</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js" integrity="sha512-J9QfbPuFlqGD2CYVCa6zn8/7PEgZnGpM5qtFOBZgwujjDnG5w5Fjx46YzqvIh/ORstcj7luStvvIHkisQi5SKw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js" integrity="sha512-cViKBZswH231Ui53apFnPzem4pvG8mlCDrSyZskDE9OK2gyUd/L08e1AC0wjJodXYZ1wmXEuNimN1d3MWG7jaQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.js" integrity="sha512-XgJh9jgd6gAHu9PcRBBAp0Hda8Tg87zi09Q2639t0tQpFFQhGpeCgaiEFji36Ozijjx9agZxB0w53edOFGCQ0g==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.23.0/pivot.min.css" integrity="sha512-BDStKWno6Ga+5cOFT9BUnl9erQFzfj+Qmr5MDnuGqTQ/QYDO1LPdonnF6V6lBO6JI13wg29/XmPsufxmCJ8TvQ==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<style>
* {font-family: Helvetica;}
</style>
</head>
<body>
<script>
function buttonClicked() {
window.Retool.triggerQuery('query1')
}
</script>
<script type="text/javascript">
window.Retool.subscribe(function(model) {
// subscribes to model updates
// all model values can be accessed here
var x_data = model.x_source_data
$(function(){
$("#output").pivotUI(
x_data,
{
rows: ["viewpoint_level1","viewpoint_sequence","a_h_plot_name","viewpoint_level2_sequence","viewpoint_level2","plot_field_name","record_type"],
cols: ["a_h_month_formatted"],
vals: ["field_value_comparison"],
aggregatorName: ["Sum"]
}
)})
});
</script>
<button onclick="buttonClicked()">Trigger Query</button>
<div id="output" style="margin: 10px;"></div>
</body></html>
Lastly - the whole pivot was working in edit mode but simply not appearing when not in edit mode.
I created this JS query, and set it to run on page load on a 1500 ms delay:
However that didn't always work, as query1 needs to have finished. So I added a little link to re-run that query. That fixed it.
Hope that all helps someone, I've been trying to get this working for ages