Some questions about Cube package

Eugene commented on Jun 5, 2020

Hi,

I am using the Cube package in my report and need some features but I don't know how to realize them.

Let me explain what I need using your example:

My questions:

How can I add a footer/header with sums by columns - to get total sales for Vintage Cars, Classic Cars, etc? In my case, the amount of columns is much more than in this example so I can not name each column in the view.
How to arrange the columns based on the sum values? Minimal first for example?

So I would like to get something like this as the result

David Winterburn commented on Jun 5, 2020

Hi Eugene,

Please try widget Table's aggregated footer property to see if it solves your problem:

https://www.koolreport.com/docs/koolphp/table/#table-settings-aggregated-footer

Thanks!

Eugene commented on Jun 5, 2020

Thanks David, I will try but as I understand it can help with the footer but not with the column order?

How can I set the cube's columns in a specific order? Please have a look at the second table in my first message.

David Winterburn commented on Jun 5, 2020

Hi Eugene,

I forgot your 2nd point. So I think you could try to reverse the row and column properties in the Cube process, like this:

->pipe(new Cube(array(
    "row" => "productLine",
    "column" => "customerName",
    "sum" => "sales"
)))

Now the result will have customers as columns and there's one special column called {{all}} which is the sum of all customers. We will sort the result by this {{all}} column in ascending order with the Sort process.

Finally use either the Transpose or Transpose2 process to reverse the result's rows and columns to get the table you want. After tranposing you might want to rename some columns.

Please try this approach and let us know if there's any difficulty you have. Thanks!

Eugene commented on Jun 5, 2020

Yes, it works, but this way I lost the total amount by rows :-) that I used to order the rows. Maybe I can use CalculatedColumn process to add this column but how to do it if I don't know the names and number of columns that I need to sum.

Eugene commented on Jun 8, 2020

up

David Winterburn commented on Jun 8, 2020

Would you please print some screenshots for us to better understand the issue now? Thanks!

Eugene commented on Jun 8, 2020

Ok Look - this is my table before I asked the question

As you see there is a {all} column with sums of the values in each row.

My question was: "How to arrange the columns based on the sum values?". You gave me the solution and I followed it - I reversed the row and column properties in the Cube process, sorted by {{all}} column, and used the transpose process. Finally I got

My columns are now ordered as I need but I lost the column with the sums of the values by row (like at the first screenshot) - I need it also.

David Winterburn commented on Jun 8, 2020

Ah, this is a tricky one. But to compute an "{{all}}" column is not very hard. I would suggest using the Map process like this:

->pipe (new \koolreport\processes\Map(array(
    "{value}" => function($row) {
        $row["{{all}}"] = 0;
        foreach ($row as $k => $v)
            if (is_numeric($v)) $row["{{all}}"] += $v;
        return $row;
    }
)))

This should add an "{{all}}" column which is summed from all columns.

Eugene commented on Jun 8, 2020

Thank you, David Oh, Map process is always a dark matter for me. :-)

Thank you it works but I need this "{{all}}" column as the second column but it is the last column now. Is it possible to move it?

David Winterburn commented on Jun 9, 2020

It's possible to move array's elements around with array_splice but we won't need it here. Just create a new row like this and return it instead of the original row:

->pipe(new \koolreport\processes\Map(array(
    "{value}" => function($row) {
        $firstColumn = array_keys($row)[0];
        $newRow = [];
        $newRow[$firstColumn] = $row[$firstColumn];
        $newRow["{{all}}"] = 0;
        foreach ($row as $k => $v) {
            $newRow[$k] = $v;
            if (is_numeric($v)) $newRow["{{all}}"] += $v;
        }
        return $newRow;
    },
    "{meta}" => function($meta) {
        $colMetas = $meta["columns"];
        $columns = array_keys($colMetas);
        $firstColumn = $columns[0];
        $newColMetas = [];
        $newColMetas[$firstColumn] = $colMetas[$firstColumn];
        $newColMetas["{{all}}"] = ["type" => "number"];
        foreach ($colMetas as $col => $colMeta) 
            $newColMetas[$col] = $colMeta[$col];
        $meta["columns"] = $newColMetas;
        return $meta;
    }
)))

Hope this works for your case.

Eugene commented on Jun 9, 2020

Thank you, David. It is again the Koolreport magic.

It works!

PS there is a small typo in the line $meta["columns"] = $newcolMetas; Must be $meta["columns"] = $newColMetas;

PPS and here if (is_numeric($v)) $row["{{all}}"] += $v; must be if (is_numeric($v)) $newRow["{{all}}"] += $v;

David Winterburn commented on Jun 9, 2020

Thanks, Eugene! I will edit the answer according to your find out so other users can copy the code without error.

Eugene commented on Jun 9, 2020

Thanks again! Have a good day.

KoolReport's Forum

Some questions about Cube package #1474

Build Your Excellent Data Report