KoolReport's Forum

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

How to pass the values/parameters from one report other report #2046

Closed Abhishek opened this topic on on Apr 24, 2021 - 29 comments
Abhishek commented on Apr 24, 2021

Dear Team, Please help me it is an urgent requirement actually I am using an report in which i have implemented Bselect and date range picker and when the data is loaded i have provided hyper link to one of the column.

But the issue is I am unable to pass the selected values of the above report into the other report.

As you can see I have provided the hyperlink to the data(number of enquirers column) so when the link is clicked i need to display the data which as to be executed in other report and I need to pass those selected value to the other report in which I need to display the data of the respective clicked link.

Please I need your help because I need to prepare more than 20 report on the same concept.

Abhishek commented on Apr 24, 2021

Respected Team, Please provide solution some to the above issue of how to pass the selected values of the main report to other report of the query.

Please help me I ma in need. Please

Abhishek commented on Apr 24, 2021

Respected Team, Please provide solution some to the above issue of how to pass the selected values of the main report to other report of the query.

Please help me I ma in need. Please

Abhishek commented on Apr 24, 2021

Respected Team, Please provide solution some to the above issue of how to pass the selected values of the main report to other report of the query.

Please help me I ma in need. Please

Abhishek commented on Apr 24, 2021

Respected Team, Please provide solution some to the above issue of how to pass the selected values of the main report to other report of the query.

Please help me I ma in need. Please

Abhishek commented on Apr 26, 2021

Respected Sir/Madam, What mistake I have done and why am I not getting reply/support from you people. Please help me and support me.

KoolReport commented on Apr 26, 2021

You will use the $_GET in this case. In the link of table, you put the necessary parameters, for example:

<a href="details.php?id=35">4</a>

In the details.php, you do:

$report = new DetailReport([
    "id"=>$_GET["id"]
]);
$report->run()->render();

Inside the DetailReport, you can get parameter from $this->params["id"] for your query:

class DetailReport  extends \koolreport\KoolReport
{
    ...
    protected function setup()
    {
          $this->src("mydb")->query("SELECT ... WHERE id=@id")
            ->params("@id"=>$this->params["id"])
            ...
            ->pipe($this->dataStore("result"));
    }
}

At the view of DetailReport.view.php, you can access the parameter as well,

<?php echo $this->params["id"]; ?>

Hope that helps.

Abhishek commented on Apr 26, 2021

Dear Team, thanks for the support and response but still I'm getting issue in passing the value and I have followed your instructions but not achieved 

SELECT  * FROM TB
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end
GROUP BY em.employeeId

        ")
		->params(array(
           
			":start"=>$this->params["dateRange"][0],
                        ":end"=>$this->params["dateRange"][1],
			":tblemployeedetails"=>$this->params["tblemployeedetails"],
			":brnchName"=>$this->params["brnchName"]
        ))

From the above query i need to pass the values to the another query of sub report which is below and I have provided as per your instructions. 

SELECT * FROM TB
 WHERE tb.brnchName IN (@brnchName) AND em.employeeFirstName=@employeeFirstName AND ce.custCreatedDate>=@start AND ce.custCreatedDate<=@end

->params(array(
            "@start"=>$this->params["start"],
            "@end"=>$this->params["end"],
			"@brnchName"=>$this->params["brnchName"],
			"@employeeFirstName"=>$this->params["employeeFirstName"]
        ))

And I also implemented the view and _GET code also. And I have provided the code below.

"formatValue" => function ($value, $row) {
                 
return "<a  href='performanceenquiryindex.php?employeeFirstName=" . $row['employeeFirstName'] . " && brnchName=" . $row['brnchName'] . " && start=" . $value['start'] . " && end=" . $value['end'] . "'target=\"subreport\">$value</a>";
   
                },        
	),

This is the _GET code of index file of the sub report.

require_once "performanceenquiry.php";
$PerformanceEnquiry= new performanceenquiry([
"employeeFirstName"=>$_GET["employeeFirstName"],
"brnchName"=>$_GET["brnchName"],
"start"=>$_GET["start"],
"end"=>$_GET["end"]

]);

I have provide you the whole code which i have implemented please could you help me where i have done the mistake please.

Abhishek commented on Apr 26, 2021

Dear Team, As per your instructions I have implemented the logic but still I am facing the issue in passing the values from main report to sub report.

And I have provide the implemented code above please review it once. please

KoolReport commented on Apr 26, 2021

Could you please check the generated url to see whether is correct. It seems to me the url is generated wrongly.

Abhishek commented on Apr 27, 2021

Respected Team, How do I pass the dateRange value in the url. 

SELECT  * FROM TB
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end
GROUP BY em.employeeId

        ")
		->params(array(
           
			":start"=>$this->params["dateRange"][0],
                        ":end"=>$this->params["dateRange"][1],
			":tblemployeedetails"=>$this->params["tblemployeedetails"],
			":brnchName"=>$this->params["brnchName"]
        ))

And in the format value how do I need to pass the values I mean as $value or else as $row. but when I need to pass the value as $row or $value I am unable to fetch the values. from the above query.

"formatValue" => function ($value, $row) {
                 
return "<a  href='performanceenquiryindex.php?employeeFirstName=" . $row['employeeFirstName'] . " & brnchName=" . $row['brnchName'] . " & start=" . $value['start'] . " & end=" . $value['end'] . "'target=\"subreport\">$value</a>";
   
                },        
	),
Abhishek commented on Apr 27, 2021

Respected Team, How do I pass the dateRange value in the url.

SELECT  * FROM TB
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end
GROUP BY em.employeeId

        ")
		->params(array(
           
			":start"=>$this->params["dateRange"][0],
                        ":end"=>$this->params["dateRange"][1],
			":tblemployeedetails"=>$this->params["tblemployeedetails"],
			":brnchName"=>$this->params["brnchName"]
        ))

And in the format value how do I need to pass the values I mean as $value or else as $row. but when I need to pass the value as $row or $value I am unable to fetch the values. from the above query.

"formatValue" => function ($value, $row) {
                 
return "<a  href='performanceenquiryindex.php?employeeFirstName=" . $row['employeeFirstName'] . " & brnchName=" . $row['brnchName'] . " & start=" . $value['start'] . " & end=" . $value['end'] . "'target=\"subreport\">$value</a>";
   
                },        
	),
KoolReport commented on Apr 27, 2021

You do:

"formatValue" => function ($value, $row) {
                 
return "<a  href='performanceenquiryindex.php?employeeFirstName=" . $row['employeeFirstName'] . " & brnchName=" . $row['brnchName'] . " & start=" . $this->params["dateRange"][0] . " & end=" . $this->params["dateRange"][1] . "'target=\"subreport\">$value</a>";
   
                },        
	),
Abhishek commented on Apr 27, 2021

Dear Team, Thanks for the above code and I'm able to pass the values but there is a small issue i.e I'm using Bselect input in order to select the branch name and now I'm unable to pass the selected branch name in the url.

And when I'm passing the date value the current date is getting displayed but in the url the selected date is getting passed. URL:

performanceenquiryindex.php?employeeFirstName=DR NISARGA & brnchName=3 & start=2021-02-01 00:00:00 & end=2021-02-28 23:59:59

echo:

2021-04-27 2021-04-27 3(branch name)
KoolReport commented on Apr 27, 2021

You do:

"formatValue"-=>function($value,$row) {
    $branchString = "";
    foreach($tihs->params["brnchName"] as $i=>$bValue)
    {
        $branchString .= "&brnchName[]=".$bValue;
    }

return "<a  href='performanceenquiryindex.php?employeeFirstName=" . $row['employeeFirstName']. $branchString." & start=" . $this->params["dateRange"][0] . " & end=" . $this->params["dateRange"][1] . "'target=\"subreport\">$value</a>";
}
Abhishek commented on Apr 27, 2021

Dear Team,Thanks for the response and for the code but still I'm facing issue in passing the branch name value.

Abhishek commented on Apr 28, 2021

Dear Team, Sorry and Thanks for the above actually the mistake is from my side the code is working properly and we are able to pass the values. But could please help me in how to pass those values into the query, please.

Abhishek commented on Apr 28, 2021

Dear Team,The below code which I'm using to get the value from the main report.

$PerformanceEnquiry= new performanceenquiry([
"employeeFirstName"=>$_GET["employeeFirstName"],
"brnchName"=>$_GET["branchString"],
"start"=>$_GET["start"],
"end"=>$_GET["end"]
]);
$PerformanceEnquiry->run()->render();

The below code which I'm using to display the value which I'm fetching

<?php echo $this->params["start"]; ?>
	<?php echo $this->params["end"]; ?>
	<?php echo $this->params["brnchName"]; ?>
	<?php echo $this->params["employeeFirstName"]; ?>

Now how do I pass the above values into below query

SELECT *
 FROM TB em INNER JOIN tblcustomerenquiries ce ON ce.custTreatedDoctor=em.employeeId
 LEFT JOIN tblbranch tb ON tb.brnchId=ce.custBranchId
 WHERE tb.brnchName IN (@brnchName) AND em.employeeFirstName=@employeeFirstName AND ce.custCreatedDate>=@start AND ce.custCreatedDate<=@end

 ->params(array(
            "@start"=>$this->params["start"],
            "@end"=>$this->params["end"],
			"@brnchName"=>$this->params["brnchName"],
			"@employeeFirstName"=>$this->params["employeeFirstName"]
        ))
KoolReport commented on Apr 28, 2021

You change this:

"brnchName"=>$_GET["branchString"],

to this:

"brnchName"=>$_GET["brnchName"],

In the performanceenquiryindex.php

Abhishek commented on Apr 28, 2021

Dear Team, Thanks for the above code and but when I have use the code before and after your reply I am facing with below error.

 PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\foldername\pages\vendor\koolreport\core\src\datasources\PdoDataSource.php on line 428

And could you also help me on how to pass those values to the query please and I am using the below code so could conform me

if(isset($_GET['employeeFirstName'])
{
	['employeeFirstName'] = $_GET['employeeFirstName'];
}
if(isset($_GET['brnchName'])
{
	['brnchName'] = $_GET['brnchName'];
}

if(isset($_GET['start'])
{
	['start'] = $_GET['start'];
}
if(isset($_GET['end'])
{
	['end'] = $_GET['end'];
}
 {
  
	 $this->src("dbname")->query("SELECT *
 FROM TB 
 WHERE brnchName IN (@brnchName) AND employeeFirstName=@employeeFirstName AND custCreatedDate>=@start AND custCreatedDate<=@end")
		
        ->params(array(
            "@start"=>$this->params["start"],
            "@end"=>$this->params["end"],
			"@brnchName"=>$this->params["brnchName"],
			"@employeeFirstName"=>$this->params["employeeFirstName"]
        ))
		 ->pipe($this->dataStore("result1"));
KoolReport commented on Apr 28, 2021

What do you mean to do with this:

if(isset($_GET['employeeFirstName'])
{
	['employeeFirstName'] = $_GET['employeeFirstName'];
}
if(isset($_GET['brnchName'])
{
	['brnchName'] = $_GET['brnchName'];
}

if(isset($_GET['start'])
{
	['start'] = $_GET['start'];
}
if(isset($_GET['end'])
{
	['end'] = $_GET['end'];
}
Abhishek commented on Apr 28, 2021

Dear Team, I have thought that after getting those value using get method I need to use ajax method calling. https://www.koolreport.com/forum/topics/1128 and I have seen this post. If its mistake please excuse me.

Abhishek commented on Apr 28, 2021

Dear Team, Above get code is placed in comment and executed but I'm facing with below error

 PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\filename\pages\vendor\koolreport\core\src\datasources\PdoDataSource.php on line 428
KoolReport commented on Apr 28, 2021

This error showing when you have parameter inside your SQL like @myParam for example but you do not have input value of @myParam inside params() function. Another case is that you do not have @myParam parameter inside SQL but insert value of it inside params(). Please check if there is any parameters declared inside your SQL but missing inside params() function.

Abhishek commented on Apr 28, 2021

Dear Team, I'm unable to understand the above reply so could you please say me where I need to check it

Abhishek commented on Apr 28, 2021

Dear Team, Sorry I have understood with some delay on what are you speaking about but what the value I have passed in the url and the same values I have used in the query and in the inside params() function.

same number of values in url, query and in params() function. And I have passed everything in the params() function.

that are branche name, employee first name, start and end date values.

KoolReport commented on Apr 28, 2021

I think you should test your performanceenquiry report first, try to input real (but directly value into report), let see if it works correctly, for example:

$PerformanceEnquiry= new performanceenquiry([
"employeeFirstName"=>"A name in database",
"brnchName"=>array(3,5), // Some branch value
"start"=>"2021-.....", // A start date
"end"=>"2021-....." // A end date
]);
$PerformanceEnquiry->run()->render();

Make sure the report is working first. Let see if there is no brnchName insert, is there any error. I suspect that if there is no brnchName, it will show error

Abhishek commented on Apr 28, 2021

Dear Team, As per your instructions i have directly passed the values and I'm able to print the values but unable to pass the values into the query. And I have one more issue i.e the branch name which I'm able to passing the url but unable to get the value according to the above code

require_once "performanceenquiry.php";
$PerformanceEnquiry= new performanceenquiry([
"employeeFirstName"=>$_GET["employeeFirstName"],
"brnchName"=>$_GET["brnchName"],
"start"=>$_GET["start"],
"end"=>$_GET["end"]
]);
$PerformanceEnquiry->run()->render();
KoolReport commented on Apr 29, 2021

Step by step, you need to print out the generated url first to see if it is correct. The correct form of url should be:

index.php?employeeFirstName=Jane&brnchName[]=2&brnchName[]=3&start=2021-01-01 00:00:00&end=2021-02-02 00:00:00

Next, test if the $_GET return correct inside performanceenquiryindex.php:

var_dump($_GET);

You will see the employeeFirstname,start and end as text, the brnchName as array.

Next you check if the params inside performanceenquiry report is correctly by:

var_dump($this->params);

put inside the setup() function of performanceenquiry class.

By this way, you will know what went wrong. This applies to any case. Since we do not work directly in the code so it is limited for us to the error or the code you post to give suggestion. We have worked on hundred of case like this so I know it will work, just somewhere something hinder it. Just test step by step, you will see the light. Come back to me with your input, code and wrong output, it will be easy for me to find what may go wrong.

P.S: This code has issue:

['employeeFirstName'] = $_GET['employeeFirstName'];

The form should be like this

$a['employeeFirstName'] = $_GET['employeeFirstName'];

You miss the name of array parameter at beginning.

Abhishek commented on Apr 29, 2021

Dear Team, Really thanks for the detail suggestion and the way of testing the data.

Really, I will test my code as per you instructions and update you will correct data.

Really thanks.

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
help needed
Inputs

Designed and built with by KoolPHP Inc.

Code licensed MIT.