KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Row detail breaks Datatable client row group #2794

Open Andrew Guattery opened this topic on on Aug 14, 2022 - 5 comments

Andrew Guattery commented on Aug 14, 2022

So I have this Datatable:

DataTables::create(array(
       "name" => "payrollbyemp",  
           "dataStore"=>$this->dataStore("paybyp"), 
           "options"=>array(
        "PERIOD_DT"=>array(
            array(1,"asc")
        )
    ),
    "columns"=> [
    "STR_ID"=>[
    "label"=>"Store"],
    "USR_ID"=>[
    "label"=>"User ID"],
    "PERIOD_DT"=>[
    "label"=>"Clock Date",
    "displayFormat"=>"m-d-Y"
     ],
    "CLOCK_IN"=>[   
    "label"=>"Clock In"],
    "CLOCK_OUT"=>[
    "label"=>"Clock Out"],
    "PD_HRS"=>[
    "label"=>"Paid Hours"],
    "TOTAL"=>array(
    "label"=>"Total Hours"),
    "USRNAME"=>[
    "visible"=>false]
    ],

       
    "clientRowGroup" => [
        "USRNAME" => [
   
            'direction' => 'asc', //'asc', 'desc'
            'calculate' => [
                'pdhrsAmount' => [
                    'sum', //'sum', 'count', 'avg', 'min', 'max'
                    'PD_HRS',
                    "format" => "function(value) {return value.toFixed(2);}",
                ],
                'totalhoursAmount' => [
                'sum',
                'TOTAL',
                "format" => "function(value) {return value.toFixed(2);}",
                
                ],
            ],
                       "top"=>"",
           "bottom"=>"<td></td><td><b>Total for user {USRNAME}</b></td><td></td><td></td><td></td><td></td><td><b>{pdhrsAmount}</b></td><td><b>{totalhoursAmount}</b></td>"
           ],
           ],
           

          "rowDetailData" => "function(row) {
             return 'Client-built row detail: ';
         }",
        // "rowDetailIcon" => false,
        // "rowDetailSelector" => 'td.col-customer-name',
//        "options" => [
//            "paging" => true,
//            "searching" => true,
//            "pageLength" => 10,
//        ],
            "cssClass"=>array(
        "table"=>"table table-striped table-bordered"
    )//end css class
        
    ));//End Table::create

If I comment out the "rowDetailData" drilldown the table grouping works fine but when I add the "rowDetaildata" the grouping by USRNAME gets screwed up. The {USRNAME} field prints as "0.0" instead of the user name and the {pdhrsAmount} is NaN.

Any ideas why this is occurring or of a different solution? I need to have a "details" section for each row - multiple rows coming from a different query than the main query for this subreport. so a nested subreport of sorts....

Sebastian Morales commented on Aug 15, 2022

Client row group feature might conflict with client row detail one. What would happen if you used server-based row detail instead?

    DataTables::create(array(
        ...
        "rowDetailData" => function($row) {
            return "Server-built row detail: " . $row['orderDate'];
        },
    ));

https://www.koolreport.com/docs/datagrid/datatables/#row-detail-(version-%3E=-5.0.0)

Andrew Guattery commented on Aug 15, 2022

Sebastian, So I changed the rowdetailData to be server side, and Datatable source from dataStore to datasource and set serverSide=>true. Still the same issue. When I comment out rowdetailData I get this:

But when I have rowdetailData in use I get this:

BTW this seems to happen regardless of what datasource method I tried. It seems like serverSide is not processing - Also, if I set serverSide=>true I start running into memory allocation errors. The report in question is a sub report- do I need to make everything serverSide for this to work? the main report loads fine (select and datetime picker stuff for the sub report). Thanks for the input, Andy

Andrew Guattery commented on Aug 17, 2022

Ok more detail - I have commented out everything but this one subreport and switched to a simple mssql select query. the datatable has "serverSide"=>true:

DataTables::create(array(
       "name" => "payrollbyemp",
       'dataSource' => function() {
        return $this->src('payroll')->query("SELECT 'a' AS STR_ID,'andy' as USRNAME,0 as PD_HRS,0 as TOTAL UNION ALL SELECT 'b' AS STR_ID, 'jeff' as USRNAME, 1 as PD_HRS, 1 as TOTAL");
 "serverSide"=>true,

  "rowDetailData" => function($row) {
      return "Server-built row detail: " . $row['STR_ID'];
   },

  "clientRowGroup" => [
        "USRNAME" => [
   
            'direction' => 'asc', //'asc', 'desc'
            'calculate' => [
                'pdhrsAmount' => [
                    'sum', //'sum', 'count', 'avg', 'min', 'max'
                    'PD_HRS',
      
                ],
                'totalhoursAmount' => [
                'sum',
                'TOTAL',

                
                ],
            ],

           ],
           ],
           


        
    ));//End Table::create

If I run it I get this error:

DataTables warning: table id=payrollbyemp - Requested unknown parameter '4' for row 0, column 4. For more information about this error, please see http://datatables.net/tn/4

Adding or removing columns just increments the index of the error- the report will load without the "expand" icon and all columns are off by one.

This is beginning to look like a bug....

Andrew Guattery commented on Aug 17, 2022

And MORE detail: Firstly, it appears I mislabeled the two screenshots above; Things change if I comment out "serverSide"=>true NOT rowdetailData. Now, what appears to be happening in Datatables.php is that the arrays that hold the columns and column data do not get an entry for rowDetail at some point, so the columns and data arrays do not match up. I cannot tell if it is in the onRender, builddataRows or builddataDetail function yet, but it appears to be something missing there that is causing the problem.

Andrew Guattery commented on Aug 18, 2022

So now have done some more investigating... There is a mismatch of column keys and data array fields when serverSide=>true and rowDetailData is used.This picture shows the error:

The error is " Unknown parameter '9' for row 0 col 9". We know that this error gives us little clue other than parameter '9' being in quotes is a string and that the error is at the 9th column. Interestingly enough the '9' changes if I play with the number of columns proportionately IE if I add a column it will be error '10' and if I remove one then error '8'.

So I went to Datatables.php and and did a var_dump in the function buildDataRows $row;

function buildDataRows --> $row

array(9) {
["USR_ID"]=> string(1) "1" 
["STR_ID"]=> string(1) "1" 
["PERIOD_DT"]=> string(10) "06-27-2022" 
["PD_HRS"]=> string(1) "4" 
["PD_TIM"]=> string(11) "04:15:00.00" 
["TTL"]=> string(18) "4.3499999999999996" 
["USRNAME"]=> string(15) "Andrew Guattery" 
["CLOCK_IN"]=> string(10) "2:30PM " 
["CLOCK_OUT"]=> string(10) "6:52PM " 
} 

Pretty simple and looks correct, this is an associative array of $key=>$value that matches the column list in the query and view.php columns array
Then I did a var_dump of the function buildDetailData columnkeys and each datarow:

--> $this->ShowColumnKeys

array(11) { 
[0]=> string(13) "rowDetailIcon" 
[1]=> string(6) "STR_ID" 
[2]=> string(6) "USR_ID" 
[3]=> string(9) "PERIOD_DT" 
[4]=> string(8) "CLOCK_IN" 
[5]=> string(9) "CLOCK_OUT" 
[6]=> string(6) "PD_HRS" 
[7]=> string(3) "TTL" 
[8]=> string(7) "USRNAME" 
[9]=> string(6) "PD_TIM" 
[10]=> string(15) "{rowDetailData}"
 } 

--> $this->dataRows[$i]

array(20) {
["USR_ID"]=> string(54) "1" 
["STR_ID"]=> string(1) "1" 
["PERIOD_DT"]=> string(10) "06-27-2022" 
["PD_HRS"]=> string(1) "4" 
["PD_TIM"]=> string(11) "04:15:00.00" 
["TTL"]=> string(18) "4.3499999999999996" 
["USRNAME"]=> string(15) "Andrew Guattery" 
["CLOCK_IN"]=> string(10) "2:30PM " 
["CLOCK_OUT"]=> string(10) "6:52PM " 

[0]=> string(1) "1" 
[1]=> string(54) "1" 
[2]=> string(10) "06-27-2022" 
[3]=> string(10) "2:30PM " 
[4]=> string(10) "6:52PM " 
[5]=> string(1) "4" 
[6]=> string(18) "4.3499999999999996" 
[7]=> string(15) "Andrew Guattery" 
[8]=> string(11) "04:15:00.00" 
["{rowDetailData}"]=> string(26) "Server-built row detail: 1" ["rowDetailIcon"]=> string(81) "" 
} 

Notice that the showColumnsKeys now has the two columns for the rowdetail icon and rowdetaildata while the datarow has both of those fields in an array..... in position 9. The function to render the report is looking for a string in this place and is getting an array. Also, all columns are shifted to the left by one as there is no rowdetailIcon column being found for each row:

Pretty clear that the build rowdetailData function is not being handed a matched set of data to parse when "serverSide"=>true Not sure exactly why this is occurring, more investigation is required.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
bug
help needed

None