KoolReport's Forum

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

Sorting and renaming of column contents #214

Open bysystem opened this topic on on Feb 8, 2018 - 19 comments

bysystem commented on Feb 8, 2018

Dear support team,

I have separeted the month contents (for example 12.2017 or 01.2018 etc.) in my datebase in "Months" (01 or 02) and "Years" (2017, 2018) an created a pivot table which works fine with following code:

	->pipe(new Map(array(
		  '{value}' => function($row, $metaData) {
			$date = explode('.', $row['FakdatZBCP']);
			$row['Year'] = $date[0];
			$row['Month'] = $date[1];
			return array($row);
		  },
		  '{meta}' => function($metaData) {
			$metaData['columns']['Year'] = array(
			  'type' => 'number',
			);
			$metaData['columns']['Month'] = array(
			  'type' => 'number',
			);
			return $metaData;
		  }
		)))		

The following frontend view is now visible:

Now I would like to implement the following 3 steps where I need your support:

1) Mapping the "Month" digit values to the real months like 01=January, 02=February etc.

I've tried it with "ValueMap" code ater the Map above but no success!

    ->pipe(new ValueMap(array(
        "Month" =>array(
            "01"=>"January",
            "02"=>"February",
            "03"=>"March",
			"{meta}"=>array(
            "type"=>"string",
            )
        )
    )))

2) As our business is not seperated into the calender year but in fiscal year I would like to sort the Month colums beginning with April end continue by May, June, July, August, September, October, November, December, January, February and finishes with March like in the excel screenshot below:

3) Last point is: I would like to add a separate column for each year which "cumulates" only the VALUE from the Month before like:

Any hints for me to implement these 3 points?

Kind regards,

KoolReport commented on Feb 9, 2018

I am making a new process called AccumulativeColumn. I will send to you by email.

bysystem commented on Feb 9, 2018

Thx a lot! I'm really appreciated!

KoolReport commented on Feb 9, 2018

I've sent you the new process AccumulativeColumn to your email. Please save this process to koolreport/processes folder. To create a accumulative column from an existed column, you do:

->pipe(new AccumulativeColumn(array(
    "accumulativeAmount"=>"amount"
)))

After the process, you will have new column named accumulativeAmount generated from amount column.

Hope that helps.

Note: This process will be available in the next version of KoolReport. Those who need this process now, please email us, we will send you.

bysystem commented on Feb 13, 2018

Dear Karl,

thx a lot for your email!

  • I've saved the new process file to koolreport/process folder
  • I've added - use \koolreport\processes\AccumulativeColumn; - in my setup
  • Then I've piped the AccumulativeColumn as follows (see below the line before ->pipe($this->dataStore('ADR.FIRMENDETAIL'));):

    $this->src('adr')
    	->query("
    	SELECT Auftrg, NameWarenem, NameAuftr, StrasAuftr, PlzAuftr, OrtAuftr, Produktname, FakdatZBCP, Ag2, AG1Beschreibung, AG2Beschreibung, AuftrM, Wert
    	FROM Reporting.TOTAL_v
    		
    	WHERE			
    		$whereAuftrg
    			
    ")->params(array(
    	":Auftrg"=>$this->params["Auftrg"],
    ))		
    
    ->pipe(new Custom(function($data){
    	$data["Wert"] = trim(str_replace(",",".",$data["Wert"]));
    	return $data;
    }))
    
    ->pipe(new ColumnMeta(array(
          "Wert"=>array(
    		"align"=>"right",
              "type"=>"number",
              "prefix"=>"",
    		"suffix"=>"",
    		"decimals"=>2,
    		"thousandSeparator"=>".",
    		"decimalPoint"=>",",
          )
    )))	
    	
    ->pipe(new Map(array(
    	  '{value}' => function($row, $metaData) {
    		$date = explode('.', $row['FakdatZBCP']);
    		$row['Year'] = $date[0];
    		$row['Month'] = $date[1];
    		return array($row);
    	  },
    	  '{meta}' => function($metaData) {
    		$metaData['columns']['Year'] = array(
    		  'type' => 'number',
    		);
    		$metaData['columns']['Month'] = array(
    		  'type' => 'number',
    		);
    		return $metaData;
    	  }
    	)))		
    

    /*

      ->pipe(new ValueMap(array(
          "Month" =>array(
              01=>"Januar",
              "Februar"=>2,
              "03"=>"März",
    		"{meta}"=>array(
              "type"=>"string",
              )
          )
      )))		
    

    */

    ->saveTo($node);
    
    $node->pipe(new Pivot(array(
    	"dimensions" => array(
    		"column" => "Year",
    		"row" => "AG2Beschreibung"
    	),
    	"aggregates" => array(
    		"sum" => "Wert",
    	)
    )))
    ->pipe(new Sort(array(
          "Wert"=>"asc"
      )))	
      ->pipe($this->dataStore('ADR.PRODUKTGRUPPEN'));		
    	
    $node->pipe(new Pivot(array(
    		"dimensions"=>array(
    			"column" => "Year",
    			"row" => "Month",
    		),
    		"aggregates" => array(
    			"sum" => "AuftrM, Wert",            
    		)
    	)))	
    ->pipe(new AccumulativeColumn(array(
    	"accumulativeAmount"=>"Month"
    )))	
    ->pipe($this->dataStore('ADR.FIRMENDETAIL'));
    
  • My view.php for that part looks like:

    			if((isset($_POST["btnSubmit"])) AND ($this->dataStore("ADR.FIRMENDETAIL")->countData()>0))
    			{
    				PivotTable::create(array(
    				  "dataStore"=>$this->dataStore('ADR.FIRMENDETAIL'),								  
    				  "headerMap" => array(
    					"AuftrM - sum" => "∑ Menge",
    					"Wert - sum" => "∑ Umsatz pro Monat",
    				  ),
    					 
    				  "rowCollapseLevels" => array(0),
    				  "columnCollapseLevels" => array(2),
    					  
    				  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
    					 
    				));
    			}
    		?>   
    

But in my front end nothing changes! The result is still the same as the first screenshot above without any accumunulated column.

Any idea what could be the reason?

bysystem commented on Feb 13, 2018

Just found an error in my code above and corrected it to the following but now I get only zero values in the accumulatedColumn named "Kumulativ":

	->pipe(new AccumulativeColumn(array(
		"Kumulativ"=>"Wert"
	)))	
	->pipe($this->dataStore('ADR.FIRMENDETAIL'));

Further: i would like to remove the TOTAL columns (= "GESAMT") on the right side of the pivot table. Is that possible?

KoolReport commented on Feb 14, 2018

I misunderstood, you are using the data from Pivot so the data structure is quite different that could not apply the AccumulateColumn process. Please wait for us, we will find solution for it. Currently we are in the holiday for a week so we promise to find solution for you in next week.

bysystem commented on Feb 14, 2018

OK, understood! I'll wait for your response in next week.

Have a nice holiday!

bysystem commented on Feb 14, 2018

Just for your information:

3 of my 4 questions I could solve in the meanwhile:

1.) ValueMapping of the Months from 01 => "Januar", 02 =>"Februar" etc. 2.) Sorting of the Month values by fiscal year instead of calendar year:

    ->pipe(new ValueMap(array(
        "Month" =>array(
            "{func}"=>function($value){
				switch ($value) {		
					case $value==01 : return "Q4-01 Januar"; break;
					case $value==02 : return "Q4-02 Februar"; break;
					case $value==03 : return "Q4-03 März"; break;
					case $value==04 : return "Q1-04 April"; break;
					case $value==05 : return "Q1-05 Mai"; break;
					case $value==06 : return "Q1-06 Juni"; break;
					case $value==07 : return "Q2-07 Juli"; break;
					case $value==08 : return "Q2-08 August"; break;
					case $value==09 : return "Q2-09 September"; break;
					case $value==10 : return "Q3-10 Oktober"; break;
					case $value==11 : return "Q3-11 November"; break;
					case $value==12 : return "Q3-12 Dezember"; break;					
				}
				//return "$value";
            }			
        )
    )))

3.) Removing of the TOTAL column by adding "hideTotalColumn" => true in my view.

So only the point 4.) AccumulatedColumn in Pivot Table is open.

KoolReport commented on Feb 14, 2018

That's great that you solved other problems. Next week when David come back, I will discuss with him on solution for Accumulate for Pivot.

bysystem commented on Feb 22, 2018

Dear support team,

do you have any updates for me in the meanwhile?

Kind regards,

David Winterburn commented on Feb 22, 2018

Hi bysystem,

Per your request, we will add a new process to extract the pivot data to a standard tabular format. After that, you could use the AccumulatedColumn process to achieve your desired result. Please wait for the next release of the Pivot package.

Thanks,

bysystem commented on Feb 22, 2018

Welcome back David!

OK, thx a lot for your comment. I'll wait for the next pivot release.

David Winterburn commented on Feb 26, 2018

Hi bysystem,

We have released a new version of the Pivot package. Please download it and use the PivotExtract process like this:

->pipe(new Pivot(array(
      "dimensions"=>array(
        "column"=>"orderYear, orderMonth",
        "row"=>"customerName, productLine, productName"
      ),
      "aggregates"=>array(
        "sum"=>"dollar_sales",
        "count"=>"dollar_sales"
      )
    )))->saveTo($node2);

    $node2->pipe(new PivotExtract(array(
      "row" => array(
          "parent" => array(),
      ),
      "column" => array(
          "parent" => array(
          ),
      ),
      "measures"=>array(
          "dollar_sales - sum", 
      ),
    )))
    ->pipe(...);

    $node2->pipe(new PivotExtract(array(
        "row" => array(
            "parent" => array(
                "customerName" => "AV Stores, Co."
            ),
            "sort" => array(
                'dollar_sales - sum' => 'desc',
            ),
        ),
        "column" => array(
            "parent" => array(
                "orderYear" => "2004"
            ),
            "sort" => array(
                'orderMonth' => function($a, $b) {
                    return (int)$a < (int)$b;
                },
            ),
        ),
        "measures"=>array(
            "dollar_sales - sum", 
            "dollar_sales - count", 
        ),
      )))
      ->pipe(...);

You could check an example at this link: https://www.koolreport.com/examples/reports/pivot/pivot_extract/index.php

After the PivotExtract process you could apply the AccumulativeColumn to achive the table you want. Please try this and let us know the result. Thanks!

mohamadreza commented on Aug 12, 2018

Hi I need to add the cumulative column to my table too .... may I get the extension ? (plus I have to set an initial value for a column - so it shouldn't start from zero necessarily)

David Winterburn commented on Aug 13, 2018

Hi,

If you have the same problem as bysystem, please download the latest version of the Pivot package and apply the PivotExtract process from there. Thanks!

Rafa commented on May 4, 2019

Hi, can I use this with pivot table?

David Winterburn commented on May 7, 2019

Hi Rafa,

Yes, you can use this with PivotTable.

David Winterburn commented on May 7, 2019

Hi Rafa,

Did you send us an email to support@koolphp.net for a development version of the Pivot package? If you did, we've just sent the package link to you but there seems to be a problem with email delivery to your email address from our email.

Rafa commented on May 9, 2019

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

Pivot