KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
How to generate a grand total? #2583
If you used core/Table widget, pls try this footer property:
https://www.koolreport.com/docs/koolphp/table/#table-settings-aggregated-footer
Let us know if it works for you. Tks,
Hi Sebastian, I'm already using this table footer structure. I'll send you my code segment for full undestanding...
Data Setup:
protected function setup()
{
$vendas = new DataStore();
$this->src('vendas')
->pipe(new CopyColumn([
'TicketMedio' => 'ValorTelhas',
'TicketMedioPago' => 'ValorTelhasPago'
]))
->pipe(new CalculatedColumn([
'PesoMedio' => function ($data) {
if ($data['PesoTelhas'] > 0)
{
return $data['ValorTelhas'] / $data['PesoTelhas'];
}
return null;
},
'PesoMedioPago' => function ($data) {
if ($data['PesoTelhasPago'] > 0)
{
return $data['ValorTelhasPago'] / $data['PesoTelhasPago'];
}
return null;
}
]))
->saveTo($vendas)
->pipe(new Group([
'by' => ['Nome','DataPedido'],
'sum' => ['QtdVendas','ValorTelhas','PesoTelhas','QtdVendasPago','ValorTelhasPago','PesoTelhasPago',
'TicketMedio','PesoMedio','TicketMedioPago','PesoMedioPago'],
]))
->pipe($this->dataStore('vendas_por_vendedor_data'));
$vendas
->pipe(new Group([
'by' => ['Nome'],
'sum'=> ['ValorTelhas','PesoTelhas','ValorTelhasPago','PesoTelhasPago']
]))
->pipe($this->dataStore('vendas_por_vendedor'));
}
Table Creation
protected function setup()
{
$vendas = new DataStore();
$this->src('vendas')
->pipe(new CopyColumn([
'TicketMedio' => 'ValorTelhas',
'TicketMedioPago' => 'ValorTelhasPago'
]))
->pipe(new CalculatedColumn([
'PesoMedio' => function ($data) {
if ($data['PesoTelhas'] > 0)
{
return $data['ValorTelhas'] / $data['PesoTelhas'];
}
return null;
},
'PesoMedioPago' => function ($data) {
if ($data['PesoTelhasPago'] > 0)
{
return $data['ValorTelhasPago'] / $data['PesoTelhasPago'];
}
return null;
}
]))
->saveTo($vendas)
->pipe(new Group([
'by' => ['Nome','DataPedido'],
'sum' => ['QtdVendas','ValorTelhas','PesoTelhas','QtdVendasPago','ValorTelhasPago','PesoTelhasPago',
'TicketMedio','PesoMedio','TicketMedioPago','PesoMedioPago'],
]))
->pipe($this->dataStore('vendas_por_vendedor_data'));
$vendas
->pipe(new Group([
'by' => ['Nome'],
'sum'=> ['ValorTelhas','PesoTelhas','ValorTelhasPago','PesoTelhasPago']
]))
->pipe($this->dataStore('vendas_por_vendedor'));
}
Sorry, correcting
Table Creation
Table::create( [
"dataSource"=>$this->dataStore('vendas_por_vendedor_data'),
'showfooter' => true,
"columns"=>[
'Nome' => [
'label' => 'Vendedor',
'footerText' => "<b>Totais Gerais:</b>"
],
'DataPedido' => [
'label' => 'Data do Pedido',
'formatValue' => function ($value,$row) {
return $value->format('d/m/Y');
},
'cssStyle' => "text-align:center",
],
'QtdVendas' => [
'label' => 'Qtd Vendas',
'formatValue' => function ($value,$row) {
return formataValor($value,true,0);
},
'cssStyle' => 'text-align:center',
'footer' => function ($value) {
return formataValor($value->sum('QtdVendas'),true,0);
},
'footerText' =>"<b>@value</b>",
],
'ValorTelhas' => [
'label' => 'Valor da Venda',
'formatValue' => function ($value,$row) {
return formataValoresEmReais($value);
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return formataValoresEmReais($value->sum('ValorTelhas'));
},
'footerText' =>"<b>@value</b>",
],
'PesoTelhas' => [
'label' => 'Peso KG',
'formatValue' => function ($value, $row) {
return formataValor($value,true,2);
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return formataValor($value->sum('PesoTelhas'),true,2);
},
'footerText' => "<b>@value</b>"
],
'TicketMedio' => [
'label' => 'Ticket Médio',
'formatValue' => function ($value, $row, $cKey) {
return formataValoresEmReais($value);
//return ($row['QtdVendas'] > 0) ? formataValoresEmReais($value / $row['QtdVendas']) : 0.00 ;
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return $value->sum('QtdVendas');
// return ($value->sum('QtdVendas') > 0 ) ?
// formataValoresEmReais($value->sum('TicketMedio') / $value->sum('QtdVendas')) :
// 0.00;
},
'footerText' => "<b>@value</b>"
],
'PesoMedio' => [
'label' => 'Valor/KG',
'formatValue' => function ($value, $row) {
return formataValoresEmReais($value);
// return ($row['QtdVendas'] > 0) ? formataValoresEmReais($value / $row['QtdVendas']) : 0.00 ;
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return formataValoresEmReais($value->sum('PesoMedio'));
// return ($value->sum('PesoMedio') > 0) ? formataValoresEmReais($value->sum('PesoMedio')) : 0.00;
},
'footerText' => "<b>@value</b>"
],
'QtdVendasPago' => [
'label' => 'Qtd Vendas (Pg)',
'formatValue' => function ($value) {
return formataValor($value,true,0);
},
'cssStyle' => 'text-align:center',
'footer' => function ($value) {
return formataValor($value->sum('QtdVendasPago'),true,0);
},
'footerText' =>"<b>@value</b>",
],
'ValorTelhasPago' => [
'label' => 'Valor da Venda (Pg)',
'formatValue' => function ($value,$row) {
return formataValoresEmReais($value);
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return formataValoresEmReais($value->sum('ValorTelhasPago'));
},
'footerText' =>"<b>@value</b>",
],
'PesoTelhasPago' => [
'label' => 'Peso KG (Pg)',
'formatValue' => function ($value, $row) {
return formataValor($value,true,2);
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return formataValor($value->sum('PesoTelhasPago'),true,2);
},
'footerText' => "<b>@value</b>"
],
'TicketMedioPago' => [
'label' => 'Ticket Médio (Pg)',
'formatValue' => function ($value, $row) {
return formataValoresEmReais($value);
// return formataValoresEmReais($value / $row['QtdVendasPago']) ;
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return ($value->sum('QtdVendasPago') > 0.00)
? formataValoresEmReais($value->sum('ValorTelhaPago') / $value->sum('QtdVendasPago'))
: 0.00;
},
'footerText' => "<b>@value</b>"
],
'PesoMedioPago' => [
'label' => 'Valor/KG (Pg)',
'formatValue' => function ($value, $row) {
return formataValoresEmReais($value);
// return ($row['QtdVendasPago'] > 0)
// ? formataValoresEmReais($value / $row['QtdVendasPago'])
// : 0.00;
},
'cssStyle' => 'text-align:right',
'footer' => function ($value) {
return ($value->sum('QtdVendasPago') > 0.00)
? formataValoresEmReais($value->sum('PesoMedioPago') / $value->sum('QtdVendasPago'))
: 0.00;
},
'footerText' => "<b>@value</b>"
],
],
'grouping' => [
'Nome' => [
'calculate' => [
"{sumQtdVendas}" => ['sum','QtdVendas'],
"{sumValorTelhas}" => ['sum','ValorTelhas'],
"{sumPesoTelhas}" => ['sum','PesoTelhas'],
"{avgTicketMedio}" => ['avg','TicketMedio'],
// "{avgTicketMedio}" => function ($store) {
//
// },
"{avgPesoMedio}" => ['avg','PesoMedio'],
"{sumValorTelhasPago}" => ['sum','ValorTelhasPago'],
"{sumPesoTelhasPago}" => ['sum','PesoTelhasPago'],
"{avgTicketMedioPago}" => ['avg','TicketMedioPago'],
"{avgPesoMedioPago}" => ['avg','PesoMedioPago'],
],
'bottom' => "<td class='table-dark' colspan=2><b> Total do Vendedor: {Nome}</b></td>
<td style='text-align: center;'><b>{sumQtdVendas}</b></td>
<td style='text-align: right;'><b>{sumValorTelhas}</b></td>
<td style='text-align: right;'><b>{sumPesoTelhas}</b></td>
<td style='text-align: right;'><b>{avgTicketMedio}</b></td>
<td style='text-align: right;'><b>{avgPesoMedio}</b></td>
<td style='text-align: right;'><b>{sumValorTelhasPago}</b></td>
<td style='text-align: right;'><b>{sumPesoTelhasPago}</b></td>
<td style='text-align: right;'><b>{avgTicketMedioPago}</b></td>
<td style='text-align: right;'><b>{avgPesoMedioPago}</b></td>",
],
],
'sorting' => [
'Nome' => 'asc',
'DataPedido' => 'asc',
],
"cssClass" => [
'table' => 'table-primary table-striped table-hover table-bordered',
],
'groupCellsInColumns' => ['Nome'],
]);
?>
What a misspelling do! Thanks Sebastian.. Now The grand total is working! One last question: For subtotals, I've applied a 'table-dark' class for all TD's and obtained the wished results, using this code:
'bottom' => "<td class='table-dark' colspan=2><b> Total do Vendedor: {Nome}</b></td>
<td class='table-dark' style='text-align: center;'><b>{sumQtdVendas}</b></td>
<td class='table-dark' style='text-align: right;'><b>{sumValorTelhas}</b></td>
<td class='table-dark' style='text-align: right;'><b>{sumPesoTelhas}</b></td>
<td class='table-dark' style='text-align: right;'><b>{avgTicketMedio}</b></td>
<td class='table-dark' style='text-align: right;'><b>{avgPesoMedio}</b></td>
<td class='table-dark' style='text-align: center;'><b>{sumQtdVendasPago}</b></td>
<td class='table-dark' style='text-align: right;'><b>{sumValorTelhasPago}</b></td>
<td class='table-dark' style='text-align: right;'><b>{sumPesoTelhasPago}</b></td>
<td class='table-dark' style='text-align: right;'><b>{avgTicketMedioPago}</b></td>
<td class='table-dark' style='text-align: right;'><b>{avgPesoMedioPago}</b></td>",
But, when I'm generating the footer, I'm using just a html segment with bold:
'footerText' => "<b>@value</b>"
How can I apply a class='table-dark' for al TD's from grand total?
Hi, there's a "cssClass" property for "tf" (tfooter) which allows you to set css classes for the footer:
https://www.koolreport.com/docs/koolphp/table/#table-settings-cssclass-options
Then you could add css rule to the footer. Otherwise, you could just inspect the footer td element (right mouse click the td, choose "Inspect element") to see its default css classes, then add css rules for those classes.
If you only want to apply css rules for one particular table, just add the table's id or name for the rules like this:
<?php
Table::create(array(
"name" => "table_1",
...
));
?>
<style>
#table_1 ... {
...
}
</style>
'cssClass' using the array for 'td','th' and 'tf' are doing the job using a style. Is there a way to establish a CLASS, instead of attributing a style content directly.
'label' => 'Vendedor',
'footerText' => "<b>Totais Gerais:</b>",
'cssStyle' => [
'td' => "text-align:right",
'th' => "text-align:center;color:white;background-color:midnightblue",
'tf' => "text-align:center;color:white;background-color:midnightblue",
],
This way is working, I just don't think that's the most elegant way!
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