Dear support team,

currently I'm using the vers. 2.31.8 (XAMPP, MS SQL Server connection via PdoDataSource.php) without any problems. Now I wanted to upgrade to the latest vers. 2.78.0 on the same way as usual which was always successfully (till yet)!

• copying the existing packages to the new folder /koolreport/packages
• copying the existing customized css files to the new folder koolreport/clients/bootstrap/css
• replacing the old koolreport folder by the new koolreport folder
• That's it!

I assume there are essential changes related to the PdoDataSource.php file so I get the following error message with a blank web site!

Fatal error: Uncaught Exception: Query Error >> [[Microsoft][ODBC Driver 13 for SQL Server]Das COUNT-Feld ist nicht korrekt, oder es besteht ein Syntaxfehler] >> SELECT ... C:\xampp\htdocs\byTool\koolreport\datasources\PdoDataSource.php:204 Stack trace: #0 C:\xampp\htdocs\byTool\koolreport\KoolReport.php(255): koolreport\datasources\PdoDataSource->start() #1 C:\xampp\htdocs\byTool\pivot\index.php(8 in C:\xampp\htdocs\byTool\koolreport\datasources\PdoDataSource.php on line 204

Any idea how to fix this?

Kind regards, bysystem

Hello David,

here is the way how I connect to my MS SQL server:

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

And here is the way how I generate the output:

						<?php
							if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.autorep_v_noNulls")->countData()>0))
							{
								PivotTable::create(array(
								  "dataStore"=>$this->dataStore('Reporting.autorep_v_noNulls'),

								  "measures" => array(
									"Verkaufsmenge - sum",
									"Umsatz - sum",
								  ),
									  
								  "headerMap" => array(
									"Verkaufsmenge - sum" => "∑ Menge",
									"Umsatz - sum" => "∑ Umsatz pro Quartal und Monat",
								  ),
								 
								  "rowCollapseLevels" => array(1,2), //0 = zu, 1 = auf
								  "columnCollapseLevels" => array(1,2),
								  
								  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
								 
								));
							}
						?>  

If you need some other information please let me know.

Kind regards,

bysystem

Hello David,

here are the binding parameters:

    function bindParamsToInputs()
    {
        return array(
			"Sell",
			"KdName",
			"Werbegruppe",
			"NKdName",
			"NOrt",
			"ADBezirk",
			"NProdGruppe",
			"NArtBez",
			"Geschaeftsjahr",
			"Quartal",
			"Monat",
        );
    }

Here is the SQL statement incl. params binding:

		// PIVOTTABLE: Umsatz aggregieren	
		if(count($this->params["Werbegruppe"])>0){$whereWerbegruppe="WerbegruppeVerbund IN (:Werbegruppe) ";}else{$whereWerbegruppe="WerbegruppeVerbund IS NOT NULL ";}			
		if(count($this->params["NOrt"])>0){$whereNOrt="NOrt IN (:NOrt) ";}else{$whereNOrt="NOrt IS NOT NULL ";}		
		if(count($this->params["NKdName"])>0){$whereNKdName="NKdName IN (:NKdName) ";}else{$whereNKdName="NKdName IS NOT NULL ";}	
		if(count($this->params["ADBezirk"])>0){$whereADBezirk="ADBezirk IN (:ADBezirk) ";}else{$whereADBezirk="ADBezirk IS NOT NULL ";}	
		if(count($this->params["NProdGruppe"])>0){$whereNProdGruppe="NProdGruppe IN (:NProdGruppe) ";}else{$whereNProdGruppe="NProdGruppe IS NOT NULL ";}		
		if(count($this->params["Geschaeftsjahr"])>0){$whereGeschaeftsjahr="Geschaeftsjahr IN (:Geschaeftsjahr) ";}else{$whereGeschaeftsjahr="Geschaeftsjahr IS NOT NULL ";}		
		if(count($this->params["NArtBez"])>0){$whereNArtBez="NArtBez IN (:NArtBez) ";}else{$whereNArtBez="NArtBez IS NOT NULL ";}		
		if(count($this->params["Quartal"])>0){$whereQuartal="Quartal IN (:Quartal) ";}else{$whereQuartal="Quartal IS NOT NULL ";}		
		if(count($this->params["Monat"])>0){$whereMonat="Monat IN (:Monat) ";}else{$whereMonat="Monat IS NOT NULL ";}		

		$this->src('autorep')
			->query("
			SELECT Sell, Geschaeftsjahr, Monat, Quartal, WerbegruppeVerbund, NKdName, NKdNr, NOrt, NProdGruppe, NArtBez, BestandHaendler_aktuell, BestandZentrale_aktuell, ADBezirk, Verkaufsmenge, Umsatz
			FROM Reporting.autorep_v_noNulls
			WHERE			
				Sell = (:Sell) AND
				KdName IN (:KdName) AND
				$whereWerbegruppe AND
				$whereNKdName AND
				$whereNOrt AND
				$whereADBezirk AND
				$whereNProdGruppe AND
				$whereNArtBez AND
				$whereGeschaeftsjahr AND				
				$whereQuartal AND
				$whereMonat
				
		")->params(array(
			":Sell"=>$this->params["Sell"],
			":KdName"=>$this->params["KdName"],
			":Werbegruppe"=>$this->params["Werbegruppe"],
			":NKdName"=>$this->params["NKdName"],
			":NOrt"=>$this->params["NOrt"],
			":ADBezirk"=>$this->params["ADBezirk"],
			":NProdGruppe"=>$this->params["NProdGruppe"],
			":NArtBez"=>$this->params["NArtBez"],
			":Geschaeftsjahr"=>$this->params["Geschaeftsjahr"],
			":Quartal"=>$this->params["Quartal"],
			":Monat"=>$this->params["Monat"]
		))
		->pipe(new ColumnMeta(array(
            "Umsatz"=>array(
				"align"=>"right",
                "type"=>"number",
                "prefix"=>"",
				"suffix"=>"",
				"decimals"=>2,
				"thousandSeparator"=>".",
				"decimalPoint"=>",",
            ),
			"Verkaufsmenge" => array(
				"align"=>"right",			
			)
		)))		
		->saveTo($node);
		
		$node->pipe(new Pivot(array(
			"dimensions" => array(
				"column" => "Geschaeftsjahr, Quartal, Monat",
				"row" => "WerbegruppeVerbund, NKdName, NOrt, NKdNr, NProdGruppe"
			),
			"aggregates" => array(
				"sum" => "Verkaufsmenge, Umsatz",
			)
		)))
		->pipe($this->dataStore('Reporting.autorep_v_noNulls'));  

Thx a lot and kind regards, bysystem

Hello David,

sorry for answering so late...

I just have removed the round brackets from the "if count(...)..:" part and kept them for the array parameters in SQL WHERE part:

		if(count($this->params["Werbegruppe"])>0){$whereWerbegruppe="WerbegruppeVerbund IN :Werbegruppe ";}else{$whereWerbegruppe="WerbegruppeVerbund IS NOT NULL ";}			
		if(count($this->params["Ort"])>0){$whereOrt="Ort IN :Ort ";}else{$whereOrt="Ort IS NOT NULL ";}		
		if(count($this->params["KdName"])>0){$whereKdName="KdName IN :KdName ";}else{$whereKdName="KdName IS NOT NULL ";}	
		if(count($this->params["ADBezirk"])>0){$whereADBezirk="ADBezirk IN :ADBezirk ";}else{$whereADBezirk="ADBezirk IS NOT NULL ";}	
		if(count($this->params["ProdGruppe"])>0){$whereProdGruppe="ProdGruppe IN :ProdGruppe ";}else{$whereProdGruppe="ProdGruppe IS NOT NULL ";}		
		if(count($this->params["Geschaeftsjahr"])>0){$whereGeschaeftsjahr="Geschaeftsjahr IN :Geschaeftsjahr ";}else{$whereGeschaeftsjahr="Geschaeftsjahr IS NOT NULL ";}		
		if(count($this->params["ArtBez"])>0){$whereArtBez="ArtBez IN :ArtBez ";}else{$whereArtBez="ArtBez IS NOT NULL ";}		
		if(count($this->params["Quartal"])>0){$whereQuartal="Quartal IN :Quartal ";}else{$whereQuartal="Quartal IS NOT NULL ";}		
		if(count($this->params["Monat"])>0){$whereMonat="Monat IN :Monat ";}else{$whereMonat="Monat IS NOT NULL ";}		

			SELECT Sell, Geschaeftsjahr, Monat, Quartal, WerbegruppeVerbund, KdName, KdNr, Ort, ProdGruppe, ArtBez, BestandHaendler_aktuell, BestandZentrale_aktuell, ADBezirk, Verkaufsmenge, Umsatz
			FROM Reporting.autorep_v_noNulls
			WHERE			
				Sell = (:Sell) AND
				KdName IN (:KdName) AND
				$whereWerbegruppe AND
				$whereKdName AND
				$whereOrt AND
				$whereADBezirk AND
				$whereProdGruppe AND
				$whereArtBez AND
				$whereGeschaeftsjahr AND				
				$whereQuartal AND
				$whereMonat

But unfortunatelly I am getting the same error message as below:

Fatal error: Uncaught Exception: Query Error >> [[Microsoft][ODBC Driver 13 for SQL Server]Das COUNT-Feld ist nicht korrekt, oder es besteht ein Syntaxfehler] >> SELECT ... C:\xampp\htdocs\byTool\koolreport\datasources\PdoDataSource.php:204 Stack trace: #0 C:\xampp\htdocs\byTool\koolreport\KoolReport.php(255): koolreport\datasources\PdoDataSource->start() #1 C:\xampp\htdocs\byTool\pivot\index.php(8 in C:\xampp\htdocs\byTool\koolreport\datasources\PdoDataSource.php on line 204

Kind regards, Yilmaz