This topic is locked
[SOLVED]

 Running totals and printing only part of the data

7/7/2017 10:42:14 AM
PHPRunner General questions
mbintex author

Hi at all,
I am using running totals in a car logbook solution and followed the tips given here. Works nicely.
Problem is, that the running totals for the total mileage only show the correct result, if all records are shown in the correct order.
If you change the order or want to print lets say only May from a year´s logbook, you get nonsense values.
Is there a way to print partial reports with correct running totals without saving the totals to a field in the database? That is not only taking space, but problematic too, if records can be deleted or corrected.

romaldus 7/7/2017



Hi at all,
I am using running totals in a car logbook solution and followed the tips given here. Works nicely.
Problem is, that the running totals for the total mileage only show the correct result, if all records are shown in the correct order.
If you change the order or want to print lets say only May from a year´s logbook, you get nonsense values.
Is there a way to print partial reports with correct running totals without saving the totals to a field in the database? That is not only taking space, but problematic too, if records can be deleted or corrected.


Any screenshots?

mbintex author 7/8/2017

here you are.


Questions is how to print only June AND keeping the correct mileage (column Kilometerstand) which is lively calculated as a running total.

romaldus 7/8/2017

Have you tried using filter panel? (filter by month)



mbintex author 7/8/2017

That is not the problem - surely you can easily filter, select and search for records.
But then the running total is newly calculated based on the resulting records and gets completely wrong.

romaldus 7/8/2017



That is not the problem - surely you can easily filter, select and search for records.
But then the running total is newly calculated based on the resulting records and gets completely wrong.


It depends on how you calculate the "kilometerstand" field.

Are the "kilometerstand" data stored physically in the database or is the result of query?
To avoid miscalculations in the report page, i think the easiest solution is to calculate the "kilometerstand" value when add record and than store the data physically in the database.
In phprunner before record added / beffore record updated event (for example: your table name is "mileage":



$sql = "SELECT MAX(kilometerstand) AS stand FROM mileage";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);
$values['kilometerstand'] = $values['kilometer'] + $data["stand"];


Using this solution, you can perform month filter in report page

mbintex author 7/9/2017

Thanks Ronaldus,
I wanted to avoid that, because you get other complications, For example if users delete/revert an entry or have to correct a mileage of an entry inbetween. Plus I have different cars with different mileages in one table.
Currently I calculate the Kilometerstand field with a custom field

if ($data["Fahrenbuch"]==$_SESSION["Fahrenbuch"])

{

$_SESSION["Kilometerstand"] += $data["Kilometer"];

$value = $_SESSION["Kilometerstand"];

}

else

{

$_SESSION["Summe"] = $data["Kilometer"];

$value = $_SESSION["Kilometerstand"];

}
$_SESSION["Fahrenbuch"]=$data["Fahrenbuch"];


which starts a new counting when the car changes in the list.

admin 7/11/2017

The only reliable way to implement multi=page running total is to use the approach suggested in this article (MySQL only):

https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql
Unfortunately PHPRunner doesn't understand MySQL variables and your best bet is to create a view in MySQL on the top of such a SQL query and then use this view as a datasource in PHPRunner.

mbintex author 7/12/2017

Yes, I already had this in MySQL Workbench:

set @summe:=0;

set @kassenbuch:="";

SELECT

`ID`,

@rechenbetrag :=if(Art='Einnahme', Betrag, Betrag*-1) AS `Rechenbetrag`,

case

when @kassenbuch=Kassenbuch then

@summe:=@summe+@rechenbetrag

else

@summe:=@rechenbetrag

end as summe,
@kassenbuch:=`Kassenbuch`,

`Art`,

`Kategorie`,

`Konto`,

`Gegenkonto`,

`Datum`,

`Betrag`,

`Betreff`
FROM `Buchungen`

ORDER BY Kassenbuch, `Datum`


Indeed it´s sad that you can´t set variables in SQL Queries within PHPRunner - this would be a feature wish :-)