2. PHPRunner General questions
  3. Reports – Summary Percents
This topic is locked

Reports – Summary Percents

 		8/14/2007 2:02:45 PM
PHPRunner General questions
hfg author

I would like to calculate percents in my sub-totals on a report.
A simple example, a table with the following fields

Location (text)

Cars_Purchased (number)

Cars_Sold (number)
The Report is grouped by Location with Cars_Purchased and Cars_Sold each having a Sum in the summary section (I am using global summaries as well).
I would like to find the overall percent of cars sold by dividing the Sum of Cars_Sold by the Sum of Cars_Purchased and show this in the summary section of the report for each location (global as well).
It would look like this:
Location: North

Cars_Purchased Cars_Sold Percent

10 5 50
Can this be done?
J
 		Jane 8/15/2007

Hi,
you can do it editing your SQL query on the Edit SQL query tab.

Here is a sample:

select

...

Sum(Cars_Sold)/Sum(Cars_Purchased)*100 as Percent,

...

from TableName
hfg author 8/16/2007

Unfortunately for my situation the average won't work. I am using sub-queries in the SQL so at times the value in the field is Null. For example:
Customer Contacted Responded

Jim Yes Yes

Mary No No

Sara Yes No
In my SQL I do a count of Contacted and Responded so it ends up looking like this
Customer Contacted Responded Resp/Cont

Jim 1 1 100%

Mary Null Null Null

Sara 1 0 0%

Totals 2 1 averages to 50%
I want to figure out what percent of those contacted responded which in this case is 50% since Mary does not count as she has not been contacted. The average sees the Null in the Responded divided by Contacted as 100% so I end up with 66% when it does the average.
Is there a way within the report itself, under visual editor, to divide the "Sum or Responded" by the "Sum of Contacted"
Thanks
J
 		Jane 8/17/2007

Hi,
to calculate total and persents on the list page you can use ListOnLoad event on the Events tab.

Here is a sample code:

global $conn;

$str = "select sum(Contacted), sum(Responded) from TableName";

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

echo "Total of Contacted ".$data[0];

echo "Total of Responded ".$data[1];

echo "Total of Contacted/Total of Responded ".$data[0]/$data[1];
hfg author 8/17/2007

Unforntatanly I am working in reports and have sub-totals for various catagories. The solution given only show a compresensive total and does not break it down for each catagory.
hfg author 8/23/2007

I don't know if this will help or not.
In the visual editor I would in a perfect world like to write this formula:
({$row.groupLot_totalAppts_Showed_sum} / {$row.groupLot_totalAppts_Made_sum}) * 100
Is there a way to write this formula into the code in visual editor?
Or a way in the Reports events to do so and then a snippet of code to place in the visual editor in order to control where the result of the formula ends up for each grouping.
I would also like to do this for the global totals.
I have three levels on this report:
Lot

Region

Global
Thanks