KoolReport's Forum

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

PDF-Export: pivot tables are not shown #137

mysql pivot php create pdf php pdf php pdf generator php pivot table
Closed bysystem opened this topic on on Oct 25, 2017 - 30 comments
bysystem commented on Oct 25, 2017

Dear support team,

like the example "export/sakila_rental/ I have implemented the PDF export fucntionaly in my following application structure:

export.php:

require_once "rck.php";
$report = new rck;
$report->run()
->export('rckPdf')
->pdf(array(
    "format"=>"A4",
    "orientation"=>"landscape",
))
->toBrowser("rck.pdf");

index.php:

error_reporting(0);
require_once "rck.php";
$rck = new rck;
$rck->run();
?> 

<!DOCTYPE >
<html>
  <head>
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <title>ADR: Rückstände</title>
	<link rel="shortcut icon" href="nikon_favicon.ico">
	<link rel="stylesheet" href="../autorep.css" />
  </head>
  <body>  
	<div id="main">
		<?php $rck->render();?>
	</div>
  </body>
</html>

in rck.php "Exportable" is bound:

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\cleandata\FillNull;
use \koolreport\processes\Custom;

class rck 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;

And finally rckPdf.view.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;

?>

<html>
    <body style="margin:0.5in 1in 0.5in 1in">
		<link rel="stylesheet" href="../koolreport/clients/bootstrap/css/bootstrap.min.css" />
		<link rel="stylesheet" href="../koolreport/clients/bootstrap/css/bootstrap.theme.min.css" />
		<link rel="stylesheet" href="../autorep.css" />  
        <div class="page-header" style="text-align:right"><i>AutoRep</i></div>
        <div class="page-footer" style="text-align:right">Seite {pageNum}</div>
        <div class="text-center">
            <h1>ADR: Rückstände</h1>
            <h4>AussenDienstReporting</h4>
        </div>
<hr/>

<!--	<div id="col1"> -->

		<div id="col1_content" class="clearfix">
			<h3> <img src="icon_grid.png"/>   RÜCKSTÄNDE: KundenNr. / Kundenname / Kunden P.O. / Produktname / Bestelldatum / Schedule-Datum</h3>
				<?php
					if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.RCK1")->countData()>0))
					{
						PivotTable::create(array(
						  "dataStore"=>$this->dataStore('Reporting.RCK1'),

						  "measures" => array(
							"Openqty - sum",
							"Openval - sum",
						  ),
							  
						  "headerMap" => array(
							"Openqty - sum" => "∑ RCK-Menge",
							"Openval - sum" => "∑ RCK-Umsatz pro Monat",
						  ),
						 
						  "rowCollapseLevels" => array(0), //0 = zu, 1 = 1. Level auf, 2 = 1. + 2. Level auf etc.
						  "columnCollapseLevels" => array(2),
						  
						  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
						 
						));
					}
				?>     
		</div>		

		<div id="col1_content" class="clearfix">
			<h3> <img src="icon_grid.png"/>   RÜCKSTÄNDE: Artikel</h3>
				<?php
					if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.RCK2")->countData()>0))
					{
						PivotTable::create(array(
						  "dataStore"=>$this->dataStore('Reporting.RCK2'),

						  "measures" => array(
							"Openqty - sum",
							"Openval - sum",
						  ),
							  
						  "headerMap" => array(
							"Openqty - sum" => "∑ RCK-Menge",
							"Openval - sum" => "∑ RCK-Umsatz pro Monat",
						  ),
						 
						  "rowCollapseLevels" => array(0), 
						  "columnCollapseLevels" => array(2),
						  
						  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
						 
						));
					}
				?>     
		</div>		
			
	</div>
  </body>
</html>

My frontend view is like:

In the outcoming rck.pdf file I removed the left column so that only the 2 pivot tables should be shown there. BUT I see only the titles without the pivot tables as below! What is wrong in my code?

Kind regards

KoolReport commented on Oct 26, 2017

Could you please make some small test. For example, try to replace the PivotTable with some text to check if the text is shown, then you move to test if pivot table can be exported well. You create a static pivot table with sample data and try to export. By this way, you can know what went wrong.

bysystem commented on Oct 26, 2017

Dear Support team,

thx for your hint. I just tested it as follows:

  • I added in the first part a simple test text instead of the pivot table by:

    <div id="col1_content" class="clearfix">
	<h3> <img src="icon_grid.png"/>   RÜCKSTÄNDE: KundenNr. / Kundenname / Kunden P.O. / Produktname / Bestelldatum / Schedule-Datum</h3>
		<?php
			echo "This is a test text INSTEAD of PIVOT TABLE <br/><br/>";
		?>     
</div>

  • And I added a simple TABLE part instead of PIVOT by:

    		<?php
		echo "This is the TABLE version INSTEAD of PIVOT: <br/><br/>";
			if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.RCK3")->countData()>0))
			{
				Table::create(array(
				  "dataStore"=>$this->dataStore('Reporting.RCK3'),

						"columns"=>array(
							"SalesRep"=>array(
								"label"=>"SalesRep"
							),
							"SchP"=>array(
								"label"=>"SchP"
							),
							"Description"=>array(
								"label"=>"Description"
							),		
							"Material"=>array(
								"label"=>"Material"
							),			
							"Name"=>array(
								"label"=>"Name"
							),
							"City"=>array(
								"label"=>"City"
							),
							"Openqty"=>array(
								"label"=>"Openqty"
							),	
							"Openval"=>array(
								"label"=>"Openval"
							)										
						),
					"cssClass"=>array(
						"table"=>"table table-hover table-bordered"
					)
					 
				));
			}
		?>

However in the my frontend view I see all these elements: means sample text "This is the TABLE version INSTEAD of PIVOT" and the corresponding Table as last part on the webpage:

But the result of my PDF view is still without TABLE! The sample test text is visible:

What could be the reason?

Kind regards,

KoolReport commented on Oct 26, 2017

What I mean is you post the text inside the condition if to see if the code reach inside the condition. Then you test a static pivot outside of condition to see if pivot works. I suspect that the condition if return false so even the Table can not display. Another test you may do is instead of export("rfcPdf"), you try render("rfcPdf") to view it in browser first.

bysystem commented on Oct 26, 2017

Just followed your hint and: 1. I put the echo part into the IF condition 2. Then I added a static pivot outside of IF condition to see if pivot works. 3. Tried to render("rckPdf") as follows:

export.php: require_once "rck.php"; $report = new rck; $report->run() ->render('rckPdf');

rckPdfview.php:

	<div id="col1_content" class="clearfix">
		<h3>&nbsp;<img src="icon_grid.png"/>&nbsp;&nbsp;&nbsp;RÜCKSTÄNDE: KundenNr. / Kundenname / Kunden P.O. / Produktname / Bestelldatum / Schedule-Datum</h3>
			<?php
				echo "This is a test text INSTEAD of PIVOT TABLE <br/><br/>";
							PivotTable::create(array(
							  "dataStore"=>$this->dataStore('Reporting.RCK1'),

							  "measures" => array(
								"Openqty - sum",
								"Openval - sum",
							  ),
								  
							  "headerMap" => array(
								"Openqty - sum" => "&sum; RCK-Menge",
								"Openval - sum" => "&sum; RCK-Umsatz",
							  ),
							 
							  "rowCollapseLevels" => array(0), //0 = zu, 1 = 1. Level auf, 2 = 1. + 2. Level auf etc.
							  "columnCollapseLevels" => array(2),
							  
							  "totalName" => '<div align="right"><strong>GESAMT</strong></div>'
							 
							));					
			?>     
	</div>		

	<div id="col1_content" class="clearfix">
		<h3>&nbsp;<img src="icon_grid.png"/>&nbsp;&nbsp;&nbsp;RÜCKSTÄNDE: Artikel</h3>
			<?php
			//echo "This is the TABLE version INSTEAD of PIVOT: <br/><br/>";
				if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.RCK3")->countData()>0))
				{
				echo "INSIDE IF: This is the TABLE version INSTEAD of PIVOT: <br/><br/>";
					Table::create(array(
					  "dataStore"=>$this->dataStore('Reporting.RCK3'),

							"columns"=>array(
								"SalesRep"=>array(
									"label"=>"SalesRep"
								),
								"SchP"=>array(
									"label"=>"SchP"
								),
								"Description"=>array(
									"label"=>"Description"
								),		
								"Material"=>array(
									"label"=>"Material"
								),			
								"Name"=>array(
									"label"=>"Name"
								),
								"City"=>array(
									"label"=>"City"
								),
								"Openqty"=>array(
									"label"=>"Openqty"
								),	
								"Openval"=>array(
									"label"=>"Openval"
								)										
							),
						"cssClass"=>array(
							"table"=>"table table-hover table-bordered"
						)
					 
					));
				}
			?>     
	</div>

The result in my front end is still without rendering the sample text, without the static pivot table (only the empty cover of the pivot table without values) and the simple table at the bottom:

But I get a warning message: Warning: Declaration of koolreport\pivot\widgets\PivotTable::create($params) should be compatible with koolreport\core\Widget::create($params, $return = false) in C:\xampp\htdocs\byTool\koolreport\packages\pivot\widgets\PivotTable.php on line 51

Any other hint?

KoolReport commented on Oct 26, 2017

Please do me a favor, you go to the "packages/pivot/widgets/PivotTable.php" and remove below function:

static function create($params)
{
...
}

Let see if there is no warning.

bysystem commented on Oct 27, 2017

OK, just removed the static func and the warning message does not appear anymore!

The web output is still the same:

KoolReport commented on Oct 27, 2017

please check the data, I suspect there is no data for pivot.

bysystem commented on Oct 27, 2017

I just added the TEST pivot part into my productive rckview.php (at the very bottom) to test whether there is data or not! it seems so that there is data:

Another reason may be?

KoolReport commented on Oct 27, 2017

The problem is that when you hit the "PDF -Download", do you post the parameters to the report at server so that it can load correct data to put to the rfcPdf to render?

bysystem commented on Oct 27, 2017

I assume that I post the needed parameters to the rckPdf.php. But how can I check this to be sure?

KoolReport commented on Oct 28, 2017

Have you had a look at our advanced example where we integrate inputs and export feature into one. It seems close to your case.

bysystem commented on Oct 30, 2017

I just followed your hint with the advance example and could now render the PDF output but only with ONE PARAMETER (which is single select option)!

Thx a lot for this important hint related to the right way to post the parameters to the report at the server.

BUT I need still your support on the following point:

  • in comparision to the advance example I have more than 1 parameter (total 6 and the other parameters are MULTIPLE SELECT OPTIONS)

  • So if I post only the first hidden input "SalesRep" (single select option) PDF rendering works fine

    <form method="post" action="export.php">

      <input type="hidden" value="<?php echo $this->params["SalesRep"]; ?>" name="SalesRep" />
  <input type="hidden" value="<?php echo $this->params["Name"]; ?>" name="Name" />
  <input type="hidden" value="<?php echo $this->params["City"]; ?>" name="City" />
  <input type="hidden" value="<?php echo $this->params["SchP"]; ?>" name="SchP" />
  <input type="hidden" value="<?php echo $this->params["Description"]; ?>" name="Description" />
  <input type="hidden" value="<?php echo $this->params["Material"]; ?>" name="Material" />
  <button class="btn btn-primary">PDF-Export-Kd-Nr.</button>

    </form>

  • But if I post the additional parameters (for example City which is MULTIPLE SELECT OPTION) no PDF output!!

  • In the source code I see within the hidden tag in that case only value="Array" instead of the chooesen value(s) (here: "Köln")
  • The first SINGLE SELECT OPTION parameter is shown fine (here: value="Ledock")

How can I post MULTIPLE SELECT OPTION parameters here?

KoolReport commented on Oct 30, 2017

Give me a little time, I will make small example to help you.

KoolReport commented on Oct 31, 2017

Below are sample code of a technique called Dynamic Target Form Post ( I named it :D)

You have 2 pages: a.php and b.php. The A page has a form which post parameters to itself when you hit Submit ( Just like your index.php page with selected parameters). The B page is the page you want to receive all parameters when you click to "Post to page B" button in page A ( The B page is like export.php).

a.php

<html>
    <head>
        <title>Page A</title>
    </head>
    <body>
        Value of params = <?php echo isset($_POST["param"])?$_POST["param"]:"Nothing" ?>
        <form id="theForm" method="post">
            <input name="param" />
            <button>Submit</button>
        </form>

        <button onclick="doPost()">Post to B page</button>
        
        <script type="text/javascript">
        function doPost()
        {
            var theForm = document.getElementById("theForm");
            theForm.action = "b.php";
            theForm.submit();
        }
        </script>
    </body>
</html>

b.php

<html>
    <head>
        <title>B Page</title>
    </head>
    <body>
        Received params = <?php echo isset($_POST["param"])?$_POST["param"]:"Nothing" ?>
    </body>
</html>

As you can see in the page A, when you click Post to B page, it will call small javascript to change the action of the form to "b.php" then submit the form. The page B will receive all parameters from page A. Simple and easy, is it.

Now come back to your work. Please do following:

  1. In the rfc.view.php, please assign an id to the form which contains our selection widgets. Let say "parameterForm"
  2. Put the javascript like this:
<script type="text/javascript">
function export()
{
    var _form = document.getElementById("parameterForm");
    _form.action = "export.php";
    _form.submit();
}
</script>
  1. In the export button, you call export() function
<button onclick="export()">Export</button>

Hope that my answer is good and easy to understand. Anything please let me know.

Karl

bysystem commented on Oct 31, 2017

Dear Karl,

thx a lot for your help!

I'm not sure whether I did understand your great idea 100% or not. I tried to implement your suggestion as follows, but however I get an empty pivot result as shown below. Probably something is missing in my code rck.view.php below. So I need your review again:

<?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 export()
{
    var _form = document.getElementById("parameterForm");
    _form.action = "export.php";
    _form.submit();
}
</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>Bezirk</h5>
					<?php
					Select2::create(array(
						"name"=>"SalesRep",
						"dataStore"=>$this->dataStore("ADR.SalesRep"),
						"dataBind"=>"SalesRep",
						"multiple"=>false,
						"attributes"=>array(
							"class"=>"form-control",
						)
					));
					?>
				</div>	

				<div class="form-group" style="width:250px;margin-right:10px;">
				<h5>Kundenname</h5>
					<?php
					Select2::create(array(
						"name"=>"Name",
						"dataStore"=>$this->dataStore("ADR.Name"),
						"dataBind"=>"Name",
						"multiple"=>true,
						"attributes"=>array(
							"class"=>"form-control",
						)
					));
					?>
				</div>
<!-- ...... -->
<!-- ALL OTHER FILTER OPTIONS HERE -->
<!-- ...... -->
	<div id="col1">
		<div class="text-center">
			<form method="post" action="export.php">
<!-- YBC -->
				<button onclick="export()" class="btn btn-primary">PDF-Export</button>
			</form>
		</div>	

<hr/>	
<!-- ..... -->

Here is the output:

Somewhere I'm doing it wrong but where?

Here is my export.php (I render it temporarely to the browser instead of creating a PDF document, it's faster to test):

<?php
error_reporting(0);
require_once "rck.php";
$report = new rck;
$report->run()
//->export('rckPdf')
->render('rckPdf');
//->pdf(array(
//    "format"=>"A4",
//    "orientation"=>"landscape"
//))
//->toBrowser("rck.pdf");
//->saveAs("PDFrck.pdf");
KoolReport commented on Oct 31, 2017

Could you please remove the form arround the Export button and see if it works.

bysystem commented on Oct 31, 2017

Just removed the form tag around the Export button. But in that case the export.php will not be called isn't it?

<div id="col1">
	<div class="text-center">
		<!-- <form method="post" action="export.php"> -->
			<button onclick="export()" class="btn btn-primary">PDF-Export</button>
		<!-- </form> -->
	</div>

But I can't click on the "PDF export" button now >> nothing happens if I click!

KoolReport commented on Oct 31, 2017

When click to the Export button, suppose the javascript function export() should be called. The form action will be change to "export.php" and as a result all parameters should post to export.php. Could you please put 

alert(1);

inside the export() function. Just to test if the function is getting called.

bysystem commented on Oct 31, 2017

Hmm... Strange! Just added alert(1) but nothing happens if I click:

<script type="text/javascript"> function export() {

alert(1);
var _form = document.getElementById("parameterForm");
_form.action = "export.php";
_form.submit();

} </script>

In the web sourcecode the javascript is shows fine:

KoolReport commented on Oct 31, 2017

Let not use the function export(). Please rename the function export() to doExport().

bysystem commented on Oct 31, 2017

Done!

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

What is next todo Karl?

KoolReport commented on Oct 31, 2017

Have you change the the export() to doExport() in the onclick of button Export?

bysystem commented on Oct 31, 2017

Karl goooood news!!

I changed the button onclick="doExport()" as well. And it works like a charme now :-)

Thx soooooo much!

KoolReport commented on Oct 31, 2017

Great :)

bysystem commented on Oct 31, 2017

Only one little issue:

However: My filter submit button triggers now the same result (PDF view) like the PDF Export button.

Means if I make my filter chooses and click on Submit >> the PDF view is shown instead of the rck.view.php.

Could the reason be the same document ID or like that?

KoolReport commented on Oct 31, 2017

Let add this line after _form.submit() fucnction:

_form.action = "index.php";

Let me know if it work

bysystem commented on Oct 31, 2017

Yes sir! That's it!

Excellent work Karl!

10 credits are on the way!!!!

KoolReport commented on Oct 31, 2017

Thank you very much for your generosity :)

mike commented on Aug 7, 2018

<?php

$report = new class1($Data);

$report = new class2( array(

"data"=>$myData    //Insert data into MyReport as parameters.

) );

$report->run()

->export('rckPdf')

->pdf(array(

"format"=>"A4",
"orientation"=>"landscape",

))

->toBrowser("rck.pdf");

?>

can we download report of two diff classes ....

David Winterburn commented on Aug 10, 2018

Hi Mike,

Thanks a lot for your feedback! If you don't mind please create a new thread for your separated issues so other users could follow the forum in a clearer manner. Thank you!

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
Export

Designed and built with by KoolPHP Inc.

Code licensed MIT.