Displaying charts to users from a pipe result

slimpens · September 23, 2023, 7:06am

Hi all,

I am having an issue regarding showing data to users in charts. Since I am unable to find a solution untill now, maybe someone else in the community has an idea how to do this:

Outline:
My user can view a movement schedule for 20 years. This movement schedule is generated as a pipe result and is individual to each user. Therefore it contains 20 field values (numbers).

In order to present this to the user in a nice view, a chart would be great. The x-axis should reflect the year (e.g. year 1 to year 20) and the y-axis should reflect the numbers. The results are showed on a details page. In order to show this I can choose 2 options:

Option1 :
Use the default TB charts. Unfortunately, this is not possible. The chart component requires that the data shown is based on colum data. To generate the designated results: I would need to run a pipe, add/ insert a (connected table) row, name the row year 1 and record the pipe result (number) to a column.

If I wanted to do this for 20 years, I need to run the pipe 20 consecutive times (and simultaneously add 20 rows to the connected table). Though this is very time consuming for TB, it is also not possible to add multiple records containing different data ( the values from each year vary) in a single pipe request.
So as far as my knowledge goes, this is not possible.

Option2:
The other option is use the Apex Charts option. Since this enables me to manually manipulate the data in custom JS, I can now add one row with 20 field values (one row containing all the values). Working through this way. I can use the pipe, since the results can be added through a singe pipe request. This is because I show the data on a details page.
However, even though @moe did a great video on adding the Apex charts, the result is that the numbers in the table are not showing the same as in the TB currency fields (e.g. format as Euro, a dot as thousand separator and no decimals).

The custom JS I added contains the intl number format and min/ max digits, but unfortunately I am not able to customize this. I have added the custom JS code below:

So, untill now I haven’t been able to find a solution which is both suitable for TB and which is user friendly.

@SafetyUniversity Adam: do you have any recommendations based on your experience with TB Charts, Apex charts or Chart.js?
@GREDDIE , @Shumon : as experienced users, I am very interested in your opinion on this issue.

For the post containing the JS code and image of the current results: check https://community.tadabase.io/t/advanced-charts/2203/9?u=slimpens

format

var options = {
          series: [{
          name: 'HYPOTHEEK',
          data: ["{pageField.Hypotheek}", "{pageField.Hypotheek}", "{pageField.Hypotheek}"]
          
        }, {
          name: 'VRIJ TE MAKEN',
          data: ['{pageField.Xoutput_QSmaximaalmetmaandlast}','{pageField.Xoutput_QSmaximaalmetmaandlastbox3}','{pageField.Xoutput_QSsociomaximaal}','{pageField.Xoutput_QSmaximaallevius}']
          
      
        }],
          chart: {
          type: 'bar',
          height: 350,
          stacked: true,
          toolbar: {
            show: true
          },
          zoom: {
            enabled: true
          }
        },
        responsive: [{
          breakpoint: 480,
          options: {
            legend: {
              position: 'bottom',
              offsetX: -10,
              offsetY: 0
            }
          }
        }],
        plotOptions: {
          bar: {
            horizontal: false,
            borderRadius: 10,
            dataLabels: {
              total: {
                enabled: true,
                style: {
                  fontSize: '20px',
                  fontWeight: 900
                }
              }
            }
          },
        },
        xaxis: {
          categories:['{pageField.Xinput_naamaanbieder}-eigen woning','{pageField.Xinput_naamaanbieder} - vrije besteding','Overwaarde zonder maandlast', 'Verkoop en terughuur'
          ],
        },
        legend: {
          position: 'right',
          offsetY: 40,
          fontSize: '20px'
        },
        fill: {
          opacity: 1
        }
        };
        var chart = new ApexCharts(document.querySelector("#chart"), options);
        chart.render();
        
    var formatter = new Intl.NumberFormat('de-DE', {
      style: 'currency',
      currency: 'EUR',
      minimumFractionDigits: '0',
     maximumFractionDigits: '0',

Regards
Martin

Shumon · September 29, 2023, 10:32pm

have you tried these options:

calender view
table containg Roll-up calculations which are then used to create the chart
Cross-tab
A secondary table containing duplicate records created from action buttons/ record rules along the way. This table holds the same data but in structure that is tb chart friendly.
Block of Cards with filters.

SafetyUniversity · October 1, 2023, 6:15am

@kruizf201

kruizf201 · October 2, 2023, 12:56am

Hi @slimpens @SafetyUniversity

Next is an example using custom component:

Demo: https://tadabase.advertscience.com/simplens-chart

Library: https://cdn.jsdelivr.net/npm/chart.js

Code:


{{!--{{#each records}}--}}

{{!--<!-- Select fields from the left to be added here -->--}}

{{!--{{/each}}--}}


<canvas id="myChart" width="400" height="200"></canvas>

<script>
    // Your JSON data
    {{#each records}}
    var jsonData = {
      "data": [
        { "year": 1, "value": 85 },
        { "year": 2, "value": 45 },
        { "year": 3, "value": 12 },
        { "year": 4, "value": 78 },
        { "year": 5, "value": 42 },
        { "year": 6, "value": 42 },
        { "year": 7, "value": 50 },
        { "year": 8, "value": 58 },
        { "year": 9, "value": 67 },
        { "year": 10, "value": 74 },
        { "year": 11, "value": 85 },
        { "year": 12, "value": 52 },
        { "year": 13, "value": 16 },
        { "year": 14, "value": 115 },
        { "year": 15, "value": 76 },
        { "year": 16, "value": 57 },
        { "year": 17, "value": 69 },
        { "year": 18, "value": 79 },
        { "year": 19, "value": 78 },
        { "year": 20, "value": 85 }
      ]
    };

   // Extract the x and y data
    var years = jsonData.data.map(item => item.year);
    var values = jsonData.data.map(item => item.value);
    
    var ctx = document.getElementById('myChart').getContext('2d');
    var myChart = new Chart(ctx, {
      type: 'bar', // Change the chart type to 'bar'
      data: {
        labels: years, // X-axis labels
        datasets: [{
          label: 'Data',
          data: values, // Y-axis values
          backgroundColor: 'rgba(75, 192, 192, 0.6)', // Bar color with alpha (transparency)
          borderWidth: 2
        }]
      },
      options: {
        scales: {
          x: {
            type: 'linear',
            position: 'bottom',
            title: {
              display: true,
              text: 'Year'
            }
          },
          y: {
            beginAtZero: true,
            title: {
              display: true,
              text: 'Value'
            }
          }
        }
      }
    });
    {{/each}}
</script>

slimpens · October 2, 2023, 4:38pm

Wow @kruizf201 ,

Thanks very much for the reply and supported code !

slimpens · October 24, 2023, 11:17am

kruizf201:

// Extract the x and y data
    var years = jsonData.data.map(item => item.year);
    var values = jsonData.data.map(item => item.value);
    
    var ctx = document.getElementById('myChart').getContext('2d');
    var myChart = new Chart(ctx, {
      type: 'bar', // Change the chart type to 'bar'
      data: {
        labels: years, // X-axis labels
        datasets: [{
          label: 'Data',
          data: values, // Y-axis values
          backgroundColor: 'rgba(75, 192, 192, 0.6)', // Bar color with alpha (transparency)
          borderWidth: 2
        }]
      },
      options: {
        scales: {
          x: {
            type: 'linear',
            position: 'bottom',
            title: {
              display: true,
              text: 'Year'
            }
          },
          y: {
            beginAtZero: true,
            title: {
              display: true,
              text: 'Value'
            }
          }
        }
      }
    });
    {{/each}}
</script>

@kruizf201

Just one question, how can I change the x-label to text? I want to show a date in the format of " 31-12-2023" , but when i replace the value of e.g. 1 to 31-12-2023, the label is not shown…