Dear support team,

let's assume I have a database table (see left table on the screenshot) with:

• a column name "date", unfortunately not a real date format, only a string "year.month"
• column name "qty" and "value"

I would like to use CUBE package to render the out as the right table on the screenshot.

I tried it wit the following setup:

	$node->pipe(new Cube(array(
		"row" => "date",
		"sum" => "value",
	)))		
	->pipe($this->dataStore('Reporting.FIRMENDETAIL2'));

2 problems in my output:

1. The date column contains ALL the years and month in ONE column. So I could not split them like in my screenshot abov (right table).
2. As it was not possible to add an additional column in the "sum" property ("qty") I get only the summary of "value" column

Do you have any hint for me how to do that?

Kind regards,

Dear support team,

your feedback is highly appreciated, thx a lot! Do you have any hints for me in the meanwhile?

Kind regards,

Dear Dave,

something seems to be wrong with my code (I guess syntax!?), becaue I get a blank page! Could you please review where is my mistake?

//firmendetail.php
<?php

require_once "../koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\Group;
use \koolreport\processes\Sort;
use \koolreport\processes\Limit;
use \koolreport\processes\Map;
use \koolreport\cleandata\FillNull;
use \koolreport\processes\Custom;
use \koolreport\cube\processes\Cube;

class firmendetail extends koolreport\KoolReport
{
	use \koolreport\clients\FontAwesome;
	use \koolreport\clients\Bootstrap;
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;
	//use \koolreport\inputs\GETBinding;
	use \koolreport\export\Exportable;

    function defaultParamValues()
    {
        return array(
            "Auftrg" => array("YBC"),
	    "NameAuftr" => array(),
        );
    }
	
    function bindParamsToInputs()
    {
        return array(
	    "Auftrg",
	    "NameAuftr",
        );
    }
	
    function settings()
    {
        return array(
	    "dataSources"=>array(
	    "adr"=>array(
		'connectionString' => 'sqlsrv:Server=tcp:1.123.45.67;Database=ABC_ADR',
			'username' => 'sa',
			'password' => '******',
			), 
		)
        );
    }

    function setup()
    {	
		// KUNDENNUMMER: MultiSelect-Optionswerte aus Reporting.TOTAL_v holen
		$this->src('adr')
        ->query("
			SELECT DISTINCT Auftrg
			FROM Reporting.TOTAL_v 
			ORDER BY Auftrg ASC
        ")
        ->pipe($this->dataStore("ADR.Auftrg"));
			
		// FIRMENDETAIL-PIVOT: Umsatz/Menge aggregieren
		if(count($this->params["Auftrg"])>0){$whereAuftrg="Auftrg IN (:Auftrg) ";}else{$whereAuftrg="Auftrg IS NOT NULL ";}	

		$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"=>",",
            )
		)))	

		$node = $this->src('adr')
		->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;
			  }
			)))		
		
		$node->pipe(new Pivot(array(
				"dimensions"=>array(
					"column" => "Year",
					"row" => "Month",
				),
				"aggregates" => array(
					"sum" => "AuftrM, Wert",            
				)
			)))	
		->pipe($this->dataStore('datastore1'));	
		
	} 
}

//firmendetail.view.php

<?php

	use \koolreport\pivot\widgets\PivotTable;
	use \koolreport\widgets\koolphp\Table;
	use \koolreport\inputs\MultiSelect;
	use \koolreport\inputs\Select2;
	use \koolreport\inputs\CheckBoxList;
	use \koolreport\inputs\RadioList;
	use \koolreport\widgets\google\BarChart;
	use \koolreport\widgets\google\PieChart;
	use \koolreport\widgets\google;
	
?>

<script type="text/javascript">
function doExport()
{
	//alert(1);
    var _form = document.getElementById("parameterForm");
    _form.action = "export.php";
    _form.submit();
	_form.action = "index.php";
}
</script>

<script type="text/javascript">
    $(document).ready(function(){
		$('[data-toggle="tooltip"]').tooltip();   
    });
</script>
		
	<div id="col2">	
		<div id="col2_content" class=""clearfix>
			<form id="parameterForm" method="post">
				<div class="form-group" style="width:250px;margin-right:10px;">
				<h5>Kundennummer</h5>
					<?php
					
					Select2::create(array(
						"name"=>"Auftrg",
						"dataStore"=>$this->dataStore("ADR.Auftrg"),
						"dataBind"=>"Auftrg",
						"multiple"=>true,
						"attributes"=>array(
							"class"=>"form-control",
						)
					));
					
					?>
				</div>		
		
				<div class="form-group text-center">
					<button class="btn btn-primary" name="btnSubmit"><i class="glyphicon glyphicon-refresh"></i> Finden</button>
				</div>	

			</form>	
		</div>
	</div>
	
	<div id="col1">
			<div id="adr1" class="tab-pane fade in active">
				<div id="col1_content" class="clearfix">			
					<div style="float:left"><h3> <img src="icon_grid.png"/>   ADR: FIRMENDETAIL</h3></div>
					<div style="float:right"><button style="min-width:200px;" onclick="doExport()" class="btn btn-primary">PDF-Export<br />FIRMENDETAIL</button></div>
						<?php
							if((isset($_POST["btnSubmit"])) AND ($this->dataStore("datastore1")->countData()>0))
							{
								PivotTable::create(array(
								  "dataStore"=>$this->dataStore('datastore1'),								  
								  "headerMap" => array(
									"AuftrM - sum" => "∑ Menge",
									"Wert - sum" => "∑ Umsatz pro Monat",
								  ),
								 
								  "rowCollapseLevels" => array(0),
								  "columnCollapseLevels" => array(2),
								  
								  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
								 
								));
							}
						?>     
				</div>		
			</div>				
	</div>	
  </body>
</html>

Dear Dave,

found the reason >> Syntax error: Missing comma and semicolon at the end of the snippet::

	->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;
		  }, //missing comma
		))); // missing semicolon

It works now!

Thx a lot for your hint!