KoolReport's Forum

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

Date Sorting in PivotTable #1567

Open Anil Vithani opened this topic on on Aug 14, 2020 - 1 comments

Anil Vithani commented on Aug 14, 2020

How can I sort teh data by date column in pivot table as you can see in image in DD/MM/YY form the dater having year 19 shoudl come on top but not coming

i am using laravel and my TeamReport.php is

<?php
namespace App\Reports;

use \koolreport\processes\Filter;
use \koolreport\pivot\processes\Pivot;
//use \koolreport\processes\Group;
use \koolreport\processes\Sort;

class TeamReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
    use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;
    
    
    protected function defaultParamValues()
    {
        return array(
            "dateRange"=>array(
               //date('Y-m-d H:i:s', strtotime('-1 year')),
                //date('Y-m-d H:i:s')
                date('Y-m-d H:i:s', strtotime('-9 months')),
                date('Y-m-d H:i:s', strtotime('-7 months'))
            ),
        );
    }
    
    protected function bindParamsToInputs()
    {
        return array(
            "dateRange"=>"dateRange",
        );
    }
	
    // By adding above statement, you have claim the friendship between two frameworks
    // As a result, this report will be able to accessed all databases of Laravel
    // There are no need to define the settings() function anymore
    // while you can do so if you have other datasources rather than those
    // defined in Laravel.
    /*
     * 
     * CV Sends
    1st Interviews
    Other Interviews
    Final interviews
    Committed jobs (A jobs)
    Hot Leads
    Job Ad leads
    New Logo Jobs
     */
    
    function setup()
    {

		$this->src('mysql')
			->query("SELECT DATE_FORMAT(SUBDATE(nr.created_date, WEEKDAY(nr.created_date)), '%Y-%m-%d') as commenced, 
            CONCAT('WC ',DATE_FORMAT(SUBDATE(nr.created_date, WEEKDAY(nr.created_date)), '%d/%m/%y')) as WeekCommencing,
            ct.team_name as TeamName, nr.user_name as UserName, 
        	SUM(IF((nr.notes_type = 139 AND nr.candidate_id> 0), 1, 0)) AS CvSends,
            SUM(IF(nr.notes_type = 114, 1, 0)) AS 1stInterviews,
            SUM(IF(nr.notes_type = 113, 1, 0)) AS FinalInterviews,
            SUM(IF(nr.notes_type = 85, 1, 0)) AS GradeAJobs,
            SUM(IF(nr.notes_type = 401, 1, 0)) AS HotLeads,
            SUM(IF((nr.notes_type = 104 AND nr.candidate_id> 0), 1, 0)) AS Deals

            FROM `notes_report` nr
            LEFT JOIN company_teams ct ON nr.team_id = ct.id
            WHERE nr.notes_type 
            IN(139,114,113,85,401,104 ) AND nr.created_date > :start AND nr.created_date < :end 	
            GROUP BY  WeekCommencing, TeamName, UserName
            ORDER BY commenced, TeamName ASC

            ")
            ->params(array(
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe(new Filter(array(
                //array('name', 'startsWith', 'a'),
               //array('year', '>', 2003),
            )))
            ->pipe(new Pivot(array(
                "dimensions"=>array(
                    //"column" => "year",
                    "row" => "WeekCommencing, TeamName, UserName",
                ),
                "aggregates"=>array(
                   "sum"=>"CvSends,1stInterviews,FinalInterviews,GradeAJobs,HotLeads,Deals",
                ),
                //'partialProcessing' => true, //add this line
            )))
            ->pipe(new Sort(array(
                "commenced"=>"asc"
            )))	
		

        ->pipe($this->dataStore('pivot'));
    }
}

and my TeamReport.view.php is

<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\inputs\DateRangePicker;
?>
<div class='report-content'>
  <div class="text-center">
    <h1>Pulse Pivot Report</h1>
    <p class="lead">
      Choose date range
    </p>
    <form method="post" >
        <div class="row">
            <div class="col-md-8 offset-md-2">
                <div class="form-group">
                <?php echo csrf_field() ?>
                
                <?php
                DateRangePicker::create(array(
                    "name"=>"dateRange"
                ))
                ?>
                </div>
                <div class="form-group text-center">
                    <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
                </div>
            </div>
        </div>
    </form>
  </div>
  

    <?php
    
      PivotTable::create(array(
        'dataStore'=>$this->dataStore('pivot'),
        'showDataHeaders' => true,
          'rowCollapseLevels' => array(0),
          'columnCollapseLevels' => array(0, 0, 0),
          'headerMap' => array(
              'CvSends - sum' => 'Cv Sends',
              '1stInterviews - sum' => '1st Interviews',
              'FinalInterviews - sum' => 'Final Interviews',
              'GradeAJobs - sum' => 'Grade A Jobs',
              'HotLeads - sum' => 'Hot Leads',
              'Deals - sum' => 'Deals',
          ),
          'rowSort' => array(
              'commenced' => 'asc',
          ),
          'columnSort' => array(
              'commenced' => 'asc',
              'TeamName' => 'asc'
           ),
          //'hideTotalRow' => true,
          //'hideTotalColumn' => true,
          //'hideSubtotalRow' => true,
          //'hideSubtotalColumn' => true,
        /*'headerMap' => function($v, $f) {
          switch ($v) {
              case 'CvSends': return 'Cand Added';
              case '1stIntervs': return 'Number of Sales';
              case 'FinalIntervs': return 'Average Sales';
              case 'GradeAJobs': return 'Year';
              case 'HotLeads': return 'Week';
              case 'Deals': return 'Day';
          }
          },*/
        /*"rowDimension"=>"row",
        "columnDimension"=>"column",
        'rowDimension'=>'row',
        'measures'=>array(
          'CvSends', 
          '1stIntervs',
        ),
          
        'rowSort' => array(
          'candidate_added - sum' => 'desc',
        ),
        'totalName' => 'All',
        'width' => '100%',
        'nameMap' => array(
          'candidate_added - sum' => 'Total Candidates Added',
          'candidate_added - count' => 'Number of Candidates',
        ),*/
      ));
    ?>
</div>
David Winterburn commented on Aug 17, 2020

Please try the property "rowSort" together with a custom function in PivotTable widget:

PivotTable::create(array(
    "rowSort" => array(
        "WeekCommencing" => function($v1, $v2) {
            //compare $v1 and $v2 here with your custom code depending on your WeekCommencing format 
            return $compareResult; // -1, 0, or 1
        }
    ),
)):

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

Pivot