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,

Thx a lot! I'm really appreciated!

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?

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?

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

Have a nice holiday!

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.

Dear support team,

do you have any updates for me in the meanwhile?

Kind regards,

Welcome back David!

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

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)

Hi, can I use this with pivot table?

Thanks!