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!

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!!

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!!

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!

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.