KoolReport's Forum

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

Aggregate table footer on certain rows only #1612

Open Sergio opened this topic on on Sep 8, 2020 - 9 comments

Sergio commented on Sep 8, 2020

Hey, I have a koolreport table that has cube process and is also displaying footer with average aggregation. I was wondering if i am able to calculate the avg by using certain rows, for example;

I cannot show the full table due to confidential data. In the last row before footer, there are cells with 0 value in it and it will affect the average calculation. I want to only calculate the footer based on cells that has value in it. Is it possible? Please keep in mind that i am using cube process for this table, hence any solution that requires me to create a footer syntax like this example;

Is not working for me because this will overwrite the columns that i created using the cube process syntax. Or maybe is there a way to implement the syntax above without overwriting the cube process?

Please let me know the solution if there is any. Thanks in advance!

David Winterburn commented on Sep 9, 2020

Hi Sergio,

Would you please post the php code for your report's setup and view for us to better understand your situation? Thanks!

Sergio commented on Sep 9, 2020

Okay i'm gonna specify the problem for a bit.. Let's say that i have a table of :

And let's say im going to use cube process for this table, with these details : row : name column : month_year avg : value And in the column meta of value, i am going to set the footer with avg aggregation. And i am going to exclude the {{all}} column. The supposed result will be :

Now as you can see on March 20, John has no data but cube process automatically makes it 0 and counts it in the average calculation at footer. How do i at least exclude those no data's from the database in the footer calculation, so that the average will be only based on those who actually have the data? Keep in mind, that if there IS a data that has 0 in it ( for example John in March 20 DOES have a row of data and its value is zero), i still need to include it. I just need to exclude if there is no data in the database but cube process generates it on its own. How can this work? Is there a way to do the solution i needed above? Or probably is there a way to change the auto generated value of No Data into NULL instead of 0, so that it might can be excluded from average calculation?

Sorry if it's unclear, i am explaining the best i could. Thankyou!!

David Winterburn commented on Sep 10, 2020

Hi Sergio,

Thanks for your detail explanation. At the moment the default empty value for the Cube process is 0. We will consider allowing users to change this default empty value in the next release of KoolReport. Meanwhile, you could fix it yourself by opening the file koolreport/cube/processes/SuperCube.php and replace this line:

    protected $emptyValue = 0;

with this one:

    protected $emptyValue = null;

That should make the Cube's result to use null value where there's no data. Thanks!

Sergio commented on Sep 10, 2020

Hey David,

The supposed solution does not work for me, the value returned in the cube process is still 0. I read a topic in this link before :

https://www.koolreport.com/forum/topics/1027

And it said that it has to do with the datasource. probably the problem is originated from there? I am using mysqldatasource, can you probably provide me with the way to do that?

Anyways, can you tell me a bit of how cube process avg footer works? i am afraid that even though we managed to make the value become null and not converted, the average will still include the cell in the calculation. Please keep that in mind. Thanks!!

David Winterburn commented on Sep 10, 2020

Hi Sergio,

Per your screenshot I think the Avg is performed by Table/DataTables widget. If you want to use Cube's avg operator this code should be enough:

->pipe(new Cube(array(
    "column" => "month_year",
    "avg" => "value"
)))

For Cube process, the "avg" value is formulated as "sum" value divided by "count" value. Both "sum" and "count" operators don't take into account of null value.

Sergio commented on Sep 10, 2020

Hi David, I See what you mean. But with cube avg process, it would process average per rows, not column. I needed average per column in the footer of the table, and it needs to exclude null values (The table must not auto convert null values to 0 as well )

Maybe you can provide me a way to do that? Thanks!

David Winterburn commented on Sep 10, 2020

Did you try my code:

->pipe(new Cube(array(
    "column" => "month_year",
    "avg" => "value"
)))

That should compute the average value for each month_year column.

If you need avg in the footer of the Cube's result table please refer to my previous post:

https://www.koolreport.com/forum/topics/1612#p8670

In any case, if there's still confusion please post your report's php code for us to better understand your requirement. Thanks!

Sergio commented on Sep 15, 2020

Hello David,

That should compute the average value for each month_year column.

I am only confused in this statement, because what i know is :


->pipe(new Cube(array(
    "column" => "month_year",
    "avg" => "value"
)))

The "avg"=>"value" syntax will average each row of value in the table and will be presented in the {{all}} column. Like so :

As you can see its averaging per row and not per month_year column. Correct me if im wrong. Thanks!!

By the way, here is a snippet of code that i am currently using to produce the avg footer :

->pipe(new ColumnMeta(array(
            "value"=>array(
                    "type"=>"number",
                    "suffix"=>"s",
                    "footer"=>"avg",             
                    ),
        )))
->pipe(new Cube(array(
            "row"=>"#,name",
            "column"=>"month_year",
            "avg"=>"value",
            "sort"=>false,
        )))

you can see the "footer"=>"avg" syntax in value ColumnMeta, which changing the emptyValue in

koolreport/cube/processes/SuperCube.php

does not work and still counts the empty value. The empty values are still presented as 0 as well in the table.

David Winterburn commented on Sep 15, 2020

Please replace your code with this:

        ->pipe(new ColumnMeta(array(
            "value"=>array(
                    "type"=>"number",
                    "suffix"=>"s",
                    //"footer"=>"avg",              
                    ),
        )))
        ->pipe(new Cube(array(
            //"row"=>"#,name",
            "column"=>"month_year",
            "avg"=>"value",
            "sort"=>false,
        ))) 

This should get one row of average for all "month_year"s.

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
None yet

None