Echoing dedicated database fields

bysystem commented on Jan 11, 2018

Dear support team,

In my report the user has the possibility to select a customernumber OR a customer name (in a SINGLE SELECT option field) and submit the form.

As a result the quatities and the values from only this customer in each date are beeing shown. This works fine.

I would like to output above of the table list:

The customer name (field in the database = "NameAuftr")
The customer number (field in the database = "Auftrg")
The customer city (field in the database = "OrtWrnem")

Number 1 or 2 is no problem if the user has choosen in the form, for example customer number, then I do this with:

$customerNumber = $this->params["Auftrg"]; echo " Customer number: ".$customerNumber." ";

or if the user has choosen in the form, for example customer name, then I do this with:

$customerName = $this->params["NameAuftr"]; echo " Customer name: ".$customerName." ";

But how to output the additional information like city etc...

Her is my select statement with the select option parameters in my setup:

	$this->src('adr')
		->query("
		SELECT Auftrg, NameWarenem, NameAuftr, OrtWrnem, Produktname, FakdatZBCP, Ag2, AG1Beschreibung, AG2Beschreibung, AuftrM, Wert
		FROM Reporting.TOTAL_v
		
		WHERE			
			$whereAuftrg AND
			$whereNameAuftr	
			
	")->params(array(
		":Auftrg"=>$this->params["Auftrg"],
		":NameAuftr"=>$this->params["NameAuftr"],
	))

Kind regards,

KoolReport commented on Jan 11, 2018

That's easy!

In the view, you have all the OrtWrnem available in dataStore already. So you can do:

$city = $this->dataStore("datastore_name")->get(0,"OrtWrnem");

The 0 is the row number and OrtWrnem is the column name.

bysystem commented on Jan 12, 2018

I've followed your hint and done:

My setup

	$this->src('adr')
		->query("
		SELECT Auftrg, NameWarenem, NameAuftr, OrtWrnem, Produktname, FakdatZBCP, Ag2, AG1Beschreibung, AG2Beschreibung, AuftrM, Wert
		FROM Reporting.TOTAL_v
		
		WHERE			
			$whereAuftrg AND
			$whereNameAuftr	
			
	")->params(array(
		":Auftrg"=>$this->params["Auftrg"],
		":NameAuftr"=>$this->params["NameAuftr"],
	))		

	->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"=>",",
        )
	)))	
	->saveTo($node);
	
	$node->pipe(new Pivot(array(
		"dimensions" => array(
			//"column" => "",
			"row" => "FakdatZBCP, Auftrg, NameWarenem, Produktname"
		),
		"aggregates" => array(
			"sum" => "AuftrM, Wert",
		)
	)))		
	->pipe($this->dataStore('Reporting.FIRMENDETAIL1'));

My view:

$name = $this->dataStore("Reporting.FIRMENDETAIL1")->get(0,"NameAuftr");

$number = $this->dataStore("Reporting.FIRMENDETAIL1")->get(0,"Auftrg");

$city = $this->dataStore("Reporting.FIRMENDETAIL1")->get(0,"OrtWrnem");

echo "Name :".$name." ";

echo "Number :".$number." ";

echo "City :".$city." ";

But the outputs for "Name", ""Number" and "City" are still empty!

KoolReport commented on Jan 12, 2018

If your data has gone through Pivot process then you can not get the city like my code, Pivot will summarize all data. Better you do another query to take out the city, may be use limit in sql statement to limit rows returned.

bysystem commented on Jan 15, 2018

Thx a lot!

Creating a new query and limitting the output of the SQL statement did exact what I need!

Kind regards,

KoolReport commented on Jan 15, 2018

If two query are the same then you can do this to save processing time:

$this->src('adr')->>query("your query")
->saveTo($branch)
->pipe(new Custom(..))
...
->pipe($this->dataStore("Reporting.FIRMENDETAIL1"));

$branch->pipe(new Limit(array(1)))
->save($this->dataStore("mydata"));

Now you have the city and name and id in the dataStore called mydata which you can use get() to get data.

bysystem commented on Jan 16, 2018

Thx for your hint!

That was exact the same way (with ->saveTo($node)...) how I did it already ;-)

KoolReport's Forum

Echoing dedicated database fields #190

Build Your Excellent Data Report