Creating a link and passing data to open another report

kat35601 · June 9, 2015

I have a web page that I get data from my sql server and I want to link to a report from the output of my main report. I have included a picture of my report and circled a couple of the items I want the user to click on and then show a report with details of what that summary number is. so in the link I will need to pass the schedule number and the column that they click on to the sub report. below is my code

<html>
<head>
<title>Status Screen</title>
</head>
<body>
<p> Status Screen </p>

<?php
$connect =odbc_connect("Removed");
if(!$connect) {
   exit("Connection Failed: " . $connect);
}

$sql="SELECT distinct  WIP_master.uompScheduleNumber
,CASE WIP_master.uompScheduleColor
WHEN 'NEON' then 'ORANGERED' 
WHEN 'PINK' THEN 'FUCHSIA'
ELSE WIP_master.uompScheduleColor end AS COLOR


      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '2')
      ) as PRT_DR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='DDF'
      ) as PRT_DDF
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype='FR'
      ) as PRT_FR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '2' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as PRT_Other
      
      
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '3')
      ) as DR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='DDF'
      ) as DDF
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype='FR'
      ) as FR
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '3' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as Other
      
      /***************************************************************************************************/
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE  WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and  (WIP_master1.itemtype ='DR' and WIP_master1.status = '4')
      ) as DR_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='DDF'
      ) as DDF_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype='FR'
      ) as FR_PRP
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '4' and WIP_master1.itemtype not in ('DR','DDF','FR')
      ) as Other_PRP
      
      /******************************************************************************************************/
      
      ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '9'
      ) as Shortage                                          
      
         ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '10'
      ) as Shortage_Prt                                  
      
         ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '11'
      ) as Red                                               
      
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '12'                                                                                     
      ) as Neon                                                
   
            ,(   SELECT COUNT(*)
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master1
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations WIP_locations1 ON WIP_master.location = WIP_locations1.location 
      LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status1 ON WIP_master.STATUS = WIP_status1.STATUS
      WHERE WIP_master.uompScheduleNumber =WIP_master1.uompScheduleNumber and WIP_master1.status = '20'                                                                                   
      ) as QC_Hold       
      
        ,s1.UOMPBUILDDATE                            
      FROM OrbeData_KithKitchens.dbo.WIP_master WIP_master
LEFT JOIN OrbeData_KithKitchens.dbo.WIP_locations ON WIP_master.location = WIP_locations.location
LEFT JOIN OrbeData_KithKitchens.dbo.WIP_status WIP_status ON WIP_master.STATUS = WIP_status.STATUS

left join m1_DC.dbo.SalesOrders s1 on s1.UOMPSCHEDULENUMBER=WIP_master.uompScheduleNumber and s1.ompSalesOrderID=WIP_master.ompSalesOrderID



where  s1.UOMPBUILDDATE >= GETDATE()-7  
order by WIP_master.uompScheduleNumber    
" ;
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "<table  cellpadding='pixels'cellspacing='10'><tr>";
echo "<th>ScheduleNumber</th>";
echo "<th>PRT_DR</th>";
echo "<th>PRT_DDF</th>";
echo "<th>PRT_FR</th>";
echo "<th>ScanDR</th>";
echo "<th>ScanDDF</th>";
echo "<th>ScanFR</th>";
echo "<th>PrpDR</th>";
echo "<th>PrpDDF</th>";
echo "<th>PrpFR</th>";



while (odbc_fetch_row($result)) {
  $bgcolor = odbc_result($result, "COLOR");  
  $uompScheduleNumber=odbc_result($result,"uompScheduleNumber");
   $uompScheduleColor=odbc_result($result,"Color");
  $PRT_DR=odbc_result($result,"PRT_DR");
  $PRT_DDF=odbc_result($result,"PRT_DDF");
   $PRT_FR=odbc_result($result,"PRT_FR");
  $DR=odbc_result($result,"DR");
  $DDF=odbc_result($result,"DDF");
  $FR=odbc_result($result,"FR");
   $DR_PRP=odbc_result($result,"DR_PRP");
  $DDF_PRP=odbc_result($result,"DDF_PRP");
  $FR_PRP=odbc_result($result,"FR_PRP");
  $Other=odbc_result($result,"Other");      

  echo "<tr><td bgcolor=$bgcolor >$uompScheduleNumber</td>";
  echo "<td style='text-align:center'>$PRT_DR</td>";
  echo "<td style='text-align:center'>$PRT_DDF</td>";
    echo "<td style='text-align:center'>$PRT_FR</td>";
  echo "<td style='text-align:center'>$DR</td>";
  echo "<td style='text-align:center'>$DDF</td>";
  echo "<td style='text-align:center'>$FR</td>";
   echo "<td style='text-align:center'>$DR_PRP</td>";
  echo "<td style='text-align:center'>$DDF_PRP</td>";
  echo "<td style='text-align:center'>$FR_PRP</td>";
}

odbc_close($connect);
?>
</body>
</html>

Barand · June 9, 2015

Can't you just make those 2 items links. EG

echo "<td style='text-align:center'><a href='subreport1.php?sn=$uompScheduleNumber'>$PRT_FR</a></td>";
echo "<td style='text-align:center'><a href='subreport2.php?sn=$uompScheduleNumber'>$DR</a></td>";

kat35601 · June 10, 2015

echo "<td style='text-align:center'><a href='subreport1.php?sn=$uompScheduleNumber'>$PRT_FR</a></td>";

That works great how would I add a second parameter $dr_fr??????

Barand · June 10, 2015

href='subreport2.php?sn=$uompScheduleNumber&dr=$DR'

kat35601 · June 10, 2015

on the subreport page would th post look like this????

$uompScheduleNumber=$_POST["$uompScheduleNumber"];

Barand · June 10, 2015

You are passing the values via the query string in the URL, therefore you need to $_GET instead of $_POST

kat35601 · June 10, 2015

  echo "<td style='text-align:center'><a href='kk_schedule_subreport.php?sn=$uompScheduleNumber&itm=DR'>$PRT_FR</a></td>";

what if I wanted to add a constant like in the example itm='DR' but what would be the correct syntax for that????

Sign In

Creating a link and passing data to open another report

Recommended Posts

kat35601

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information