Koolreport Pivot Value setup - not displaying data

saiful commented on Feb 16, 2022

Hi, Im trying to create report using PivotTable, but i have some difficulties to setting the pipe or properties for the report.

the Voucher No & Remark column are still blank at the moment, what i need to set (the aggregate or PivotTable properties) to display the content? the value can be string or numeric.

Here is my code: Setup:

->pipe(new Pivot([
            "dimensions"=>[
                "row"=>"Location Name, Account Name, Customer Name",
            ],
            "aggregates"=>[
                "sum"=>"Debit, Credit",
                "Voucher No, Remark"
            ]
        ]))

Controller:

"headerMap"=>[
                    "Voucher No"=>"Voucher No",
                    "Debit - sum"=>"Debit",
                    "Credit - sum"=>"Credit",
                    "Remark"=>"Remark"
                ],
                "measures"=>[
                    "Voucher No",
                    "Debit - sum",
                    "Credit - sum",
                    "Remark"
                ],

Sebastian Morales commented on Feb 16, 2022

You have error in your array syntax. Pls change:

            "aggregates"=>[
                "sum"=>"Debit, Credit",
                "Voucher No, Remark" // there's no aggregate
            ]

to this:

            "aggregates"=>[
                "sum"=>"Debit, Credit, Voucher No, Remark"
            ]

or:

            "aggregates"=>[
                "sum"=>"Debit, Credit",
                "count" => "Voucher No, Remark"
            ]

saiful commented on Feb 16, 2022

i have been tested both :

but the result still blank

saiful commented on Feb 16, 2022

and when i try export to excel, the voucher value was exist

Sebastian Morales commented on Feb 16, 2022

Don't set "measures" in PivotTable so that it uses all available measures by default. Or set "measures" like this:

PivotTable::create(array(
    ...
                "measures"=>[
                    "Voucher No - count", // add count aggregate
                    "Debit - sum",
                    "Credit - sum",
                    "Remark - count" // add count aggregate
                ],
));

saiful commented on Feb 16, 2022

if i don't use measures, the result looks like this:

and when i use count, it don't show the real values:

Sebastian Morales commented on Feb 16, 2022

I meant commenting out the whole "measures" property together. If you only set credit and debit in measures, of course PivotTable only shows those two measures. As for count aggregate its grand total should be the total number of your data rows, not the sum of all values.

saiful commented on Feb 16, 2022

i commented out the measure, but the result is only 0:

Sebastian Morales commented on Feb 16, 2022

Pls make sure that the field names "Voucher No" and "Remark" are correct case sensitively. If a field name is incorrect all of its row values are null its aggregation will be empty. To add null row to count you might want to convert all null value before Pivot process like this:

//MyReport.php

->pipe(new \koolreport\processes\Map([
   "{value}" => function($row)
        if (!isset($row["Voucher No"])) $row["Voucher No"]) = ""; // assuming "Voucher No" is a correct column name
        if (!isset($row["Remark"])) $row["Remark"]) = "";
        return $row;
    } 
]))
->pipe(new Pivot(...))

saiful commented on Feb 16, 2022

the fields name are correct, i have been add Map process too, this is my pipe process:

->pipe(new ColumnRename([
            'LOC_NM'=>'Location Name',
            'ACC_NM'=>'Account Name',
            'CST_NM'=>'Customer Name',
            'VCR_NO'=>'Voucher No',
            'DB_AMT'=>'Debit',
            'CR_AMT'=>'Credit',
            'ORD_NO'=>'Order No',
            'REMARK'=>'Remark'
        ]))
        ->pipe(new ColumnMeta([
            "Debit"=>[
                "decimals"=>2,
                "thousandSeparator"=>".",
                "decimalPoint"=>",",
            ],
            "Credit"=>[
                "decimals"=>2,
                "thousandSeparator"=>".",
                "decimalPoint"=>",",
            ],
        ]))
        ->pipe(new Map([
            "{value}" => function($row){
                 if (!isset($row["Voucher No"])) $row["Voucher No"] = "";
                 if (!isset($row["Remark"])) $row["Remark"] = "";
                 return $row;
             } 
         ]))
        ->pipe(new Pivot([
            "dimensions"=>[
                "row"=>"Location Name, Account Name, Customer Name",
            ],
            "aggregates"=>[
                "sum"=>" Voucher No, Debit, Credit, Remark"
            ],
            // "aggregates"=>[
            //     "sum"=>"Debit, Credit",
            //     "count" => "Voucher No, Remark"
            // ]
        ]))

Sebastian Morales commented on Feb 16, 2022

What're the value of your Voucher No and Remark fields. If they are not numeric then "sum" will result in 0 like in your result.

saiful commented on Feb 17, 2022

Voucher No value is alphanumeric, and Remark value is string .

Sebastian Morales commented on Feb 17, 2022

How do you intend to "sum" alphanumeric and string values?

saiful commented on Feb 17, 2022

no, i just following your instructions.

then what aggregate properties that i need to display string values? is it still possible to display the data in pivottable?

Sebastian Morales commented on Feb 17, 2022

You can use "count", "min", "max" for string values for a start.

saiful commented on Feb 17, 2022

great, it works! i use "max" & "min" properties ("count" is not working).

now it just 1 little problem, to remove the total value of Voucher No and Remark.

Sebastian Morales commented on Feb 17, 2022

Pls inspect the cell you want to hide content to see its CSS classes and attributes. In your case it is probably something like:

<style>
    .pivot-data-cell-row-total[data-data-field="0"], /* change this data field to 0 for Voucher No or 3 for Remark */
    .pivot-data-cell-row-grand-total[data-data-field="0"] /* hide content for both total and grand total rows */
    { 
        visibility: hidden;
    }
</style>

saiful commented on Feb 17, 2022

works well, thanks a lot.

KoolReport's Forum

Koolreport Pivot Value setup - not displaying data #2578

Build Your Excellent Data Report