KoolReport's Forum

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

How to make running total of both sub group and grand total #1429

Open KoolReport opened this topic on on May 7, 2020 - 1 comments

KoolReport commented on May 7, 2020

Questions:

(We received an interesting question from one of our users and thought that it would be useful to show you the solution and the flexibility of KoolReport)

During the grouping of KoolPHP Table we can have the group footer summary example total sales value of each product range. I want to know if I can add AcumulativeColumn that will columulate this value row by row and reset the column at the end of each group and also have another AcumulativeColumn that will cumulate the value till the end of the records by Grand Total.

Example:

KoolReport commented on May 7, 2020

Answer:

Let say we have dataStore with product_range, product_name and sales. We will use grouping and custom column feature of KoolPHP Table

<?php
Table::create(array(
    "dataSource"=>$this->dataStore("result"),
    "grouping"=>array(
        "product_range"=>array(
            "{sumSales}"=>array("sum","sales"),
            "bottom"=>"<b>Total: {sumSales}</b>"
        )
    ),
    "removeDuplicate"=>array("product_range"),
    "showFooter"=>true,
    "columns"=>array(
        "product_range"=>array(
            "label"=>"Product Range",
            "footerText"=>"Grand Total",
        ),
        "product_name"=>array(
            "label"=>"Product Name",
        ),
        "sales"=>array(
            "label"=>"Sales",
            "footer"=>"sum",
        ),
        array(
            "label"=>"CumulativeByRange",
            "value"=>function($row) use (&$range,&$range_total) {
                //Everytime product range is changed,a new group is started, we reset range total
                if($row["product_range"]!=$range) {
                    $range = $row["product_range"];
                    $range_total = 0;
                }
                $range_total += $row["sales"];
                return $range_total;
            }
        ),
        array(
            "label"=>"CumulativeByGrandTotal",
            "value"=>function($row) use (&$total){
                $total += $row["sales"];
                return $total;
            }
        )
    )
));
?>

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
wiki

None