KoolReport's Forum

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

Cube: grouping #189

Open bysystem opened this topic on on Jan 11, 2018 - 5 comments

bysystem commented on Jan 11, 2018

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,

KoolReport commented on Jan 12, 2018

Hold on, we will find solution for you.

bysystem commented on Jan 17, 2018

Dear support team,

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

Kind regards,

David Winterburn commented on Jan 18, 2018

Hi bysystem,

For solving your problem I suggest 2 solutions. First one is that you should split the Date column into Year and Month columns. If you could not do this from your data source, you could use the following process:

//myReport.php

function setup {
//...
$node = $this->src('datasource1')
->pipe(new \koolreport\processes\Map(array(
      '{value}' => function($row, $metaData) {
	$date = exlode('.', $row['Date']);
	$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;
      },
    )));
//...
}

For your second problem, the Cube package could not display more than 2 aggregated/summed data fields. In that case I suggest using the Pivot package with the pivot table widget. Example code looks like this:

//myReport.php
<?php

function setup {

$node = $this->src('datasource1')
->pipe(new \koolreport\processes\Map(array(
      '{value}' => function($row, $metaData) {
	$date = exlode('.', $row['Date']);
	$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 \koolreport\pivot\Pivot(array(
        "dimensions"=>array(
            "column" => "Year",
            "row" => "Month",
        ),
        "aggregates" => array(
            "sum" => "Qty, Value",            
        )
    )))
->pipe($this->dataStore('datastore1'));

}
?>

//myReport.tpl.php

<?php
\koolreport\pivot\PivotTable::create(array(
            "dataStore"=>$this->dataStore('datastore1'),            
          ));
?>

The final table's format might look a bit different from your image but it will be clear enough. Please let us know if these approaches meet your need. Thanks!

bysystem commented on Jan 19, 2018

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>
bysystem commented on Jan 24, 2018

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!

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
solved

Cube