Custom total PivotMatrix

Rafa commented on May 4, 2019

Hi,

I need to add some extra TOTAL rows in my PivotMatrix.

Example:

---------- COLUMN 1 ---- COLUMN 2 ---- COLUMN 3

ROW 1

ROW 2

Custom total: SUM of ROW 1 and ROW 2

ROW 3

ROW 4

Custom total: SUM of ROW 3 and ROW 4

ROW 5

ROW 6

Default total

I really need it to be a PivotMatrix. I know when the user change the pivotmatrix attributes by default configuration will be lost, but I need the first vision to be like I said in the example.

Can you help me, please?

Thanks!

David Winterburn commented on May 7, 2019

Hi Rafa,

We can't think of an exact solution for your requirement but you could try this near one. Says your pivot setup is like this:

->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => "colField1",
                "row" => "rowField1",
            ),
            ...
)))

We would add a fake row field based on rowField1 to group ROW1 and ROW2, ROW3 and ROW4, ROW5 and ROW6 together:

->pipe(new \koolreport\processes\Map([
    "{value}" => function($row) {
        if ($row["rowField1"] == "ROW 1" || $row["rowField1"] == "ROW 2")
            $row["fakeRowField"] = "Group 1";
        else if ($row["rowField1"] == "ROW 3" || $row["rowField1"] == "ROW 4")
            $row["fakeRowField"] = "Group 2";
        else if ($row["rowField1"] == "ROW 5" || $row["rowField1"] == "ROW 6")
            $row["fakeRowField"] = "Group 3";
        else 
            $row["fakeRowField"] = "Other groups";
        return $row; 
    }
]))
->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => "colField1",
                "row" => "fakeRowField, rowField1",
            ),
            ...
)))

This would place the fakeRowField group before the rowField1's ROW 1, ROW 2, etc and show the groups' total like your custom total as well as the overall total of all groups. Let us know if you need any adjustment for this solution. Thanks!

Rafa commented on May 9, 2019

All right, I can use this way! Thank so much!

Rafa commented on May 9, 2019

Looking again, there's a problem. There's a way that of SUM totals? Example:

Example:

---------- COLUMN 1 ---- COLUMN 2 ---- COLUMN 3

ROW 1

ROW 2

Custom total 1: SUM of ROW 1 and ROW 2

ROW 3

ROW 4

Custom total 2: SUM of ROW 3 and ROW 4 + Custom total 1

ROW 5

ROW 6

Default total

Can you help me, please?

Thanks!

David Winterburn commented on May 10, 2019

Hi Rafa,

If the problem is like your last example, I would add another fakeRowField2 before fakeRowField. This fakeRowField2 should put ROW 1, ROW 2, ROW 3, ROW 4 into one group and ROW 5, ROW 6 into another:

->pipe(new \koolreport\processes\Map([
    "{value}" => function($row) {
        if ($row["rowField1"] == "ROW 1" || $row["rowField1"] == "ROW 2")
            $row["fakeRowField"] = "Group 1.1";
        else if ($row["rowField1"] == "ROW 3" || $row["rowField1"] == "ROW 4")
            $row["fakeRowField"] = "Group 1.2";
        else if ($row["rowField1"] == "ROW 5" || $row["rowField1"] == "ROW 6")
            $row["fakeRowField"] = "Group 1.3";
        else 
            $row["fakeRowField"] = "Other groups 1";

        if ($row["rowField1"] == "ROW 1" || $row["rowField1"] == "ROW 2" 
            || $row["rowField1"] == "ROW 3" || $row["rowField1"] == "ROW 4")
            $row["fakeRowField2"] = "Group 2.1";
        else if ($row["rowField1"] == "ROW 5" || $row["rowField1"] == "ROW 6")
            $row["fakeRowField2"] = "Group 2.2";
        else 
            $row["fakeRowField"] = "Other groups 2";

        return $row; 
    }
]))
->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => "colField1",
                "row" => "fakeRowField2, fakeRowField, rowField1",
            ),
            ...
)))

Again, it's not the exact or the most beautiful solution but you could use it if it's close enough for you. If you don't need PivotMatrix's hierarchy for this I can help you with employing a datagrid to show an exact format like your example. Let us know what you think. Thanks!

Rafa commented on May 10, 2019

Hi David, unfortunaly I really need the hierarchy. But thank you anyway.

KoolReport's Forum

Custom total PivotMatrix #851

Build Your Excellent Data Report