Dear David,

I've tried to bind (first of all only 1) input element (multiselect) into my PivotTable report so that the user can select the needed companies and load the corresponding report. If this works fine, in the next step I would like to bind much more elements for having a big "search field" with several input elements for easy retrieving of selected data.

For this pupose I tried the examples on github but at this moment I am a little bit lost and need your help.

Here is my pivot 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;

class autoreppivot extends koolreport\KoolReport
{
	use \koolreport\clients\FontAwesome; //für die Collapse + icons
	use \koolreport\clients\Bootstrap;
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "werbegruppen" => array(
                "Einzelhaus"
            ),
        );
    }
	
    protected function bindParamsToInputs()
    {
        return array(
            "werbegruppen"=>"werbegruppen",
        );
    }
	
    function settings()
    {
        return array(
			"dataSources"=>array(
				"sales"=>array(
				'connectionString' => 'sqlsrv:Server=tcp:12.34.56.789;Database=test',
				'username' => 'sa',
				'password' => '****',
				), 
			)
        );
    }

   function setup()
  {
    $node = $this->src('sales')
	->query("SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz FROM Reporting.autorep_v_noNulls")
    ->pipe(new Pivot(array(
      "dimensions" => array(
        "column" => "Monat",
        "row" => "WerbegruppeVerbund, NikonKdName, NikonProdGruppe"
      ),
      "aggregates" => array(
	    "sum" => "Umsatz",
      )
    )))
    ->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  
  } 
}

Here is the view php:

<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\inputs\MultiSelect;
?>
            <form method="post">
                <div class="row">
                    <div class="col-md-8 col-md-offset-2">

                        <div class="form-group">
                        <?php
                        MultiSelect::create(array(
                            "name"=>"werbegruppen",
                            "dataStore"=>$this->dataStore("Reporting.autorep_v_noNulls"),
                            "dataBind"=>array(
                                "text"=>"WerbegruppeVerbund",
                                "value"=>"WerbegruppeVerbund",
                            ),
                            "attributes"=>array(
                                "class"=>"form-control",
                                "size"=>10,
                            )
                        ));
                        ?>
                        </div>
                        <div class="form-group text-center">
                            <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Submit</button>
                        </div>
                    </div>
                </div>
</form>

<hr/>

<?php

PivotTable::create(array(
  "dataStore"=>$this->dataStore('Reporting.autorep_v_noNulls'),
		
  "measures" => array(
    "Umsatz - sum",
  ),
	  
  "headerMap" => array(
    "Umsatz - sum" => "&sum; Umsatz",
  ),
 
  "rowCollapseLevels" => array(1), //0 = zu, 1 = auf
  "columnCollapseLevels" => array(0),
  
  "totalName" => 'GESAMT'
 
));
?>

      </div>
      
    </div>
  </body>
</html>

The result is now an empty multiselect field (here the field "WervegruppeVerbund" should be retrieved. And of course no working "Submit" button. The result should be a pivot table containing only from the WerbegruppeVerbund companies which are selected by the user:

Any hints for me?

Kind regards

Thx for quick response.

I have just followed you advice and tried to implement / adjust the example "Order details of customers " with only a SELECT option for the databse column "WerbegruppeVerbund" instead of MULTISELECT to have the situation more easier. Because I have only 1 table and don't need to JOIN the foreign keys etc. But unfortunately I got as a result a completely empty blank site! Probably there are a lot of sytax errors and non sense variable assignments below in my code ???

Here ist my pivot php code: ` <?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;

class autoreppivot extends koolreport\KoolReport {

use \koolreport\clients\FontAwesome; //für die Collapse + icons
use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;

protected function defaultParamValues()
{
    return array(
        "WerbegruppeVerbund" => null,
    );
}

protected function bindParamsToInputs()
{
    return array(
        "WerbegruppeVerbund",
    );
}

function settings()
{
    return array(
		"dataSources"=>array(
			"sales"=>array(
			'connectionString' => 'sqlsrv:Server=tcp:12.345.67.89;Database=test',
			'username' => 'sa',
			'password' => '******',
			), 
		)
    );
}

function setup() {

$this->src('sales')
->query("
	SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
    FROM Reporting.autorep_v_noNulls
	WHERE WerbegruppeVerbund = :WerbegruppeVerbund
")->params(array(
	":WerbegruppeVerbund"=>$this->params["WerbegruppeVerbund"]
))
->pipe(new Pivot(array(
  "dimensions" => array(
    "column" => "Monat",
    "row" => "WerbegruppeVerbund, NikonKdName, NikonProdGruppe"
  ),
  "aggregates" => array(
    "sum" => "Umsatz",
    //"sum" => "Umsatz, Verkaufsmenge",
  )
)))
->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  

} }

Here ist my view php:

<?php

use \koolreport\pivot\widgets\PivotTable;
use \koolreport\inputs\MultiSelect;
use \koolreport\inputs\Select;

$WerbegruppeVerbund = "";
$this->dataStore("Reporting.autorep_v_noNulls")->popStart();
while($row = $this->dataStore("Reporting.autorep_v_noNulls")->pop())
{
    if($row["WerbegruppeVerbund"]==$this->params["WerbegruppeVerbund"])
    {
        $WerbegruppeVerbund =$row["WerbegruppeVerbund"];
    }
}

?>

        <form method="post">
            <div class="row">
                <div class="col-md-8 col-md-offset-2">

                    <div class="form-group">
                    <?php
                        Select::create(array(
                            "name"=>"WerbegruppeVerbund",
                            "dataStore"=>$this->dataStore("Reporting.autorep_v_noNulls"),
							"defaultOption"=>(""=>"--"),
                            "dataBind"=>array(
								"text"=>"WerbegruppeVerbund",
								"value"=>"WerbegruppeVerbund",
							),
                            "attributes"=>array(
                                "class"=>"form-control",
                            )
                        ));
                    ?>
                    </div>
                    <div class="form-group text-center">
                        <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Submit</button>
                    </div>
                </div>
            </div>

</form>

<?php

PivotTable::create(array( "dataStore"=>$this->dataStore('Reporting.autorep_v_noNulls'),

"measures" => array(

"Umsatz - sum",
//"Verkaufsmenge - sum",

),

"headerMap" => array(

"Umsatz - sum" => "∑ Umsatz",
//"Verkaufsmenge - sum" => "∑ Verkaufsmenge"

),

"rowCollapseLevels" => array(1), //0 = zu, 1 = auf "columnCollapseLevels" => array(0),

"totalName" => 'GESAMT'

)); ?>

  </div>
  
</div>

</body> </html> ` Any idea how to fix this?

Kind regards, Yilmaz

OK I could fix the problem related to the "empty blank white site". The reason was the syntax error "defaultOption"=>(""=>"--"), in the example for Select option. I removed it.

Now the site with the select field is apperaring but still no options inside and the pivot table is empty because no data was retrieved!

In the meanwhile I've understood the way to create 2 different src() and did it in my code. But however I cannot retrieve neither the select option list for WerbegrupVerbund nor the correct data from the selection of user.

Here is now my adjust code for setup (view php is the same as above in my previous post): (I changed the name of the input select field to "werbegruppe")

    function setup()
    {
		// 1. src () for retrieving all select options for WerbegruppeVerbund
		$this->src('sales')
        ->query("
            SELECT WerbegruppeVerbund
			FROM Reporting.autorep_v_noNulls
            ORDER BY WerbegruppeVerbund
        ")
        ->pipe($this->dataStore("Reporting.autorep_v_noNulls"));

		// 2. src () base on the selection of user to show correct data	
		$this->src('sales')
		->query("
			SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
			FROM Reporting.autorep_v_noNulls
			WHERE WerbegruppeVerbund = :werbegruppe
		")->params(array(
			":werbegruppe"=>$this->params["werbegruppe"]
		))
		->pipe(new Pivot(array(
			"dimensions" => array(
			"column" => "Monat",
			"row" => "WerbegruppeVerbund, NikonKdName, NikonProdGruppe"
			),
			"aggregates" => array(
			"sum" => "Umsatz",
			//"sum" => "Umsatz, Verkaufsmenge",
		)
		)))
		->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  
	} 
}

My view php:

                        <?php
                        Select::create(array(
                            "name"=>"werbegruppe",
                            "dataStore"=>$this->dataStore("Reporting.autorep_v_noNulls"),
                            "dataBind"=>array(
                                "text"=>"werbegruppe",
                                "value"=>"WerbegruppeVerbund",
                            ),
                            "attributes"=>array(
                                "class"=>"form-control"
                            )
                        ));
                        ?>

Here is the result (now the selection option box is empty but it has more lines!

Need definitely your help at this point!

Kind regards

I can now retrieve the select option list contents from the database.

Now I'm missing "only" showing the correct data base on the selection of user!

Here is my current setup():

function setup()
{
	// 1. src () for retrieving all select options for WerbegruppeVerbund
	$this->src('sales')
    ->query("
        SELECT WerbegruppeVerbund
		FROM Reporting.autorep_v_noNulls
        ORDER BY WerbegruppeVerbund
    ")
    ->pipe($this->dataStore("Reporting.autorep_v_noNulls"));

	// 2. src () base on the selection of user to show correct data	
	$this->src('sales')
	->query("
		SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
		FROM Reporting.autorep_v_noNulls
		WHERE WerbegruppeVerbund = :werbegruppe
	")->params(array(
		":werbegruppe"=>$this->params["werbegruppe"]
	))
	->pipe(new Pivot(array(
		"dimensions" => array(
		"column" => "Monat",
		"row" => "WerbegruppeVerbund, NikonKdName, NikonProdGruppe"
		),
		"aggregates" => array(
		"sum" => "Umsatz",
		//"sum" => "Umsatz, Verkaufsmenge",
	)
	)))
	->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  
} 

And my view php:

                        <?php
                        Select::create(array(
                            "name"=>"werbegruppe",
                            "dataStore"=>$this->dataStore("Reporting.autorep_v_noNulls"),
                            "dataBind"=>array(
                                "text"=>"WerbegruppeVerbund",
                                "value"=>"WerbegruppeVerbund",
                            ),
                            "attributes"=>array(
                                "class"=>"form-control"
                            )
                        ));
                        ?>

Any hints here at this point?

Do you mean different names for the dataStrore for src() 1 and src() 2? Do you have an example for me?

Hmmm... Strange. I just changed one of the src() functions to src(autorep2) but I get an empty site!

function setup()
{
	// 1. src () for retrieving all select options for WerbegruppeVerbund
	$this->src('autorep')
    ->query("
		SELECT Werbegruppe
		FROM ETL.Werbegruppen 
		WHERE Werbegruppe = 'Einzelhaus' OR Werbegruppe LIKE 'Expert%'
		ORDER BY Werbegruppe ASC
    ")
    ->pipe($this->dataStore("ETL.Werbegruppen"));

	// 2. src () base on the selection of user to show correct data	
	$this->src('autorep2')
	->query("
		SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
		FROM Reporting.autorep_v_noNulls
		WHERE WerbegruppeVerbund = :Werbegruppe
	")->params(array(
		":Werbegruppe"=>$this->params["Werbegruppe"]
	))

Just double checked in the example "Order List" with database automaker they are using the same name of the src(automaker):

function settings()
{
    $config = include "../../../config.php";
    return array(
        "dataSources"=>array(
            "automaker"=>$config["automaker"]
        )
    );
}
function setup()
{
    $this->src('automaker')
    ->query("
        SELECT *
        FROM customers
        ORDER BY customerName
    ")
    ->pipe($this->dataStore("customers"));
    $this->src('automaker')
    ->query("
        SELECT products.productName,orderdetails.priceEach,orderdetails.quantityOrdered
        FROM orders

Or do you mean something others?

But in my setup this IS different:

1. src() for retrieving the select option list ->pipe($this->dataStore("ETL.Werbegruppen"));

   $this->src('autorep')
    ->query("
   	SELECT Werbegruppe
   	FROM ETL.Werbegruppen 
   	WHERE Werbegruppe = 'Einzelhaus' OR Werbegruppe LIKE 'Expert%'
   	ORDER BY Werbegruppe ASC
    ")
   

   ->pipe($this->dataStore("ETL.Werbegruppen"));

2. src() for getting the right data for user is ->pipe($this->dataStore('Reporting.autorep_v_noNulls'));

OK, here the php code where my setup is:

<?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;

class autoreppivot extends koolreport\KoolReport {

use \koolreport\clients\FontAwesome; //für die Collapse + icons
use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;

function defaultParamValues()
{
    return array(
        "Werbegruppe" => "Einzelhaus",
    );
}

function bindParamsToInputs()
{
    return array(
		"Werbegruppe",
    );
}

function settings()
{
    return array(
		"dataSources"=>array(
			"autorep"=>array(
			'connectionString' => 'sqlsrv:Server=tcp:12.34.56.789;Database=test',
			'username' => 'sa',
			'password' => '******',
			), 
		)
    );
}

function setup()
{
	// 1. src () for retrieving all select options for WerbegruppeVerbund
	$this->src('autorep')
    ->query("
		SELECT Werbegruppe
		FROM ETL.Werbegruppen 
		WHERE Werbegruppe = 'Einzelhaus' OR Werbegruppe LIKE 'Expert%'
		ORDER BY Werbegruppe ASC
    ")
    ->pipe($this->dataStore("ETL.Werbegruppen"));

	// 2. src () base on the selection of user to show correct data	
	$this->src('autorep')
	->query("
		SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
		FROM Reporting.autorep_v_noNulls
		WHERE WerbegruppeVerbund = :Werbegruppe
	")->params(array(
		":Werbegruppe"=>$this->params["Werbegruppe"]
	))
	->pipe(new Pivot(array(
		"dimensions" => array(
		"column" => "Monat",
		"row" => "WerbegruppeVerbund, NikonKdName, NikonProdGruppe"
		),
		"aggregates" => array(
		"sum" => "Umsatz",
		//"sum" => "Umsatz, Verkaufsmenge",
	)
	)))
	->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  
} 

}

Here is my view code:

<?php

use \koolreport\pivot\widgets\PivotTable;
//use \koolreport\inputs\MultiSelect;
use \koolreport\inputs\Select;

/*

$haendler = "";
$this->dataStore("zwei")->popStart();
while($row = $this->dataStore("zwei")->pop())
{
    if($row["WerbegruppeVerbund"]==$this->params["Werbegruppe"])
    {
        $haendler =$row["WerbegruppeVerbund"];
    }
}

*/ ?>

        <form method="post">
            <div class="row">
                <div class="col-md-8 col-md-offset-2">

                    <div class="form-group">
                        <?php
                        Select::create(array(
                            "name"=>"Werbegruppe",
                            "dataStore"=>$this->dataStore("ETL.Werbegruppen"),
                            "dataBind"=>array(
                                "text"=>"Werbegruppe",
                                "value"=>"Werbegruppe",
                            ),
                            "attributes"=>array(
                                "class"=>"form-control"
                            )
                        ));
                        ?>
                    </div>
                    <div class="form-group text-center">
                        <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Submit</button>
                    </div>
                </div>
            </div>
		</form>

<?php

if($this->dataStore("Reporting.autorep_v_noNulls")->countData()>0)
{
?>
	<?php

	PivotTable::create(array(
	  "dataStore"=>$this->dataStore('Reporting.autorep_v_noNulls'),

	  "measures" => array(
		"Umsatz - sum",
		//"Verkaufsmenge - sum",
	  ),
		  
	  "headerMap" => array(
		"Umsatz - sum" => "&sum; Umsatz",
		//"Verkaufsmenge - sum" => "&sum; Verkaufsmenge"
	  ),
	 
	  "rowCollapseLevels" => array(1), //0 = zu, 1 = auf
	  "columnCollapseLevels" => array(0),
	  
	  "totalName" => 'GESAMT'
	 
	));
	?>
  </div>
  <?php
  }
  ?>     
</div>

</body> </html>

Unfortunately there are no data shown when I click on Submit (empty site under the input form and submit button)

But it works if I change the passing parameter :Werbegruppe in the WHERE condition (see below). That means there must be an error during the passing of the input parameter to the SQL statement!

	$this->src('autorep')
	->query("
		SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
		FROM Reporting.autorep_v_noNulls
				$this->src('autorep')
	->query("
		SELECT Geschaeftsjahr, Monat, WerbegruppeVerbund, NikonKdName, NikonProdGruppe, Verkaufsmenge, Umsatz
		FROM Reporting.autorep_v_noNulls
                    -- WHERE WerbegruppeVerbund = :Werbegruppe
		__WHERE WerbegruppeVerbund = 'Einzelhaus'__

Passing params seems to be OK. The choosen select option is shown on top left by echo when I click on Submit:

Thx a lot for your support. I just sent you an email.

Wooow :-)

Just received the new updated file and replaced it. And it works like a charme ;-)

What was wrong?

Thx a lot again!

Great job! Just put 5 credits from me into your tips box :-)

Thx a lot to all the support team!