Forums: Implementing Running Total In Phprunner - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Implementing Running Total In Phprunner

#1 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15773
  • Joined: 03-February 03

Posted 07 June 2013 - 08:58 PM

As Wikipedia says "running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total". It is also known as running sum or running balance.

Adding running total to one of list pages is really simple. Lets consider table Balance with fields ID and Amount.

1.Modify SQL query adding a calculated Total field:

SELECT
ID,
Amount,
0 as Total
from Balance


2. Events Editor, Balance table: BeforeProcessList event:

$_SESSION["Total"]=0;


We are going to accumulate the current total amount in session variable $_SESSION["Total"]. This variable needs to be reset when page is loaded.

3. 'Total' field in Visual Editor. Set 'View as' type to 'Custom' and use the following code:

$_SESSION["Total"] += $data["Amount"];
$value = $_SESSION["Total"];


This is it. Here is how it looks in live application.

Posted Image
Best regards,
Sergey Kornilov
0

#2 User is offline   Alain 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 31
  • Joined: 12-February 17

Posted 04 August 2017 - 11:07 AM

View Postadmin, on 07 June 2013 - 08:58 PM, said:

As Wikipedia says "running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total". It is also known as running sum or running balance.

Adding running total to one of list pages is really simple. Lets consider table Balance with fields ID and Amount.

1.Modify SQL query adding a calculated Total field:

SELECT
ID,
Amount,
0 as Total
from Balance


2. Events Editor, Balance table: BeforeProcessList event:

$_SESSION["Total"]=0;


We are going to accumulate the current total amount in session variable $_SESSION["Total"]. This variable needs to be reset when page is loaded.

3. 'Total' field in Visual Editor. Set 'View as' type to 'Custom' and use the following code:

$_SESSION["Total"] += $data["Amount"];
$value = $_SESSION["Total"];


This is it. Here is how it looks in live application.

Posted Image

Great this is wonderfull, How about if it is grouped by column like Customer for example?
0

#3 User is online   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 392
  • Joined: 26-April 08

Posted 09 January 2018 - 12:14 AM

This just came in very handy for a project I am doing. Does anyone know how I can format the total field to currency? I am using C#.
I tried this on View As Custom, but it doesn't make any change.
XSession.Session["Total"] += data["Budgeted"].ToString();
value = String.Format("{0:C}", XSession.Session["Total"]);

Thanks!!
-jerry
0

#4 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15773
  • Joined: 03-February 03

Posted 16 January 2018 - 11:49 PM

Jerry,

I just double checked in ASPRunner.NET, your code works with both integer and floating point values. What results are you getting?
Best regards,
Sergey Kornilov
0

#5 User is online   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 392
  • Joined: 26-April 08

Posted 17 January 2018 - 12:05 AM

View Postadmin, on 16 January 2018 - 06:49 PM, said:

Jerry,

I just double checked in ASPRunner.NET, your code works with both integer and floating point values. What results are you getting?


I am using floats in my sql server database. I would love to be able to display the running total as currency. But no matter what I try, I end up with this:

Posted Image

Thanks
-jerry
0

#6 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15773
  • Joined: 03-February 03

Posted 17 January 2018 - 07:36 PM

Jerry,

got it, I thought there were an issue with calculation. You need to convert it to float first.

XSession.Session["Total"] += data["Budgeted"].ToString();
value = String.Format("{0:C}", float.Parse(XSession.Session["Total"]));

Best regards,
Sergey Kornilov
0

#7 User is online   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 392
  • Joined: 26-April 08

Posted 17 January 2018 - 11:10 PM

View Postadmin, on 17 January 2018 - 02:36 PM, said:

Jerry,

got it, I thought there were an issue with calculation. You need to convert it to float first.

XSession.Session["Total"] += data["Budgeted"].ToString();
value = String.Format("{0:C}", float.Parse(XSession.Session["Total"]));


It's a thing of beauty. The app looks great now. Thank you so much.
-jerry
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic