Flexible table component / Pivot Table

Feature Requests
19

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):

image

Query 1 is a select from a SQL db with a formatDataAsArray in the transformer of the query:

image
image993×97 7.46 KB

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:
image

image
image1239×118 7.63 KB

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.

image

Hope that all helps someone, I've been trying to get this working for ages :frowning:

4 Likes