Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Hello,
I have a data source and I want to add to it two type of columns during the init of the report
the rows show be added to the datasource columns (10 columns ) are coming from the data source. columns from the data source should be rowspan=2 and the dynamic date columns show be a separate row each so it should look something like this
Hi David,
Thank you for answering. In the meantime I managed to overcome a problem in my report. instead of trying to generate the date columns (all dates in selected month) dynamically in the report side I did it in MySQL stored procedure side. that way I already receive all the columns ready. You can view the report (although it's data is in Hebrew) in the following link: https://www.parashapp.com/dev/sbm/report now I have the following issues: a. I want to set settings to the columns in the report like for example: align-right or type=string etc for specific columns but I do not want to write it in the columns array because I do not know the names of the columns I get from the report because if it generated dynamically. b. I do I join all the cells of substitute_teacher_name. I do not need it to appear in each row. c. each substitute teacher has two rows or more for teacher_name I need to join the cells the relevant teacher_name. d. I need to add a calculated new row that sum each two rows of a teacher_name and sum the total_hours. Thanks
This is the current code for the table view
Table::create(array(
'dataStore'=>$this->dataStore('Substitutes'),
'columns'=>array(
'{others}'=>array(
'formatValue'=>function($value)
{
return $value == NULL ? '' : $value;
}
),
'total_hours'=>array(
'label'=>lang('cost')
),
),
'cssClass'=>array(
'table'=>'table table-hover table-bordered',
'tr'=>'cssItem',
'td'=>function($row,$colName)
{
return in_array($colName, array('substitute_teacher_name','id_number','full_address','bank_account','teacher_name','group_name')) ? 'rext-right' : 'text-center';
},
'th'=>function($colName)
{
return in_array($colName, array('substitute_teacher_name','id_number','full_address','bank_account','teacher_name','group_name')) ? 'cssHeader text-right' : 'cssHeader text-center';
},
),
"paging"=>array(
"pageSize"=>25,
"pageIndex"=>0,
),
"grouping"=>array(
"substitute_teacher_name"=>array(
"calculate"=>array(
"{sumAmount}"=>array("sum","total_hours"),
),
"bottom"=>'<b>{substitute_teacher_name}: {sumAmount}</b>',
),
),
"excludedColumns"=>array("total_hours")
));
Hi Ron,
Regarding your requirement in the screenshot:
At the moment the Table widget hasn't supported creating colspan, rowspan in the header yet. If you use datagrid/DataTables widget there's an available solution for such complex headers.
Again, the Table widget has no way to set its columns' label with a function but you could set a datastore metadata programmatically like this:
//MyReport.php
public function setup()
{
...
->pipe(new \koolreport\processes\Map(array(
"{meta}" => function($meta) {
$colMetas = $meta["columns"];
foreach ($colMetas as $colKey => $colMeta) {
$colMetas[$colKey]["label"] = modify($colKey);
}
$meta["columns"] = $colMetas;
return $meta;
}
)))
->pipe($this->dataStore("myDatastore"));
}
Hope this help! Thanks!
Hi Ron,
In order to use header hierarchy with datagrid/DataTables please set up your table columns in the following format:
| GroupA | Cat1.1 - Cat2.1 | Cat1.1 - Cat2.2 |
|---|
Then create DataTables like this:
DataTables::create([
'dataSource' => $ds,
'complexHeaders' => true,
'headerSeparator' => ' - ',
]);
You could replace the separator " - " with anything your like, such as " || ", etc. DataTables would divide the headers using the separator and put in rowspan, colspan in th for you.
Please try this with simple data and let us know the result. Thanks!
P.S: To get support you could either post your question here or send email to support@koolphp.net. We will try to answer in the next 24 hours if we find a quick solution. Some hard issues could take more time to solve. Thanks a lot!
In your specific case, Map process could be useful:
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
for ($monthday=1; $monthday<=30; $monthday++) {
$weekday = getWeekday($monthday);
$row[$monthday . " - " . $weekday] = $row[$monthday];
unset($row[$monthday]);
}
return $row;
}
)))
...
You would need to write function getWeekday to compute weekday from monthday. Then try the DataTables set up in my previous post. Thanks!
here is the image.
another two questions please: 1. in the column substitute_teacher_id I want the name of the teacher to appear only one time for each group of rows. meaning I want to rowspan it. and same for the column teacher_id.
Hi Ron,
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k)) $k = 1*k;
if ($k >= 1 && $k <= 30) { //i.e $k is month day
$weekday = getWeekday($k);
$newRow[$k. " - " . $weekday] = $row[$k];
} else {
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
Hope that help!
ok its working tnx. 1. in the column substitute_teacher_id I want the name of the teacher to appear only one time for each group of rows. meaning I want to rowspan it. and same for the column teacher_id. 2. this code is not working now since we shifted from table to datatable
"grouping"=>array(
"substitute_teacher_id"=>array(
"calculate"=>array(
"{sumAmount}"=>array("sum","total_hours"),
),
"bottom"=>'<b>{substitute_teacher_id}: {sumAmount}</b>',
),
),
"excludedColumns"=>array("total_hours")
Hi Ron,
To use grouping in datagrid/DataTables, please use the following format:
DataTables::create(array(
...
"options" => array(
'rowGroup' => [
'dataSrc' => [10], //10 is the order of the column you want to group
"endRender" => "function ( rows, group ) {
var salaryAvg = rows
.data()
.pluck(5) //5 is the order of the salary column
.reduce( function (a, b) {
return a + b.replace(/[^\d]/g, '')*1;
}, 0) / rows.count();
salaryAvg = $.fn.dataTable.render.number(',', '.', 0, '$').display( salaryAvg );
var ageAvg = rows
.data()
.pluck(3) //3 is the order of the age column
.reduce( function (a, b) {
return a + b*1;
}, 0) / rows.count();
return $('<tr/>')
.append( '<td colspan=3>Averages for '+group+'</td>' )
.append( '<td>'+ageAvg.toFixed(0)+'</td>' )
.append( '<td/>' )
.append( '<td>'+salaryAvg+'</td>' );
}"
],
),
...
));
I use this grouping with averaging example from this link:
https://datatables.net/extensions/rowgroup/examples/initialisation/customRow.html
You could easily replace average with sum by removing the dividing by rows.count(). Let me know if you have any question. Thanks!
Hi Ron,
Sorry for missing your excel export question. At the moment our excel Table widget hasn't had the ability to export grouping and complex headers yet. It's in our roadmap for the Excel package but it could take some time to develop, probably several months in the future.
If and when it's tested or released you would get the update automatically with your current KoolReport Pro license. Let us know your thought. Thanks!
Hi Ron,
datagrid/DataTables is a html/js/css widget which has features such as complex headers and grouping as I promised.
The ability to export to Excel depends on the Excel package which has its own Table, Chart, PivotTable widgets. At the moment the Excel package has not supported complex headers or grouping yet.
If you need this feature as soon as possible I would suggest using a client-side solution like this:
https://datatables.net/extensions/buttons/examples/initialisation/export.html
In order to do this please download the datatables.min.js including all the plugins in this example and replace the file KoolReport/datagrid/DataTables/datatables.min.js with it:
https://datatables.net/download/
Hope this helps! Let us know if there's any difficulty. Thanks!
tnx sir. I have a problem in my header columns label. I do a map process to change the label names to the relevent language and when I var_dum the $meta I see that the column names were changed correctly but when the report is displays I still see the original datasource column names
this is the code
->pipe(new \koolreport\processes\Map(array(
"{meta}" => function($meta) {
$colMetas = $meta["columns"];
$index = 1;
foreach ($colMetas as $colKey => $colMeta) {
if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
$colMetas[$colKey]["label"] = lang($colKey);
$colMetas[$colKey]["type"] = "string";
}
$index++;
}
$meta["columns"] = $colMetas;
//echo "<pre>";
//var_dump($meta);
//echo "</pre>";
return $meta;
}
)))
DataTables::create(array(
"dataSource"=>$this->dataStore("st"),
"options"=>array(
"searching"=>false,
"paging"=>false,
'columnDefs' => array(
array(
'visible' => false,
'targets' => [37], //hide the first column
)
),
'rowGroup' => [
'dataSrc' => [0], //10 is the order of the column you want to group
'endRender' => "function ( rows, group ) {
var salaryAvg = rows
.data()
.pluck(5) //5 is the order of the salary column
.reduce( function (a, b) {
return a + b.replace(/[^\d]/g, '')*1;
}, 0) / rows.count();
salaryAvg = $.fn.dataTable.render.number(',', '.', 0, '$').display( salaryAvg );
return $('<tr/>')
.append( '<td colspan=39>סה״כ שעות מילוי מקום למורה '+group+'</td>' )
.append( '<td/>' )
.append( '<td>'+salaryAvg+'</td>' );
}"
],
),
'complexHeaders' => true,
'headerSeparator' => ' - ',
'cssClass'=>array(
'table'=>'table table-hover table-bordered',
'tr'=>'cssItem',
'td'=>function($row,$colName)
{
return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'cssItem rext-right' : 'cssItem text-center';
},
'th'=>function($colName)
{
return in_array($colName, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','group_name')) ? 'cssHeader text-right' : 'cssHeader text-center';
},
),
))
this is the setup of the report
function setup()
{
$this->src('sbm')
->query('CALL getSubstituteTeacherHourListPerMonth(:year, :date)')
->params(array(
":year"=>$this->params["year"],
":date"=>$this->params["date"]
))
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$days = array('א','ב','ג','ד','ה','ו','ש');
$month = date('m', strtotime($this->params["date"]));
$year = date('Y', strtotime($this->params["date"]));
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k))
$k = 1*$k;
if ($k >= 1 && $k <= 31) { //i.e $k is month day
$weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
$newRow[$k. " - " . $days[$weekday]] = $row[$k];
} else {
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
->pipe(new \koolreport\processes\Map(array(
"{meta}" => function($meta) {
$colMetas = $meta["columns"];
$index = 1;
foreach ($colMetas as $colKey => $colMeta) {
if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
$colMetas[$colKey]["label"] = lang($colKey);
$colMetas[$colKey]["type"] = "string";
}
$index++;
}
$meta["columns"] = $colMetas;
//echo "<pre>";
//var_dump($meta);
//echo "</pre>";
return $meta;
}
)))
->pipe($this->dataStore("st"));
}
Hi Ron,
Yes, you are correct. The complex headers feature must modify/split the table's header content and because it performs modification on the column name, the column label isn't shown. Even if we want to show the label, says a column is {name: "group 1 - cat 1.1", label: "my column label"} which text should be shown for header cat1.1?
If you really want to to localize the headers I would suggest changing the column name directly and not the column label, i.e apply the lang() function in the first Map process. Thanks!
how to I join the two map processes. actually if I can change the meta in the first process then I do not need the second one. can you please tell me how do I change the label of the meta from the first map process?
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$days = array('א','ב','ג','ד','ה','ו','ש');
$month = date('m', strtotime($this->params["date"]));
$year = date('Y', strtotime($this->params["date"]));
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k))
$k = 1*$k;
if ($k >= 1 && $k <= 31) { //i.e $k is month day
$weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
$newRow[$k. " - " . $days[$weekday]] = $row[$k];
} else {
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
Hi Ron,
One Map process allows for both "{value}" and "{meta}" properties. However, in your case I think we could change the 2nd Map process like this:
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$days = array('א','ב','ג','ד','ה','ו','ש');
$month = date('m', strtotime($this->params["date"]));
$year = date('Y', strtotime($this->params["date"]));
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k))
$k = 1*$k;
if ($k >= 1 && $k <= 31) { //i.e $k is month day
$weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
$newRow[$k. " - " . $days[$weekday]] = $row[$k];
} else {
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $meta) {
$colMetas = $meta["columns"];
$index = 1;
$newRow = [];
foreach ($colMetas as $colKey => $colMeta) {
if (! isset($row[$colKey])) continue;
if ( $index < 7 || $index > sizeof($colMetas) - 2 ) {
$newKey = lang($colKey);
$newRow[$newKey] = $row[$colKey];
} else {
$newRow[$colKey] = $row[$colKey];
}
$index++;
}
return $newRow;
}
)))
Let me know how it works. Thanks!
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$days = array('א','ב','ג','ד','ה','ו','ש');
$month = date('m', strtotime($this->params["date"]));
$year = date('Y', strtotime($this->params["date"]));
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k))
$k = 1*$k;
if ($k >= 1 && $k <= 31) { //i.e $k is month day
$weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
$newRow[$k. " - " . $days[$weekday]] = $row[$k];
} else {
if (shouldApplyLang($k)) $k = lang($k); //write your shouldApplyLang function
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
This is the code of the setup:
`
function setup()
{
$this->src('sbm')
->query('CALL getSubstituteTeacherHourListPerMonth(:year, :date)')
->params(array(
":year"=>$this->params["year"],
":date"=>$this->params["date"]
))
->pipe(new \koolreport\processes\Map(array(
"{value}" => function($row, $metaData, $index, $mapState) {
$days = array('א','ב','ג','ד','ה','ו','ש');
$month = date('m', strtotime($this->params["date"]));
$year = date('Y', strtotime($this->params["date"]));
$newRow = [];
foreach ($row as $k => $v) {
if (is_numeric($k))
$k = 1*$k;
if ($k >= 1 && $k <= 31) { //i.e $k is month day
$weekday = date('w', strtotime($year.'-'.$month.'-'.$k));
$newRow[$k. " - " . $days[$weekday]] = $row[$k];
} else {
//echo $k."<br>";
if ( in_array($k, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','hour_group_id', 'cost', 'total_travel_cost') ) ) {
$k = lang($k);
}
$newRow[$k] = $row[$k];
}
}
return $newRow;
}
)))
->pipe($this->dataStore("st"));
}
it gives an error :
A PHP Error was encountered
Severity: Notice
Message: Undefined index: סה״כ עלות נסיעות
Filename: reports/MyReport.php
Line Number: 50
Backtrace:
File: /var/www/parashapp.com/public_html/dev/sbm/application/reports/MyReport.php
Line: 50
Function: _error_handler
File: /var/www/parashapp.com/public_html/dev/sbm/application/libraries/koolreport/core/src/processes/Map.php
Line: 137
Function: {closure}
File: /var/www/parashapp.com/public_html/dev/sbm/application/libraries/koolreport/core/src/core/Node.php
Line: 244
Function: onInput
File: /var/www/parashapp.com/public_html/dev/sbm/application/libraries/koolreport/core/src/core/Node.php
Line: 201
Function: input
File: /var/www/parashapp.com/public_html/dev/sbm/application/libraries/koolreport/core/src/datasources/PdoDataSource.php
Line: 487
Function: next
File: /var/www/parashapp.com/public_html/dev/sbm/application/libraries/koolreport/core/src/KoolReport.php
Line: 315
Function: start
File: /var/www/parashapp.com/public_html/dev/sbm/application/controllers/Report.php
Line: 19
Function: run
File: /var/www/parashapp.com/public_html/dev/sbm/index.php
Line: 315
Function: require_once
it seems that the map process is running on each row of the table which is pointless. the only rows I needed is the 1st and 2nd rows of the header in order to update the lang() parameters.
$originalK = $k;
if ( in_array($k, array('substitute_teacher_id','id_number','full_address','bank_account','teacher_id','hour_group_id', 'cost', 'total_travel_cost') ) ) {
$k = lang($k);
}
$newRow[$k] = $row[$originalK];
Regarding your opinion about the Map process, each row of KoolReport's pipe data is an associative array so you need to change every row in order to change the column names.
Hi Ron,
Please set the column meta for "total_travel_cost" like this:
->pipe(new \koolreport\processes\ColumnMeta(array(
"total_travel_cost" => array(
"type" => "number",
"decimals" => 2,
"decimalPoint" => ".",
"thousandSeparator" => ",",
)
)))
or set the column option directly with DataTables:
DataTables::create(array(
...
"columns" => array(
...
"total_travel_cost" => array(
"type" => "number",
"decimals" => 2,
"decimalPoint" => ".",
"thousandSeparator" => ",",
),
...
),
...
));
when I do this
`
->pipe(new \koolreport\processes\ColumnMeta(array(
"total_travel_cost" => array(
"type" => "number",
"decimals" => 2,
"decimalPoint" => ".",
"thousandSeparator" => ",",
)
)))
`
I still get in one of the rows values of the column total_travel_cost the value 15.600000000000001
This is the report view code
Table::create(array(
"dataSource"=>$this->dataStore("st"),
"columns"=>array(
"hour_num"=>array(
'label'=>'Hour',
),
"1"=>array(
"label"=>"Sunday",
"type"=>"string",
),
"2"=>array(
"label"=>"Monday",
),
"3"=>array(
"label"=>"Tuesday",
),
"4"=>array(
"label"=>"Wednesday",
),
"5"=>array(
"label"=>"Thursday",
),
"6"=>array(
"label"=>"Friday",
),
),
"cssClass"=>array(
"table"=>"table table-bordered",
"th"=>"table-dark",
)
))
using this code I can see only the cube columns but no the data inside the row.
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo