This topic is locked
[SOLVED]

 Custom Reports

3/28/2012 8:44:55 AM
PHPRunner General questions
S
Sfereday author

Good Morning. I want to state that I am a very novice user of both the product and PHP. I am trying to get two summed figures as a percentage of each other on a separate line. I have a report set up to take 2 values out of mysql. The values are NumRep and NumImage. When I tell PHPrunner to sum them I get a global object of {$global_totalNumRep_sum} and {$global_totalNumImage_sum}. When I take just these values I get the numbers themselves. I've tried a few things. I've tried:

<?php

$first_Number={$global_totalNumRep_sum}

$second_Number={$global_totalNumImage_sum}

$third_Number=100
$sum_total=$first_Number/$second_Number*$third_Number
print($sum_total)

?>
This did not work. What happens is as soon as I go from code to design mode it replaces the {$global_totalNumRep_sum} and {$global_totalNumImage_sum} with a bunch of table properties.

I've tried just randomly pushing things around. That didn't work either. I've tried yelling at it. The cold blank stare from the monitor was a little unnerving.
I went a different route and though maybe I could do it in an SQL Querry, but when I do that it gives me a strange value. I think it gives me 72% when the numbers that get populated are 262 and 12.
Any help would be greatly appreciated. Thanks in advance. Below is a snidbit of the code.
class=report border=0 cellSpacing=0 cellPadding=1 width=auto>

<TBODY>

<TR>

<TD class=blackshade>Number of Repeated Images</TD>

<TD class=blackshade>Number of Images</TD></TR>{BEGIN grid_row}{END grid_row}

<TR>

<TD class=blackshade>Number of Repeated Images</TD>

<TD class=blackshade>Number of Images</TD></TR><!--------------------------------------global summary-------------------------------------------->

<TR>

<TD class=globalsummary colSpan=2>Global summary {$global_total_cnt} -

records total</TD></TR><!-- Sum -->

<TR>

<TD class=globalsummary>Sum&nbsp; {$global_totalNumRep_sum} </TD>

<TD class=globalsummary>{$global_totalNumImag_sum}

</TD></TR></TBODY></TABLE></DIV>{END grid_block}</DIV>

{BEGIN pagination_block}<DIV id="pagination_block{$id}">{$pagination}</DIV>{END pagination_block}</DIV><B

class=xbottom><B class=xb4b></B><B class=xb3b></B><B class=xb2b></B><B

class=xb1b></B></B></DIV>{$footer}

</DIV></DIV>{END body}</BODY></HTML>

G
giusepe9 3/28/2012

http://www.asprunner.com/forums/topic/17907-calculation-from-a-column-total-on-report/
---
the problem occurs when some field groupings done this method is not applied correctly
group by ..

S
Sfereday author 3/28/2012
S
Sfereday author 3/28/2012



http://www.asprunner.com/forums/topic/17907-calculation-from-a-column-total-on-report/
---
the problem occurs when some field groupings done this method is not applied correctly
group by ..


When I do what is listed at the other fix I get a division by 0 event.

My code looks like this
$sum_1 = $xt->getvar("Global_totalNumRep_sum");

$sum_2 = $xt->getvar("Global_totalNumImage_sum");

$division = $sum_1 / $sum_2;

$xt->assign("division",$division);

S
Sfereday author 3/28/2012



When I do what is listed at the other fix I get a division by 0 event.

My code looks like this
$sum_1 = $xt->getvar("Global_totalNumRep_sum");

$sum_2 = $xt->getvar("Global_totalNumImage_sum");

$division = $sum_1 / $sum_2;

$xt->assign("division",$division);


Further I'm getting this
Error type 2

Error description Division by zero

URL 10.18.130.205/radimage/Total_By_Count_report.php?

Error file C:\wamp\www\RadImage\include\Total_By_Count_events.php

Error line 59

SQL query SELECT count(1) as countField, SUM(NumRep) as NumRepSUM, SUM(NumImag) as NumImagSUM FROM (SELECT NumRep, NumImag, Date FROM master ) original

S
Sfereday author 4/5/2012

I'm going a completely different route here. I can get this to kind of work, but I am getting stuck when I try to execute. I can get the information with a plain old SQL Query. My query looks like this:
SELECT

avg((NumRep/(master.NumImag+master.numrep))100),

FROM master
I get back the exact information I'm looking for. Now, if I put it in like this:
SELECT

avg((NumRep/(master.NumImag+master.numrep))
100),

'master'.Date

FROM master
I get the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master.NumImag+master.numrep))*100)) as ((NumRep/(master.NumImag+master' at line 1

SELECT count(1) as
countField, AVG(((NumRep/(master.NumImag+master.numrep))100)) as((NumRep/(master.NumImag+master.numrep))100)AVG, COUNT(((NumRep/(master.NumImag+master.numrep))100)) as((NumRep/(master.NumImag+master.numrep))100)NAVGFROM (SELECT ((NumRep/(master.NumImag+master`.numrep))*100) FROM master ) original
When I do the same query in navicat I get the information exactly the way I want it. When it is in PHP runner it bombs out when I load the report page.

S
Sfereday author 4/5/2012

So I figured it out. Here is my sql code that worked.
SELECT

((NumRep/(master.NumImag+master.numrep))*100) AS Percent of Repeats,

Date

FROM master
I then select the AVG portion inside PHP runner to make it all work.

I had to tell PHPrunner to name the column Percent of Repeats for it to work correctly.