2. PHPRunner General questions
  3. Calculate on the summary fields in a report
This topic is locked

Calculate on the summary fields in a report

 		8/19/2011 3:50:35 PM
PHPRunner General questions
author

I have been trying to do this and as usual having a issues. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17580&image=1&table=forumtopics' class='bbc_emoticon' alt=':(' />
I have a report that is pulling information from 3 tables.
activity

session

data
I have the report doing a summary on the following field:
pay_amount
I have a field that reports the Amount Received:
Amount_received
How can I get something like this on the report in a field on the report.:
Amount_received - pay_amount sum
As always thanks for any information that you can give me!
I have looked at this but I'm confused by what table to use and the AnyColumn part??

global $conn;
$strSQLExists = "select sum(f1) as f1_sum, sum(f2) as f2_sum from yourTable where AnyColumn='SomeValue'";
$rsExists = db_query($strSQLExists,$conn);
$data=db_fetch_array($rsExists);
if($data)
{
// if record exists do something
}
else
{
// if dont exist do something else
}


Thanks!
C
 		cgphp 8/19/2011

In which table are the Amount_received and pay_amount fields ?
501395 8/19/2011



In which table are the Amount_received and pay_amount fields ?


The Amount_received is in session table and the pay_amount field is in the activity table.
Thanks as always!!
C
 		cgphp 8/19/2011

Sorry, do you have only one report ? Are the tablesj oined in the report query ?
501396 8/19/2011



Sorry, do you have only one report ? Are the tablesj oined in the report query ?


Yes only one report and the tables are joined in the report.
Thanks!
C
 		cgphp 8/19/2011

In the sql query of the report create an alias (difference between the two fields) and call it diff:

SELECT

Amount_received,

pay_amount,

field_3,

field_4,

(Amount_received - pay_amount) as diff

FROM

your_table

INNER JOIN blah blah blah

...

...


In the "Group" tab check off the "Show page summary" option. In the "Total" tab check off "Sum" for the diff field.