Crosstab query with dynamic columns

[daverene]

How do i make paramquery display the columns dynamically from a dynamic crosstab query which has the columns displayed changing all the time?

[paramvir]

I assume when the columns are changed, data is also changed.

grid.refreshCM( new_colModel );
grid.refreshDataAndView();

https://paramquery.com/pro/api#method-refreshCM
https://paramquery.com/pro/api#method-refreshDataAndView

[daverene]

I don't think that will work as I won't know what columns will come back from the data url.

Eg. run it for 01/01/2017 may produce these columns below.

AA, BB, CC, DD, EE, FF

run it for 01/02/2017 may produce this

AA, BB, CC, DD, KK, LL, MM

In my initial column Model is setup as AA, BB, CC, DD, EE, FF, GG, HH, II, JJ, KK, LL, MM, NN, OO, PP

so how do I get the right columns to display at runtime in the grid no matter what date I run it for?

[paramvir]

You would need to construct colModel in runtime depending upon the received columns.

[daverene]

so without knowing what exact column names are going to come back to me from the query, can you please show me an example an how to setup the column model.

[paramvir]

Last time I checked, a cross tab query displays column headings in first row.

Anyway as you can't gather column names or any column information from the data, so your question is more related to creating colModel without any column information?

So I guess the data is in array format and first row with column headers is missing in your case.

var data = [
            [1, 'Exxon Mobil', '339938.0', '36130.0'],
            [2, 'Wal-Mart Stores', '315654.0', '11231.0']
];

From this data you can at least count the number of columns, which can help you generate empty colModel.

var len = data[0].length

var colModel = [];
for(var i=0;i<len;i++){
 colModel.push({});
}

grid.option('dataModel.data', data); //assign new data.
grid.refreshCM( colModel ); //assign new colModel.
grid.refreshDataAndView();

[daverene]

the crosstab query displays the columns in the actual heading of the data, not the first row. Anymore suggests on how to make this work?
Is there a way to do a test if a dataIndx does not exist in the data, then you can make hidden true for the column?

[paramvir]

Could you please post the format of sample data received by the grid in your case. It would be easier to provide suggestions based on that.

[daverene]

Here is data for 2 dates 07/04/17 and 08/04/17, you will notice the 2 columns appear on 8th but the 7th, they are _110 and _120.

The grid won't display the "DisplayCols" column, I have it there incase we can use this to decide what columns to display.

{"data":[{"DisplayCols":"[NT], [TH], [DT], [_110], [_120]","EmployeeId":84238,"Employee":"wally, wicky","TimesheetDate":"07/04/17","NT":2,"TH":null,"DT":null,"_110":3,"_120":null},{"DisplayCols":"[NT], [TH], [DT], [_110], [_120]","EmployeeId":84248,"Employee":"wayne, smith","TimesheetDate":"07/04/17","NT":null,"TH":null,"DT":null,"_110":null,"_120":7}]}

{"data":[{"DisplayCols":"[NT], [TH], [DT]","EmployeeId":84238,"Employee":"wally, wicky","TimesheetDate":"08/04/17","NT":2,"TH":null,"DT":null},{"DisplayCols":"[NT], [TH], [DT], [_110], [_120]","EmployeeId":84248,"Employee":"wayne, smith","TimesheetDate":"08/04/17","NT":null,"TH":null,"DT":null}]}

[paramvir]

Looks like you have all necessary info to create colModel during runtime. Just pick first row, read its keys ( which correspond to dataIndx of columns ) and create colModel array.

followed by

Code: [Select]

grid.option('dataModel.data', data); //assign new data.
grid.refreshCM( colModel ); //assign new colModel.
grid.refreshDataAndView();


[daverene]

where do I run this code exactly at runtime as I cannot get it to work? This is my current code on creating the grid.

 var dataModelDetail = {
                     location: "remote",
                     dataType: "json",
                     method: "GET",
                     recIndx: "RecID", //used to make update row active
                     url: "http://projectcontrols/TimeLinkWebservice/TimeLinkWebservice.asmx/JSON_TimesheetDetail?projectID=" + querySt("ProjectID") + "&managerID=" + document.getElementById("<%=ddlManager.ClientID %>").value + "&FromDate=" + fdate[1] + '/' + fdate[0] + '/' + fdate[2] + "&ToDate=" + tdate[1] + '/' + tdate[0] + '/' + tdate[2] + "&EmployeeName=",
                     success: function (result) {
                         $('#grid_jsonpDetail').html(result);
                     },
                     error: function (jq) {
                         alert(JSON.stringify(jq));
                     },
                     getData: function (dataJSON) {
                         var data = dataJSON.data;
                         return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: data };
                     },
                 };


 var objDetail = {
                     load: function () {
                         var grid = this;
                         var filter = grid.getColumn({ dataIndx: "TradeName" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["TradeName"] });

                         filter = grid.getColumn({ dataIndx: "Employee" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["Employee"] });

                         filter = grid.getColumn({ dataIndx: "EmployeeNo" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["EmployeeNo"] });

                         filter = grid.getColumn({ dataIndx: "WorkOrderNumber" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["WorkOrderNumber"] });

                         filter = grid.getColumn({ dataIndx: "ContractNo" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["ContractNo"] });

                         filter = grid.getColumn({ dataIndx: "Event" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["Event"] });

                         filter = grid.getColumn({ dataIndx: "SI" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["SI"] });

                         filter = grid.getColumn({ dataIndx: "SWS" }).filter;
                         filter.cache = null;
                         filter.options = grid.getData({ dataIndx: ["SWS"] });
                     },

                     width: 1700,
                     height: 800,
                     filterModel: { on: true, mode: "AND", header: true },
                     scrollModel: { autoFit: true },
                     collapsible: false,
                     postRenderInterval: -1, //synchronous post render.
                     pageModel: { type: "local", rPP: 100, rPPOptions: [10, 50, 100, 2000], strRpp: "{0}", strDisplay: "{0} to {1} of {2}" },
                     dataModel: dataModelDetail,
                     colModel: columnsDetail,
                     trackModel: { on: true }, //used to make update row active
                     create: function (evt, ui) {

                         var grid = this,
                             column;

                         this.option('dataModel.data', dataModelDetail);
                             this.refreshCM(columnsDetail); //assign new colModel.
                            this.refreshDataAndView();

                         //fetch options for WO column from server.
                         var ContractNo = '%';
                         $.getJSON("http://projectcontrols/TimeLinkWebservice/TimeLinkWebservice.asmx/JSON_GetWOs?SupervisorID=" + document.getElementById("<%=ddlManager.ClientID %>").value + "&WorkOrderNo=&projectID=" + querySt("ProjectID") + "&ContractNo=" + ContractNo + "&IncludeLeave=True", function (response) {
                     
                             column = grid.getColumn({ dataIndx: 'WorkOrderNumber' });
                             column.editor.options = response;
                         });
                     
                         $.getJSON("http://projectcontrols/TimeLinkWebservice/TimeLinkWebservice.asmx/JSON_GetSIs?WorkOrderNo=%&DateCriteria=" + fdate[1] + '/' + fdate[0] + '/' + fdate[2] + "&ContractNo=" + ContractNo + "&projectID=" + querySt("ProjectID"), function (response) {
                           
                             SIDD = response;
                         });

                         $.getJSON("http://projectcontrols/TimeLinkWebservice/TimeLinkWebservice.asmx/JSON_GetEvents?WorkOrderNo=%&DateCriteria=" + fdate[1] + '/' + fdate[0] + '/' + fdate[2] + "&ContractNo=" + ContractNo + "&projectID=" + querySt("ProjectID"), function (response) {

                             EventDD = response;
                         });
                     },
                     wrap: true, hwrap: true,
                     editModel: {
                         clicksToEdit: 1,
                        // pressToEdit: $.ui.keyCode.ENTER,
                         saveKey: $.ui.keyCode.ENTER,
                         keyUpDown: false,
                         cellBorderWidth: 0
                     },
                     toolbar: {
                         items: [{
                             type: 'button',
                             cls: 'blue',
                             icon: 'ui-icon-pencil',
                             label: '<h3 style="color:blue;"><b>Save All Updated Rows</b></h3>',
                             options: { disabled: false },
                             listener: function () {
                                 saveChanges(this);
                             }
                         }]
                     },
                     hoverMode: 'row',
                     numberCell: { show: false, resizable: true, title: "#" },
                     title: "<u><b><mark>Detail</mark></b></u>",
                     resizable: true
                 };

var $gridDetail = pq.grid("#grid_jsonpDetail", objDetail);

[daverene]

This is my column array below, is there a way that I can add a function to the "hidden" element in the column to decide if it is true or false, it will be whether the columns titles from 104, 105 etc. onwards are in the actual data?

var columnsDetail = [

                 {
                     title: "DisplayCols", dataType: "string", dataIndx: "DisplayCols", editable: false, hidden: true
                 },
                 {
                     title: "RecID", dataType: "string", dataIndx: "RecID", editable: false, hidden: true
                 },
                 {
                     title: "EmployeeId", dataType: "integer", dataIndx: "EmployeeId", editable: false, hidden: true
                 },

                 {
                     title: "Employee", dataType: "string", editable: false, dataIndx: "Employee", minWidth: 140, maxWidth: 180, hidden: false,
                     filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "Employee",
                         labelIndx: "Employee",
                         listeners: ['change']
                     }
                 },
                 {
                     title: "Emp. No.", dataType: "string", editable: false, dataIndx: "EmployeeNo", minWidth: 70, maxWidth: 70, hidden: false, filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "EmployeeNo",
                         labelIndx: "EmployeeNo",
                         listeners: ['change']
                     }
                 },
                 {
                     title: "Trade", dataType: "string", editable: false, dataIndx: "TradeName", minWidth: 130, maxWidth: 200, hidden: false, filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "TradeName",
                         labelIndx: "TradeName",
                         listeners: ['change']
                     }
                 },
                 {
                     title: "Contract", dataType: "string", editable:false, dataIndx: "ContractNo", minWidth: 70, maxWidth: 70, hidden: false, filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "ContractNo",
                         labelIndx: "ContractNo",
                         listeners: ['change']
                     }
                 },
                 {
                     title: "W.O", dataType: "string", dataIndx: "WorkOrderNumber", minWidth: 90, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) { return true; } else { return false; } },
                     filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },         
                         valueIndx: "WorkOrderNumber",
                         labelIndx: "WorkOrderNumber",
                         listeners: ['change']
                     },
                     editor: {
                         type: 'select',
                         valueIndx: "WorkOrderNo",
                         labelIndx: "WorkOrderNo",
                         options: []
                     },
                     validations: [{ type: 'minLen', value: 1, msg: "Required" }]       
                 },
                 {
                     title: "Event", dataType: "string", dataIndx: "Event", minWidth: 70, maxWidth: 70, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) { return true; } else { return false; } },
                     filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "Event",
                         labelIndx: "Event",
                         listeners: ['change']
                     },
                     editor: {
                         type: 'select',
                         prepend: { "": "" },
                         options: function (ui) {
                               var id = ui.rowData.WorkOrderNumber;
                             
                               var eventList = "";
                               //iterate through the events list to find the matching record.
                               for (var i = 0; i < EventDD.length; i++) {
                                   
                                     var row = EventDD;
                                     if (row.WorkOrderNo == id) {//match found.
                                         eventList = eventList + "|" + row.EventNo;
                                     }
                               }
                               if (eventList.length == 0) {
                                   return [];
                               }
                               else {
                                   return eventList.split("|");
                               }
                         }
                     }
                 },
                 {
                     title: "S.I", dataType: "string", dataIndx: "SI", minWidth: 70, maxWidth: 80, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) { return true; } else { return false; } }, filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "SI",
                         labelIndx: "SI",
                         listeners: ['change']
                     },
                     editor: {
                         type: 'select',
                         prepend: { "": "" },
                         options: function (ui) {
                             var id = ui.rowData.WorkOrderNumber;

                             var SIList = "";
                             //iterate through the events list to find the matching record.
                             for (var i = 0; i < SIDD.length; i++) {

                                 var row = SIDD;
                                 if (row.VariationNo == id) {//match found.
                                     SIList = SIList + "|" + row.SINo;
                                 }
                             }
                             if (SIList.length == 0) {
                                 return [];
                             }
                             else {
                                 return SIList.split("|");
                             }
                         }
                     }
                 },
                 {
                     title: "SWS", dataType: "string", dataIndx: "SWS", minWidth: 70, maxWidth: 70, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) { return true; } else { return false; } }, filter: {
                         type: "select",
                         condition: 'equal',
                         prepend: { '': '-ALL-' },
                         valueIndx: "SWS",
                         labelIndx: "SWS",
                         listeners: ['change']
                     }
                 },
                 {
                     title: "Date", dataType: "date", dataIndx: "TimesheetDate", minWidth: 60, maxWidth: 60, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) { return true; } else { return false; } },
                     editor: {
                         type: 'textbox',
                         init: dateEditor
                     },
                     validations: [
                         { type: 'regexp', value: '^[0-9]{2}/[0-9]{2}/[0-9]{2}$', msg: 'Not in dd/mm/yy format' }
                     ]
                 },
                 {
                     title: "Comments", dataType: "string", dataIndx: "Comments", minWidth: 100, hidden: false, editable: function (ui) { if (ui.rowData.Update == 1) {return true;} else {return false;} }
                },
                {
                    title: "", dataIndx: "Delete", minWidth: 80, maxWidth: 80, hidden: false, editable: false,
                    render: function (ui) {
                        if (ui.rowData.Delete != null) {
                            if (ui.rowData.Delete == 1) {
                                return "<button type='button' class='delete_btn' style='font-weight: bold; color: #FF0000'>Delete</button>";
                            }
                            else {
                                return "<button type='button' disabled='disabled' style='font-weight: bold; color: #FFFFFF'>Delete</button>";
                            }
                        }
                    },
                    postRender: function (ui) {
                        var grid = this,
                            $cell = grid.getCell(ui);
                        $cell.find("button").button({ icons: { primary: 'ui-icon-scissors' } })
                        .bind("click", function () {
                            deleteRow(grid, ui.rowData.Employee, ui.rowData.EmployeeId, ui.rowData.WorkOrderNumber, ui.rowData.Event, ui.rowData.SI, ui.rowData.SWS, ui.rowData.TimesheetDate, ui.rowData.Comments, querySt('Login'));
                        });
                    }
                },
                { title: "NT", dataType: "float", dataIndx: "NT", minWidth: 40, maxWidth: 40, editable: true },
                { title: "TH", dataType: "float", dataIndx: "TH", minWidth: 38, maxWidth: 38, editable: true },
                { title: "DT", dataType: "float", dataIndx: "DT", minWidth: 38, maxWidth: 38, editable: true },
                { title: "102", dataType: "float", dataIndx: "102", minWidth: 38, maxWidth: 38, editable: true },
                { title: "104", dataType: "float", dataIndx: "_104", minWidth: 38, maxWidth: 38, editable: true },
                { title: "105", dataType: "float", dataIndx: "_105", minWidth: 38, maxWidth: 38, editable: true },
                { title: "106", dataType: "float", dataIndx: "_106", minWidth: 38, maxWidth: 38, editable: true },
                { title: "107", dataType: "float", dataIndx: "_107", minWidth: 38, maxWidth: 38, editable: true },
                { title: "109", dataType: "float", dataIndx: "_109", minWidth: 38, maxWidth: 38, editable: true },
                { title: "110", dataType: "float", dataIndx: "_110", minWidth: 38, maxWidth: 38, editable: true },
                { title: "112", dataType: "float", dataIndx: "_112", minWidth: 38, maxWidth: 38, editable: true },
                { title: "113", dataType: "float", dataIndx: "_113", minWidth: 38, maxWidth: 38, editable: true },
                { title: "115", dataType: "float", dataIndx: "_115", minWidth: 38, maxWidth: 38, editable: true },
                { title: "120", dataType: "float", dataIndx: "_120", minWidth: 38, maxWidth: 38, editable: true },
                { title: "128", dataType: "float", dataIndx: "_128", minWidth: 38, maxWidth: 38, editable: true },
                { title: "129", dataType: "float", dataIndx: "_129", minWidth: 38, maxWidth: 38, editable: true },
                { title: "131", dataType: "float", dataIndx: "_131", minWidth: 38, maxWidth: 38, editable: true },
                { title: "132", dataType: "float", dataIndx: "_132", minWidth: 38, maxWidth: 38, editable: true },
                { title: "135", dataType: "float", dataIndx: "_135", minWidth: 38, maxWidth: 38, editable: true },
                { title: "140", dataType: "float", dataIndx: "_140", minWidth: 38, maxWidth: 38, editable: true },
                { title: "145", dataType: "float", dataIndx: "_145", minWidth: 38, maxWidth: 38, editable: true },
                { title: "146", dataType: "float", dataIndx: "_146", minWidth: 38, maxWidth: 38, editable: true },
                { title: "147", dataType: "float", dataIndx: "_147", minWidth: 38, maxWidth: 38, editable: true },
                { title: "148", dataType: "float", dataIndx: "_148", minWidth: 38, maxWidth: 38, editable: true },
                { title: "150", dataType: "float", dataIndx: "_150", minWidth: 38, maxWidth: 38, editable: true },
                { title: "151", dataType: "float", dataIndx: "_151", minWidth: 38, maxWidth: 38, editable: true },
                { title: "152", dataType: "float", dataIndx: "_152", minWidth: 38, maxWidth: 38, editable: true },
                { title: "155", dataType: "float", dataIndx: "_155", minWidth: 38, maxWidth: 38, editable: true },
                { title: "156", dataType: "float", dataIndx: "_156", minWidth: 38, maxWidth: 38, editable: true },
                { title: "160", dataType: "float", dataIndx: "_160", minWidth: 38, maxWidth: 38, editable: true },
                { title: "161", dataType: "float", dataIndx: "_161", minWidth: 38, maxWidth: 38, editable: true },
                { title: "161a", dataType: "float", dataIndx: "_161a", minWidth: 38, maxWidth: 38, editable: true },
                { title: "164", dataType: "float", dataIndx: "_164", minWidth: 38, maxWidth: 38, editable: true },
                { title: "168", dataType: "float", dataIndx: "_168", minWidth: 38, maxWidth: 38, editable: true },
                { title: "168a", dataType: "float", dataIndx: "_169a", minWidth: 38, maxWidth: 38, editable: true },
                { title: "170", dataType: "float", dataIndx: "_170", minWidth: 38, maxWidth: 38, editable: true },
                { title: "178", dataType: "float", dataIndx: "_178", minWidth: 38, maxWidth: 38, editable: true },
                { title: "180", dataType: "float", dataIndx: "_180", minWidth: 38, maxWidth: 38, editable: true },
                { title: "187", dataType: "float", dataIndx: "_187", minWidth: 38, maxWidth: 38, editable: true },
                { title: "188", dataType: "float", dataIndx: "_188", minWidth: 38, maxWidth: 38, editable: true },
                { title: "189", dataType: "float", dataIndx: "_189", minWidth: 38, maxWidth: 38, editable: true },
                { title: "196", dataType: "float", dataIndx: "_196", minWidth: 38, maxWidth: 38, editable: true },
                { title: "202", dataType: "float", dataIndx: "_202", minWidth: 38, maxWidth: 38, editable: true },
                { title: "210", dataType: "float", dataIndx: "_210", minWidth: 38, maxWidth: 38, editable: true },
                { title: "211", dataType: "float", dataIndx: "_211", minWidth: 38, maxWidth: 38, editable: true }               
           ];

[paramvir]

Since you have set value of dataModel.location to 'remote',

you may place this code to update colModel in the dataModel.getData callback.

Code: [Select]

getData: function (dataJSON) {

           var data = dataJSON.data;

            //build new colModel or show / hide columns from the data received in getData response.
           grid.refreshCM( colModel );

           return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: data };
},