2. PHPRunner General questions
  3. Division by Zero Error
This topic is locked

Division by Zero Error

 		12/4/2009 9:59:35 AM
PHPRunner General questions
S
 swgiles author

On my site made through PHPRunner 5.1, I give the user the ability to change the time range of his report. My site displays both the some and the average of a few columns. I have found that on certain pages, when the user pick time range that has no data, a division by zero php error is thrown, pointing to that reports reportlib, where the average and sum of the data is being calculated, specifially the line
$s["Column1"]['AVG'] /= $s["Column1"]['count'];
However is it solved by changeing the
if($data["Column1"] !== null)
to
if($data["Column1"] != null)
This I find a bit confusing. Is there anything I can do from PHPRunner or my database to make sure this doesn't happen? There hasn't been any changes done to the reportlib, the only changes were athetic or involved with my "change report range" feature.
Thank you for your help.
S
 		swgiles author 12/4/2009

On an unrelated note, I seem to be having an issue with my order by (or perhaps group by) clause. I have a query the aggregates by day and then hour, and then orders by day and then hour. This works beautifully as a SQL query in my database (SQL server) and even shows the right information in the phprunner query page, however once my page is built it doesn't seem to realize there is an order by statement (everything is arranged ascending) and when there is a 0 (datepart (hour sometime), 24 is set to 0) it just displays null. Even stranger is this query worked previously just fine when made in phprunner 5.0.
I can deliver more information at request, I don't quite know if maybe this should be talked about as a bug? I also fiddled around in the variables files, and changing the $gstrOrderBy variable doesn't do anything.
J
 		Jane 12/8/2009

Steve,
thank you for pointing me to this bug.

We'll fix it in the next update.
S
 		swgiles author 12/9/2009



Steve,
thank you for pointing me to this bug.

We'll fix it in the next update.


Hmm well I don't know which bug you were referring to, but I will post just once more asking for help with the query issue, as I am totally stumped with that one.
I created a page with a single report, and the only changes made to that report was changing the query. This is the query -

SELECT TOP (100) PERCENT dbo.v_ProdTbl_Durations.ProdDate,

dbo.v_ProdTbl_Durations.ShiftID,

DATEPART(HOUR, dbo.v_ProdTbl_Durations.LocalTime) AS LocalHour,

DATEPART(HOUR, dbo.v_ProdTbl_Durations.StartTime) AS UTCHour,

dbo.StatEventKeyTbl.EventDesc,

SUM(dbo.v_ProdTbl_Durations.DurSec) AS DurSec,

(CASE IJ.InDurSec WHEN 0 THEN 0 ELSE (CAST(SUM(dbo.v_ProdTbl_Durations.DurSec) AS FLOAT)/CAST(IJ.InDurSec AS FLOAT))*100 END) AS [Percent]

FROM dbo.v_ProdTbl_Durations INNER JOIN

(SELECT DATEPART(HOUR,StartTime) AS InUTCHour, ProdDate AS InProdDate, DATEPART(HOUR,LocalTime) AS InLocalHour, SUM(DurSec) AS InDurSec

FROM dbo.v_ProdTbl_Durations AS v_ProdTbl_Durations_1

GROUP BY DATEPART(HOUR,StartTime), DATEPART(HOUR,LocalTime), ProdDate) AS IJ ON dbo.v_ProdTbl_Durations.ProdDate = IJ.InProdDate AND DATEPART(HOUR, dbo.v_ProdTbl_Durations.StartTime) = IJ.InUTCHour AND

DATEPART(HOUR, dbo.v_ProdTbl_Durations.LocalTime) = IJ.InLocalHour

INNER JOIN dbo.StatEventKeyTbl ON dbo.v_ProdTbl_Durations.Status = dbo.StatEventKeyTbl.Event

GROUP BY

dbo.v_ProdTbl_Durations.ProdDate,

dbo.v_ProdTbl_Durations.ShiftID,

DATEPART(HOUR, dbo.v_ProdTbl_Durations.StartTime),

DATEPART(HOUR, dbo.v_ProdTbl_Durations.LocalTime),

dbo.StatEventKeyTbl.EventDesc,

IJ.InDurSec

HAVING

SUM(DurSec)>0

ORDER BY

dbo.v_ProdTbl_Durations.ProdDate DESC,

dbo.v_ProdTbl_Durations.ShiftID DESC,

DATEPART(HOUR, dbo.v_ProdTbl_Durations.LocalTime) DESC


Which works fine in the PHPRunner "veiw your sql" tab, fine as a query to my database, but orders everything as JUST ascending when looking at the website. I am not opposed to going into the reportlib and changing this manually, since obviously something is going wrong with how PHPRunner is assigning things to the template. As I have said changes in the variables file have no effect.
EDIT-

a few other notes for clarity

  • I have other querys on other pages just as complicated as this, selecting from tables made in the query, which do work.

    -It works fine in 5.0
J
 		Jane 12/10/2009

Steve,
I see what you're saying.

Unfortunately it's impossible to change sorting order for group fields (selected on the Report: Group fields tab) on the reports.