Author Topic: Crosstab query with dynamic columns  (Read 7471 times)

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Crosstab query with dynamic columns
« on: March 25, 2017, 05:05:07 am »
How do i make paramquery display the columns dynamically from a dynamic crosstab query which has the columns displayed changing all the time?

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #1 on: March 28, 2017, 11:06:43 am »
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
« Last Edit: March 28, 2017, 11:12:30 am by paramquery »

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #2 on: March 30, 2017, 04:52:00 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #3 on: March 31, 2017, 02:49:19 pm »
You would need to construct colModel in runtime depending upon the received columns.

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #4 on: April 03, 2017, 01:55:10 pm »
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.
« Last Edit: April 03, 2017, 02:35:38 pm by daverene »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #5 on: April 04, 2017, 11:00:57 am »
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();
« Last Edit: April 04, 2017, 11:36:27 am by paramquery »

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #6 on: April 07, 2017, 09:08:38 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #7 on: April 07, 2017, 09:20:04 am »
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.
« Last Edit: April 07, 2017, 09:33:12 am by paramquery »

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #8 on: April 07, 2017, 10:30:44 am »
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

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #9 on: April 10, 2017, 10:55:30 pm »
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

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #10 on: April 11, 2017, 09:28:50 am »
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);
« Last Edit: April 11, 2017, 09:40:01 am by daverene »

daverene

  • Newbie
  • *
  • Posts: 23
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #11 on: April 13, 2017, 05:08:42 am »
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 }               
           ];
« Last Edit: April 13, 2017, 05:11:03 am by daverene »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6156
    • View Profile
Re: Crosstab query with dynamic columns
« Reply #12 on: April 17, 2017, 01:42:27 pm »
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 };
},