multiple row group in datagrid

saiful commented on Dec 21, 2021

hi, i want to create multilevel row group in a datagrid table like this:

but I'm having trouble making the group like the one in the red box.

this is the column name:

            'LOCATION_NM'=>'Location',
            'CURR'=>'Currency',
            'CURR_NM'=>'Currency Name',
            'ACC_NM'=>'Account Name',
            'PRE_CU_AMT'=>'Beginning Balance',
            'DR_CU_AMT'=>'Debit',
            'CR_CU_AMT'=>'Credit',
            'BAL_CU_AMT'=>'Last Balance'

can you please help me how to set the row group?

Sebastian Morales commented on Dec 21, 2021

This looks and functions more like a PivotTable than Datagrid with row groups. Pls have a look at these pivot examples:

https://www.koolreport.com/examples/reports/pivot/customers_categories_products/

https://www.koolreport.com/examples/reports/pivot/bun_template/

Let us know your opinion. Tks,

saiful commented on Dec 24, 2021

i have read the docs and looks like its suitable for my project, but i still have trouble to set properties between rows & columns

here is my code:

->pipe(new Pivot([
            "dimensions"=>[
                "column"=>"Location","Beginning Balance","Debit","Credit","Last Balance",
                "row"=>"Account Name","Currency Name",
            ],
            "aggregates"=>[
                "sum"=>"Beginning Balance","Debit","Credit","Last Balance",
                "count"=>"Location","Currency Name",
            ],
            // "fieldDelimiter" => " -||- ",
        ]))

PivotTable::create(["dataSource"=>$this->dataStore,
                "themeBase"=>"bs4",
                "rowDimension"=>"row",
                "columnDimension"=>"column",
                "showDataHeaders" => true,
                'template' => 'PivotTable-Bun',
                "rowCollapseLevels" => array(1),
                "columnCollapseLevels" => array(0),
                "width" => '100%',
                "measures"=>[
                    "Beginning Balance - sum"=>"Beginning Balance",
                    "Debit - sum"=>"Debit",
                    "Credit - sum"=>"Credit",
                    "Last Balance - sum"=>"Last Balance"
                ],
                // "rowSort"=>[
                //     "Account Name",
                //     "Beginning Balance"=>"asc",
                //     "Debit"=>"asc",
                //     "Credit"=>"asc",
                //     "Last Balance"=>"asc"
                // ]
            ];);

Sebastian Morales commented on Dec 24, 2021

I think you made a small mistake. Pls replace this line:

                "row"=>"Account Name","Currency Name", // this means "row"=>"Account Name", 0 => "Currency Name"

with this one:

                "row"=>"Account Name, Currency Name",

Replace similarly for "column", "sum", "count".

saiful commented on Dec 24, 2021

the row is OK, but the column still have problem

->pipe(new Pivot([

        "dimensions"=>[
            "column"=>"Beginning Balance", "Debit", "Credit", "Last Balance",
            "row"=>"Location, Currency Name, Account Name",
        ],
        "aggregates"=>[
            "sum"=>"Beginning Balance, Debit, Credit, Last Balance",
            // "count"=>"Location","Currency","Account Name",
        ],
        // "fieldDelimiter" => " -||- ",
    ]))

i want make header table like this

Sebastian Morales commented on Dec 24, 2021

Your "column" dimension has the same issue as "row" before. But in your case there's no need for a "column" dimension so just remove it. Your aggregate sum of multiple data fields should be enough.

saiful commented on Dec 24, 2021

yes, i mean to remove this header or replace with other text

Sebastian Morales commented on Dec 24, 2021

For "Total" text pls use totalName property:

https://www.koolreport.com/docs/pivot/pivottable_and_pivotmatrix/#properties-total-name

As for the data field zone pls use this in page CSS for now:

<!-- MyReport.view.php -->
<style>
    .pivot-data-field-zone:after {
        content: "Hello world"; /* any text value you want */
    }
    .pivot-data-field-content {
        display: none;
    }
</style>

There would be a map option to customize this zone in the next version of Pivot.

saiful commented on Jan 3, 2022

hi, i get error like this when i set the second pivot tables, the previous pivot was done right. what's wrong?

here is the code:

->pipe(new Pivot([
            "dimension"=>[
                "row"=>"Location, Account Name, Customer Name",
                "column"=>"Voucher No, Debit, Credit, Order No, Remark",
            ],
            "aggregates"=>[
                "sum"=>"Debit, Credit",
            ]
        ]))
        ->pipe($this->dataStore("FNAFAD0001_200"));

$properties_200     = [
                "dataSource"=>$report_200->dataStore('FNAFAD0001_200'),
                "themeBase"=>"bs4",
                "rowDimension"=>"row",
                "columnDimension"=>"column",
                "showDataHeaders" => true,
                // 'template' => 'PivotTable-Bun',
                "hideSubtotalRow"=>true,
                // "rowCollapseLevels" => array(1),
                "columnCollapseLevels" => array(0),
                "measures"=>[
                    "Debit - sum",
                    "Credit - sum",
                ],
            ];

saiful commented on Jan 3, 2022

solved, thanks

Sebastian Morales commented on Jan 4, 2022

Pls let us and other users know how you solved this issue. Tks,

KoolReport's Forum

Multiple row group in datagrid #2504

Build Your Excellent Data Report