KoolReport's Forum

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

Unable to pass the multiple values into sub report query #2064

Open Abhishek opened this topic on on May 10, 2021 - 16 comments
Abhishek commented on May 10, 2021

Dear Team, I am able to pass the multiple values from main report to sub report through url and able to print those values but unable to pass into the sub report query. so,could you please help me in passing multiple values in the query of sub report

SELECT  *
 FROM tblemployeedetails em INNER JOIN tblcustomerenquiries ce ON ce.custTreatedDoctor=em.employeeId
 LEFT JOIN tblbranch tb ON tb.brnchId=ce.custBranchId
 LEFT JOIN tblsourceofinformation ts ON ts.srcId=ce.custSourceId
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end AND ts.srcName IN (:srcName) AND em.employeeFirstName=:employeeFirstName
 AND ce.custEnquiryId IS NOT null ORDER BY ce.custName


        ->params(array(
            ":start"=>$this->params["start"],
            ":end"=>$this->params["end"],
			':brnchName'=>$this->params['brnchName'],
			':srcName'=>$this->params['srcName'],
			":employeeFirstName"=>$this->params["employeeFirstName"]
        ))
		 ->pipe($this->dataStore("result1"));
Abhishek commented on May 10, 2021

Dear Team, I am able to pass the multiple values from main report to sub report through url and able to print those values but unable to pass into the sub report query. so, could you please help me in passing multiple values in the query of sub report.

KoolReport commented on May 10, 2021

Could you please post detail code of how you "pass the multiple values from main report to sub report through url"

Abhishek commented on May 10, 2021

Below is the main report code for the passing values to the sub report through url

 "formatValue"=>function($value,$row)
	{
		$empname = $row['employeeFirstName'];
        $encryptId1=encrypt_url($empname);
		$brnch = $row['branchString'];
		$brnch = $row['sourceString'];
		$date1= $this->params["dateRange"][0];
        $encryptId3=encrypt_url($date1);
		$date2 = $this->params["dateRange"][1];
        $encryptId4=encrypt_url($date2);
    $branchString = "";
    foreach($this->params["brnchName"] as $i=>$bValue)
    {
        $branchString.=$bValue;
		$encryptId2 = encrypt_url($branchString);
    }
	$sourceString = "";
    foreach($this->params["srcName"] as $i=>$bValue)
    {
        $sourceString.=$bValue;
		$encryptId5 = encrypt_url($sourceString);
    }
    
	
	return "<a  href='srcenquiryindex.php?id1=".$encryptId1."&id2=".$encryptId2."&id3=".$encryptId3."&id4=".$encryptId4."&id5=".$encryptId5."'target=\"subreport\">$value</a>";
	},  
	                
	),

Below is the code of sub report values fetching from main report url

require_once "srcenquiry.php";
$srcEnquiry= new srcenquiry([

"employeeFirstName"=>decrypt_url($_GET["id1"]),
'brnchName'=>decrypt_url($_GET['id2']),
//"brnchName"=>$_GET["brnchName"],
"start"=>decrypt_url($_GET["id3"]),
"end"=>decrypt_url($_GET["id4"]),
'srcName'=>decrypt_url($_GET['id5']),
]);
$srcEnquiry->run()->render();
Abhishek commented on May 10, 2021

Dear Team,As per your request I have shared the full detailed code of passing the multiple values from main report to sub report above.

Abhishek commented on May 11, 2021

Dear Team,As per your request I have shared the full detail code of passing the multiple values from main report to sub report above. So, could you please help me on how to pass the values to query.

KoolReport commented on May 11, 2021

That's great. So you want to pass parameters to SubReport from client (js) or from your main report (in php)?

Abhishek commented on May 11, 2021

Dear Team, The below code is the query of the sub report and I am able to print the selected values in the sub report whatever the values passed from main report url.

SELECT  *
 FROM tblemployeedetails em INNER JOIN tblcustomerenquiries ce ON ce.custTreatedDoctor=em.employeeId
 LEFT JOIN tblbranch tb ON tb.brnchId=ce.custBranchId
 LEFT JOIN tblsourceofinformation ts ON ts.srcId=ce.custSourceId
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end AND ts.srcName IN (:srcName) AND em.employeeFirstName=:employeeFirstName
 AND ce.custEnquiryId IS NOT null ORDER BY ce.custName

        ->params(array(
            ":start"=>$this->params["start"],
            ":end"=>$this->params["end"],
			':brnchName'=>$this->params['brnchName'],
			':srcName'=>$this->params['srcName'],
			":employeeFirstName"=>$this->params["employeeFirstName"]
        ))
		 ->pipe($this->dataStore("result1"));

Now could you please help me to pass the values into the query which I am fetching from the main report through URL and the GET method code is above

"formatValue"=>function($value,$row)
	{
		$empname = $row['employeeFirstName'];
        $encryptId1=encrypt_url($empname);
		$brnch = $row['branchString'];
		$brnch = $row['sourceString'];
		$date1= $this->params["dateRange"][0];
        $encryptId3=encrypt_url($date1);
		$date2 = $this->params["dateRange"][1];
        $encryptId4=encrypt_url($date2);
    $branchString = "";
    foreach($this->params["brnchName"] as $i=>$bValue)
    {
        $branchString.=$bValue;
		$encryptId2 = encrypt_url($branchString);
    }
	$sourceString = "";
    foreach($this->params["srcName"] as $i=>$bValue)
    {
        $sourceString.=$bValue;
		$encryptId5 = encrypt_url($sourceString);
    }
    
	
	return "<a  href='srcenquiryindex.php?id1=".$encryptId1."&id2=".$encryptId2."&id3=".$encryptId3."&id4=".$encryptId4."&id5=".$encryptId5."'target=\"subreport\">$value</a>";
	},  
	                
	),
Abhishek commented on May 11, 2021

Dear Team, Please help me with above the issue.

Abhishek commented on May 11, 2021

Dear Team, Please help me with above the issue.

Abhishek commented on May 11, 2021

Dear Team, I just need a small piece of code on how to pass the values fetched from the main report through the URL.

KoolReport commented on May 11, 2021

So I guess you want to pass the value to subReport from client via subReport.update(). In the main report view, you do:

subReport.update("name of subreport",{
    "employeeFirstName":"<?php echo $this->params["employeeFirstName"]; ?>",
    "brnchName":<?php echo json_encode($this->params["brnchName"]); ?>,
})

In the subreport, you will have available parameters like $this->params["employeeFirstName"]

Abhishek commented on May 11, 2021

Dear Team, I am able to pass the values from main report through url and print the values in sub report but the only thing is I am unable to pass those values to the query. Which is present in the sub report.

And the above code which you have provided is not working for my condition.

SELECT  *
 FROM tblemployeedetails em INNER JOIN tblcustomerenquiries ce ON ce.custTreatedDoctor=em.employeeId
 LEFT JOIN tblbranch tb ON tb.brnchId=ce.custBranchId
 LEFT JOIN tblsourceofinformation ts ON ts.srcId=ce.custSourceId
 WHERE tb.brnchName IN (:brnchName) AND ce.custCreatedDate>=:start AND ce.custCreatedDate<=:end AND ts.srcName IN (:srcName) AND em.employeeFirstName=:employeeFirstName
 AND ce.custEnquiryId IS NOT null ORDER BY ce.custName

        ->params(array(
            ":start"=>$this->params["start"],
            ":end"=>$this->params["end"],
			":brnchName"=>$this->params["brnchName"],
			':srcName'=>$this->params['srcName'],
			":employeeFirstName"=>$this->params["employeeFirstName"]
        ))
		 ->pipe($this->dataStore("result1"));

<div class="col-sm-4 form-group">
    <label>Select Branch<?php //echo $this->params["statename"]; ?>:</label><br>
        <?php
         BSelect::create(array(
			"name"=>"brnchName",
			"multiple"=>true,
			"dataStore"=>$this->dataStore("result2"),
			"dataBind"=>"brnchName",
			"options"=>array(
			'numberDisplayed' => 5,
			'includeSelectAllOption' => true,
			),			
            "clientEvents"=>array(
                "change"=>"function(){
                    subReport.update('statereportselect',{
                        statename:$('#statename').val(),
                        brnchName:<?php echo json_encode($this->params['brnchName']); ?>,
                    });                     
                }",
            ),
            "attributes"=>array(
                "class"=>"form-control",
            )
        ));    
        ?>
    </div>

	 <div class="col-md-4 form-group">
                
                    <div class="col-sm-5">
                        <label>Select Year:</label>
                       <?php
    Select::create(array(
        "name"=>"yearname",
        "dataStore"=>$this->dataStore("result3"),
        "defaultOption"=>array("--"=>null),
        "dataBind"=>"yearname",
        "clientEvents"=>array(
            "change"=>"function(){
                subReport.update('statereportselect',{
                    statename:$('#statename').val(),
					brnchName:<?php echo json_encode($this->params['brnchName']); ?> ,
					yearname:$('#yearname').val(),
                });
                subReport.update('statereportOrders');
            }",
        ),
        "attributes"=>array(
            "class"=>"form-control",
        )
    ));
    ?>
Abhishek commented on May 14, 2021

Dear Team, I am able to pass the values from main report through url and print the values in sub report but the only thing is I am unable to pass those values to the query. Which is present in the sub report.

And the above code which you have provided is not working for my condition.

Abhishek commented on May 14, 2021

Dear Team, I am able to pass the values from main report through url and print the values in sub report but the only thing is I am unable to pass those values to the query. Which is present in the sub report.

And the above code which you have provided is not working for my condition.

Abhishek commented on May 15, 2021

Dear Team, I am able to pass the values from main report through url and print the values in sub report but the only thing is I am unable to pass those values to the query. Which is present in the sub report.

And the above code which you have provided is not working for my condition.

KoolReport commented on May 15, 2021

Don't just copy and paste our code (which work when you put in javascript block in html). Since your subReport.update() function is inside a string, you should transform like this:

    "change"=>"function(){
                subReport.update('statereportselect',{
                    statename:$('#statename').val(),
		    brnchName:".json_encode($this->params['brnchName']).",
		    yearname:$('#yearname').val(),
                });
                subReport.update('statereportOrders');
    }",

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.