DataView for Chart

[Gavin Dundee]

I am seeking help.  Here is my datatable setup:

  var grouped_data = google.visualization.data.group(

    proxyDataTable,

    [yearIndex],

    [

      {'column': sschIndex, 'aggregation': google.visualization.data.sum, 'type':'number'},

      {'column': enrollmentsIndex, 'aggregation': google.visualization.data.sum, 'type':'number'},

      {'column': crnIndex, 'aggregation': google.visualization.data.count, 'type':'number'}

    ]

  );

Now I am creating a view:

var view = new google.visualization.DataView(grouped_data);

Then I draw the view:

view.setColumns([[0,1],[0,1],[0,1]]);

chart0.setDataTable(view);

chart0.draw();

My issue is with setColumns().  There are 3 columns being displayed.  I thought I would be able to use this code:
view.setColumns([1,2]);
To display only columns 1 and 2 and not show column 3.  My assumption is that [1,2,3] would show all the columns, but that does not work either.  I am not sure where to go from here I have tried setColumns with many different arrays, but I am just guessing at this point.  Can anyone help determine how I could hide a column using setColumns?

I attached a screenshot of what my chart looks like.

Thanks in advance for any help!!

[Gavin Dundee]

With array inputs:
[0,1,2]
or
[0,1,3]

I usually get an error like this:
Invalid column index 3. Should be an integer in the range [0-2].  

If I set the array to:
[0,1,2,3]
I get all three columns per row.

I am still at a loss.  Any help would be greatly appreciated! :)

[Alvin Galit]

[0,1, 2] doesn't work? Index starts at [0] and since you have 3 columns, I thought maybe that should be working. 

It makes sense that [0, 1, 3] doesn't work because you only have 3 columns and so the 3rd column is at index [2].

[Gavin Dundee]

It really doesn't work.  I truly am puzzled by this.  I am a newbie to this though.

[Alvin Galit]

Are you able to share your whole code?

[Gavin Dundee]

I finally got it figured out.  Column charts cannot change the number of columns.  I had to build the view with hidden columns.  Thanks for taking a look at this Alvin!

  Here is my new helper function:

//returns a view with hidden columns

//expects a Google Charts dataTable and an array of indexes of the desired columns to show

function buildViewWithSpecifiedColumns(dataTable, visibleIndexes) {

  const totalCols = dataTable.getNumberOfColumns();

  const viewColumns = [];

  for (let i = 0; i < totalCols; i++) {

    if (visibleIndexes.includes(i)) {

      viewColumns.push(i);

    } else { //the column should be hidden

      viewColumns.push({

        type: dataTable.getColumnType(i),

        label: dataTable.getColumnLabel(i),

        calc: () => null

      });

    }

  }

  const view = new google.visualization.DataView(dataTable);

  view.setColumns(viewColumns);

  return view;

}

Here is the updated code to redraw the chart:

//logic to show/hide columns when clicking on the legend

  google.visualization.events.addListener(chart0, 'select', function () {

    var sel = chart0.getChart().getSelection();

    if ( sel.length > 0 ) { //either a legend item or a chart element has been selected

      if ( sel[0].row === null ) { //legend has been clicked

        var selectedCol = sel[0].column;

        //toggle column visibility in the activeColumns array

        var indexInactive = activeColumns.indexOf(selectedCol); //get the index in the array for the selected column

        if (indexInactive > -1) { //if it is -1 it was not in the active columns array

          activeColumns.splice(indexInactive, 1); //remove if present (hide)

        } else {

          insertSorted(activeColumns, selectedCol); //add column if not present (show)

        }

      }

    } // else //an element in the chart has been deselected

    const view = buildViewWithSpecifiedColumns(grouped_data, activeColumns);

    chart0.setDataTable(view);

    chart0.draw();

  });

[Gavin Dundee]

I sent my whole code earlier.  If you did not get it, please let me know.

Update:
I tried the removeColumn() method, but I still get the same results.  It is doing the same as the setColumn.  I verified this with the getViewColumns() method.

I also am able to duplicate columns.  If I do this:
view.setColumns([0,1,2,2])

It does actually produce column 2 twice which makes column 3 not visible any longer.  It also updates the legend.  I have attached a photo of this behavior.

[Alvin Galit]

Hey Gavin. I think I just see snippets of your code. I can take a closer look if you provide the whole file.

[Gavin Dundee]

I'm not sure why it never sent my message with my code attached.  I have attached it here.  Like I said though, I got it figured out.  Column charts cannot have the number of columns hidden.  They must be psuedo hidden.  I have attached my code here in case you still want to take a look!  Thanks for being willing to help.  My faith in humanity was boosted because of you, Alvin. <3

var params = new URLSearchParams(window.location.search);

var data = null;

var options = null;

var proxyTable = null;

let proxyDataTable = null;

var chart0 = null;

var filters = [];

//variables for showing/hiding columns

var activeColumns = [];

let chartSelectListener = false; //prevent duplicate listeners from being added

$(function() {

  google.charts.load('current', {'packages':['corechart','controls']});

 

  google.charts.setOnLoadCallback( function() {

    getData()

    .done(function (response) { //runs once response is returned from getData()

      let processedData = processData(response); //add new rows (Fall, Spring, Academic Year)

      drawChart(processedData);

    })

    .fail(function(jqXHR, textStatus, errorThrown) {

      console.error("Error fetching data:", textStatus, errorThrown);

      $('#loading').text("Error loading data."); // Update loading message on error

    })

    .always(function() {

      $('#loading').remove(); // Remove loading indicator once done (success or fail)

    });

  });

  $('#contents').append('<div style="display: none;" id="proxyTable"></div>'); //add the proxyTable to the DOM because it is necessary for Google Charts to operate properly

 

  //make the chart auto resize based on window size

  $(window).resize(function() {

    if(this.resizeTO) clearTimeout(this.resizeTO);

    this.resizeTO = setTimeout(function() {

      $(this).trigger('resizeEnd');

    }, 500);

  });

  //redraw graph when window resize is completed

  $(window).on('resizeEnd', function(e) {

      chart0.draw();

  });

});

function getData() {

  $('#ef_left').append('<div id="loading" style="width: 100%; text-align: center;">Loading</div>');

  const baseDataURL = window.location.pathname.replace(/index.php.*$/, 'index.php/json/');

  let data = $.getJSON(baseDataURL + '/data', $.param({ 'func': 'classsections', 'json': '' }));

  return data;

}

//add a column for the Period(Academic Year, Fall, or Spring) and Year for filtering and remove null/empty values for any columns with number as the type

function processData(rawData) {

  const transformedData = {

    cols: rawData.cols.filter(col => col.label !== "Academic Year"),

    rows: []

  };

  //add the two columns to the beginning

  transformedData.cols.unshift(

    { id: "", label: "Period", pattern: "", type: "string" },

    { id: "", label: "Year", pattern: "", type: "string" }

  );

  const academicYearIndex = rawData.cols.findIndex(col => col.label === "Academic Year");

  const termIndex = rawData.cols.findIndex(col => col.label === "Term");

  rawData.rows.forEach(row => {

    const term = row.c[termIndex]?.v || "";

    const academicYear = printAYperiod(row.c[academicYearIndex]?.v || "");

    const baseRow = row.c.filter((_, idx) => idx !== academicYearIndex);

    transformedData.rows.push({

      c: [

        { v: "Academic Year", f: null },

        { v: academicYear, f: null },

        ...baseRow

      ]

    });

    if (term.endsWith("70")) {

      transformedData.rows.push({

        c: [

          { v: "Fall", f: null },

          { v: term.substring(0, 4), f: null },

          ...baseRow

        ]

      });

    }

    if (term.endsWith("20")) {

      transformedData.rows.push({

        c: [

          { v: "Spring", f: null },

          { v: term.substring(0, 4), f: null },

          ...baseRow

        ]

      });

    }

  });

  //after manipulating the data, make sure to resolve any null values for numbers

  //identify numeric column indexes

  const numericIndexes = transformedData.cols

    .map((col, idx) => col.type === "number" ? idx : -1)

    .filter(idx => idx !== -1);

  //iterate over all rows

  transformedData.rows.forEach(row => {

    numericIndexes.forEach(idx => {

      const cell = row.c[idx];

      const value = cell?.v;

      //if cell missing, null, empty string, or invalid — set to 0

      if (!cell || cell.v === null || cell.v === "" || isNaN(cell.v)) {

        row.c[idx] = { v: 0, f: null };

      } else {

        //force all to type number just in case any were saved as strings

        row.c[idx].v = Number(value);

      }

    });

  });

  return transformedData;

}

function drawChart(d) {

  data = new google.visualization.DataTable(d); //data is transformed to a Google dataTable

  //desired filters (must be a column in the returned data)

  const filterList = [

    { name: 'Period', allowMultiple: false, allowNone: false },

    { name: 'Campus', allowMultiple: true, allowNone: true },

    { name: 'College', allowMultiple: true, allowNone: true },

    { name: 'Department', allowMultiple: true, allowNone: true },

    { name: 'Course Level', allowMultiple: true, allowNone: true },

    { name: 'Detail Level', allowMultiple: true, allowNone: true },

    { name: 'Concurrent', allowMultiple: false, allowNone: true }, //don't allow multiple because if both Y and N are selected it is the same as not selecting either one

    { name: 'General Education', allowMultiple: false, allowNone: true } //don't allow multiple because if both Y and N are selected it is the same as not selecting either one

  ];

  //create the array of filters containing the Google controlWrappers

  filterList.forEach(filterInfo => {

    let filter_id = filterInfo.name.replace(/ /g, "_") + '_filter'; //replace all spaces with _ and append _filter

    $("#ef_right").append('<div id="' + filter_id + '"></div>'); //create all filters in the ef_right div

    filters.push(new google.visualization.ControlWrapper({

      controlType: 'CategoryFilter',

      containerId: filter_id,

      options: {

        filterColumnLabel: filterInfo.name, //this is the label from your dataTable/the returned column names from the database

        ui: {

          allowMultiple: filterInfo.allowMultiple,

          allowTyping: false,

          sortValues: true,

          selectedValuesLayout: 'below',

          labelStacking: 'vertical',

          allowNone: filterInfo.allowNone

        }

      }

    }));

  });

  //invisible table used for filtering

  proxyTable = new google.visualization.ChartWrapper({

    'chartType': 'Table',

    'containerId': 'proxyTable'

  });

  //build the chartWrapper for the column graph

  options = {

    'hAxis': {

      'title': null

    },

    'vAxis': {

      'format': '#,###',

      'minValue': 0,

      'maxValue': 'auto',

      'textPosition': 'in',

      'title': null

    },

    //'title': 'Class Sections and SSCH',

    'legend': {

      'position':'bottom'

    },

    'chartArea': {

      'left':'10',

      'right':'10',

      'top':'50',

      'bottom':'50',

      'width':'100%',

      'height':'100%',

    },

    'width':'100%',

    'height':450,

    'viewWindowMode': 'pretty',

    'tooltip': {

      'trigger': 'selection',

      'isHtml': true

    },

    'annotations': {

      'alwaysOutside': true,

      'highContrast': true,

    }

  };

  options = JSON.parse(JSON.stringify(options));

  chart0 = new google.visualization.ChartWrapper({

    'chartType': 'ColumnChart',

    'containerId': 'chart0',

    'options': options

  });

  var dash = new google.visualization.Dashboard(document.getElementById('dashboard'));

  dash.bind(filters,proxyTable);

 

  google.visualization.events.addListener(proxyTable,'ready',proxyTableReady); //call proxyTableReady once the proxy table is fully drawn

  dash.draw(data); //draw the dashboard with retrieved JSON data which has been converted to a google dataTable

}

function proxyTableReady() {

  proxyDataTable = proxyTable.getDataTable(); //retrieve filtered datatable (filters are applied to proxyTable)

  //get indexes and then aggregate/group data for graphing

  let yearIndex = proxyDataTable.getColumnIndex('Year'); //23

  let sschIndex = proxyDataTable.getColumnIndex('SSCH'); //0

  let enrollmentsIndex = proxyDataTable.getColumnIndex('ENROLLMENTS'); //1

  let crnIndex = proxyDataTable.getColumnIndex('CRN'); //2

  var grouped_data = google.visualization.data.group(

    proxyDataTable,

    [yearIndex],

    [

      {'column': sschIndex, 'aggregation': google.visualization.data.sum, 'type':'number'},

      {'column': enrollmentsIndex, 'aggregation': google.visualization.data.sum, 'type':'number'},

      {'column': crnIndex, 'aggregation': google.visualization.data.count, 'type':'number'}

    ]

  );

  if ( activeColumns.length === 0 ) { //only run if activeColumns is not populated

    const x = grouped_data.getNumberOfColumns();

    for ( let i=0; i < x; i++ ) {

      activeColumns.push(i); //build array of active Columns for showing/hiding them in the chart

    }

  }

  var view = buildViewWithSpecifiedColumns(grouped_data, activeColumns);

  chart0.setDataTable(view);

  chart0.draw();

  setChartActionDownload(chart0);

  if ( chartSelectListener == false ) {

    //logic to show/hide columns when clicking on the legend

    google.visualization.events.addListener(chart0, 'select', function () {

      var sel = chart0.getChart().getSelection();

      if ( sel.length > 0 ) { //either a legend item or a chart element has been selected

        if ( sel[0].row === null ) { //legend has been clicked

          var selectedCol = sel[0].column;

          //toggle column visibility in the activeColumns array

          var indexInactive = activeColumns.indexOf(selectedCol); //get the index in the array for the selected column

          if (indexInactive > -1) { //if it is -1 it was not in the active columns array

            activeColumns.splice(indexInactive, 1); //remove if present (hide)

          } else {

            insertSorted(activeColumns, selectedCol); //add column if not present (show)

          }

          proxyTableReady(); //recursively call this function to redraw the chart with selected filters

        }

      } // else //an element in the chart has been deselected

    });

    chartSelectListener = true; //prevent the listener from being added more than once

  }

}

//makes the chart downloadable by adding a download button to the tooltip.

function setChartActionDownload(chart) {

  chart.getChart().setAction({

    'id': chart.getContainerId() + '_tooltip',

    'text': 'Download Data',

    'action': function() { downloadData(chart); },

    'visible': function() { return isDownloadVisible(chart); }

  });

}

function downloadData(chart) {

  var chartData = chart.getDataTable();

  var selection = chart.getChart().getSelection();

  var filter_data = {};

  //in the future it could limit the download to the single academic year that is clicked on

  //filter_data['field'] = chartData.getColumnLabel(selection[0]['column']); //grabs the name of the column the user clicked in Google Charts

  //grab the filter options

  $(filters).each(function() {

    filter_data[this.getOption('filterColumnLabel')] =  this.getState()['selectedValues'];

  });

  window.location.href = 'classsections/csv?' + $.param(filter_data);

  /* this whole section will download the data directly from the dataTable

  //manually build the header row

  let headerRow = [];

  for (let i = 0; i < proxyDataTable.getNumberOfColumns(); i++) {

    let label = proxyDataTable.getColumnLabel(i);

    headerRow.push('"' + label.replace(/"/g, '""') + '"');

  }

 

  let csvHeaders = headerRow.join(',') + '\n';

  let csvFormattedDataRows = google.visualization.dataTableToCsv(proxyDataTable);

  //combine headers and data

  let fullCsvContent = csvHeaders + csvFormattedDataRows;

  let encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(fullCsvContent);

  //download link creation

  let $link = $('<a>')

    .attr('href', encodedUri)

    .attr('download', 'class_sections_ssch.csv')

    .css('display', 'none')

    .appendTo('body');

  $link[0].click(); //execute the downloading by "clicking" the link

  $link.remove();   //clean up the DOM

  */

}

function isDownloadVisible(chart) {

  let chartData = chart.getDataTable();

  let selection = chart.getChart().getSelection();

  if(selection.length > 0) {

    if(chartData.getColumnRole(selection[0]['column']) == 'annotation') //prevent the download button from showing on columns with the role of annotation

      return false;

    else

      return true;

  } else

    return false;

}

//returns the period of years when given the academic year

function printAYperiod(academicYear) {

  const endYear = parseInt(academicYear, 10); //convert to integer

  if (!isNaN(endYear)) { //check if conversion was successful

    const startYear = endYear - 1;

    return `${startYear}-${endYear}`;

  } else {

    return false;

  }

}

//inserts a value into an array in numerical order

//the array is assumed to consist of numbers

function insertSorted(arr, val) {

  let i = 0;

  while (i < arr.length && arr[i] < val) {

    i++;

  }

  arr.splice(i, 0, val); // Insert val at index i

}

//returns a view with hidden columns

//expects a Google Charts dataTable and an array of indexes of the desired columns to show

function buildViewWithSpecifiedColumns(dataTable, visibleIndexes) {

  const totalCols = dataTable.getNumberOfColumns();

  const viewColumns = [];

  for (let i = 0; i < totalCols; i++) {

    if (visibleIndexes.includes(i)) {

      viewColumns.push(i);

    } else { //the column should be hidden

      viewColumns.push({

        type: dataTable.getColumnType(i),

        label: dataTable.getColumnLabel(i),

        calc: () => null

      });

    }

  }

  const view = new google.visualization.DataView(dataTable);

  view.setColumns(viewColumns);

  return view;

}

[Alvin Galit]

Oh gotcha! Glad you got it figured out. That was all YOU!