KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Request for a help in arranging database data into Pivot Table #345

Open shanaka perera opened this topic on on Jul 2, 2018 - 6 comments

shanaka perera commented on Jul 2, 2018

Assume that I have a database table like below :

With above given data, I need to create a Pivot Table structured like below :

As my first attempt, I tried with :

Report Class

        $this->src('konst_pro')
            ->query($query)
            ->pipe(new Pivot(array(
                'dimensions' => array(
                    //  'column' => 'site_name',
                    'row' => 'site_name, item_name, suborder_code, suborder_qty'
                ))))
            ->pipe($this->dataStore('sw_item_order_report'));

Report View

 $dataStore = $this->dataStore('sw_item_order_report');
        PivotTable::create(array(
            'dataStore'=>$dataStore
        ));

So it gives me below output :

Which is not what I expected.. So any help would be appreciable.

Thanks in advanced !

shanaka perera commented on Jul 3, 2018

FYI , I don't need the red colored text inside the table. Just included them for better understanding. :)

David Winterburn commented on Jul 3, 2018

Hi shanaka,

Please change your setup to the following:

->pipe(new Pivot(array(
  'dimensions' => array(
    'row' => 'site_name, item_name, suborder_code, suborder_date'
  ),
  "aggregates"=>array(
    "sum"=>"suborder_qty, total_reg",
  ),
)))

Let us know how this works for you. Thanks!

shanaka perera commented on Jul 3, 2018

@David, Your solution worked for me. I removed total_req column from the aggregates because it looks much cleaner now. Now my pivot table shown as following :

As you can observe from the picture, the underlined total columns are not useful to my report because the blue colored represent the total of all the items of given stage and red colored represent the total of all the items of all the stages. So any suggestion to get rid of those columns ??

My updated code so far :

$this->src('konst_pro')
            ->query($query)
            ->pipe(new Pivot(array(
                'dimensions' => array(
                    //  'column' => 'site_name',
                    'row' => 'site_name, stage_name, item_name, suborder_code, suborder_date'
                    // stage_name is another column of my table
                ),

                "aggregates" => array(
                   // "sum" => "suborder_qty, total_req", --> removed the total_req
                    "sum" => "suborder_qty",
                )

            )))
            ->pipe($this->dataStore('sw_item_order_report')); 
David Winterburn commented on Jul 4, 2018

Hi Shanaka,

Please follow this thread to see the way to hide your subtotal rows:

https://www.koolreport.com/forum/topics/134

Thanks!

shanaka perera commented on Jul 4, 2018

@David Yes, I saw that question and I don't think that hiding fornt-end html elements will effect on pdf generating report .. Isn't it ??

David Winterburn commented on Jul 4, 2018

Hi Shanaka,

Please put those hiding CSS rules in the PDF.view file when you export to pdf. It should hide the subtotal rows even in the pdf file. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

Pivot