Hide values within a column based on another field

Martin Parkes commented on Mar 13

I want to be able to hide the values within the columns for 'net' and 'vatrate' when a database value 'expensetype' = 'Non-mileage' but still be able to run all calculations for sub totals and overall totals. I've highlighted on the image, what I'd like to be able to hide. I've tried various approaches and I tend to lose the values I'd like to retain. Probably something simple that I've missed it. So gone back to basic in a hope someone can help me sort this.

This my table code:

Table::create(array(
    "dataStore"=>$this->dataStore('data-core'),
    'showHeader'=>false,
    'showFooter'=>'bottom',
    "font" => "StyleScript-Regular",
    "grouping"=>array(
        "programmename"=>[
            "top"=>'<td class="main-header" colspan="9">{programmename}</td>'
        ],
        'expensetype'=>[
            "calculate"=>[
                "{sumTypeAmount}"=>["sum", "gross"],
                "{sumTypeNetAmount}"=>["sum", "net"],
                "{sumTypeVatAmount}"=>["sum", "vatrate"],
                "{sumMileage}"=>["sum", "miles"]
            ],
            "top"=>function($value){
                $ttl = $value['{sumTypeAmount}'];
                if($value['{expensetype}'] == 'Mileage') {
                    return '<strong>'.$value['{expensetype}'].'</strong><tr><th>Date</th><th>From</th><th>To</th><th>Reason</th><th>Miles</th><th>Return</th><th>Net</th><th>VAT</th><th>Gross</th></tr>';     
                } else {
                    return '<strong>'.$value['{expensetype}'].'</strong><tr><th colspan="3">Date</th><th colspan="5">Details</th><th>Gross</th></tr>';
                }
                
            },
            "bottom"=>function($value) {
                if($value['{expensetype}'] == 'Mileage') {
                    return '<td colspan="4" class="bg-white">&nbsp;</td><td class="bg-white bold subtotal">'.$value['{sumMileage}'].'</td><td class="bg-white">&nbsp;</td><td class="bg-white bold subtotal">'.$value['{sumTypeNetAmount}'].'</td><td class="bg-white bold subtotal">'.$value['{sumTypeVatAmount}'].'</td><td class="bg-white bold subtotal">'.$value['{sumTypeAmount}'].'</td>';
                } else {
                    return '<td colspan="8" class="bg-white">&nbsp;</td><td class="bg-white bold subtotal">'.$value['{sumTypeAmount}'].'</td>';
                }
            }
        ],
    ),
    'columns'=>[
        'entrydate'=>[
            'label'=>'Date',
            'displayFormat'=>'d/m/Y',
            'cssStyle'=>'padding-right:8px'
        ],
        'from'=>[
            'label'=>'From',
            'cssStyle'=>'width:10%',
            'formatValue'=>function($value, $row){
                return (empty($value)) ? '' : $value;
            }
        ],
        'to'=>[
            'label'=>'To',
            'cssStyle'=>'width:10%;padding-right:4px',
            'formatValue'=>function($value, $row){
                return (empty($value)) ? '' : $value;
            }
        ],
        'details'=>[
            'label'=>'Description',
            'cssStyle'=>'padding-right:8px;word-break: break-all'
        ],
        'miles'=>[
            'label'=>'Miles',
            'cssStyle'=>'width:8%',
            'formatValue'=>function($value, $row){
                return (empty($value)) ? '' : $value;
            },
            'footer'=>'sum',
            'footerText'=>'<span class="grand-total">@value</span>'
        ],
        'return'=>[
            'label'=>'Return',
            'cssStyle'=>'padding: 0 18px'
        ],
        'net'=>[
            'label'=>'Net',
            'prefix'=>'£',
            'decimals'=>2,
            'footer'=>'sum',
            'footerText'=>'<span class="grand-total">@value</span>'
        ],
        'vatrate'=>[
            'label'=>'VAT',
            'prefix'=>'£',
            'decimals'=>2,
            'footer'=>'sum',
            'footerText'=>'<span class="grand-total">@value</span>'
        ],
        'gross'=>[
            'label'=>'Gross',
            'prefix'=>'£',
            'decimals'=>2,
            'footer'=>'sum',
            'footerText'=>'<span class="grand-total">@value</span>'
        ]
    ],
    "cssClass"=>array(
        "table"=>"table table-bordered"
    )
));

Sebastian Morales commented on Mar 14

If you only want to hide certain values instead of changing it to empty one, use css class with function is the way:

https://www.koolreport.com/docs/koolphp/table/#table-settings-cssclass-options

Basically you add a css class to cell/td that meets your criteria and set the css rule display:none for that css class.

Martin Parkes commented on Mar 14

Could you expand on that please, if I want to hide the value for the 'net' value if it's zero if the $row['expensetype'] == 'Non-Mileage'. Have tried something like this, but didn't work

if((isset($row['expensetype']) && $row['expensetype'] === 'Non-Mileage') && $columnName === 'net') {
                return 'hide';
            }
            return '';

Sebastian Morales commented on Mar 20

Of course, you set css class for td based on your condition then add the following css rule to your report view page:

<style>
    td.hide {
        display: none;        
    }
</style>
<?php
Table::create(array(
    "cssClass"=>array(
        ...
        "td"=>function($row,$columnName){
            if((isset($row['expensetype']) && $row['expensetype'] === 'Non-Mileage') && $columnName === 'net') {
                return 'hide';
            }
            return '';
        },

Let us know how this works for you.

KoolReport's Forum

Hide values within a column based on another field #3263

Build Your Excellent Data Report