Loop thru dataset for running total

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've run into this problem twice now and haven't quite figured out how to
approach it properly. I'm using ASP.NET 1.1 and C# to develop a reporting
package. Some of these reports are showing a daily total amount. There has
been a request to add a running total next to the daily amount. For example:

1/1/2006 $41 $41
1/2/2006 $23 $64
1/3/2006 $57 $121

Since I know the exact column I'm using to do the calculation, I assume I
just need to loop thru the rows using a foreach row loop. But I'm having
trouble figuring out how to do the calculation and then adding the new column
with the updated value. Every example I've seen with a foreach loop with a
calculation uses the Expression property which I don't believe I can use.
Any help would be appreciated.
 
One thing you can do that's pretty simple is to create a DataColumn and set
its Autoincrement property to true. Now, create an expression column and do
Sum(ValueColumnName), AutoIncrementColumnName < = AutoIncrementColumn - this
will sum all of the values for the columns previous to the current one.
This should give you the running total and you don't have to loop through it
each time. Plus, if you change any of the values in your value column, then
the totals will automatically update themselves
 
I'm sorry Coolburn, I got my syntax mixed up. I was thinking about
DataTable.Compute with the fitler, but actually i think you're going o thave
to loop through everything.

here's an example
DataColumn dc1 = new DataColumn("ValueColumn", typeof(float));

DataColumn dcAuto = new DataColumn("AutoIncrementColumn",
typeof(System.Int32));

dcAuto.AutoIncrement = true;

DataColumn dcExpression = new DataColumn("ExpressionColumn", typeof(float));

///dcExpression.Expression = "SUM(ValueColumn)";

dt.Columns.Add(dc1);

dt.Columns.Add(dcAuto);

dt.Columns.Add(dcExpression);

DataRow dro = dt.NewRow();

dro["ValueColumn"] = 10;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro["ValueColumn"] = 20;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro["ValueColumn"] = 30;

dt.Rows.Add(dro);

dgv.DataSource = dt;

float total = 0;

foreach (DataRow drow in dt.Rows)

{


total += (float)drow["ValueColumn"];

drow["ExpressionColumn"] = total;

}
 
That was exactly what I was looking for. And I knew there was a way to do it
but I was overcomplicating it.

In case anyone in the future wanted to see my code, it is below. I had
previously loaded a dataset (ds) from a DB2 database and using what you gave
me, I got the results. Thank you very much.

DataTable dt=new DataTable();
dt=ds.Tables[0].Copy();

DataColumn dcExp = new DataColumn("BalanceDue",typeof(decimal));
dt.Columns.Add(dcExp);

decimal total=0;
foreach (DataRow drow in dt.Rows)
{
total += (decimal)drow["AmountOwed"];
drow["BalanceDue"] = total;
}

dg.DataSource=dt;
dg.DataBind();

W.G. Ryan - MVP said:
I'm sorry Coolburn, I got my syntax mixed up. I was thinking about
DataTable.Compute with the fitler, but actually i think you're going o thave
to loop through everything.

here's an example
DataColumn dc1 = new DataColumn("ValueColumn", typeof(float));

DataColumn dcAuto = new DataColumn("AutoIncrementColumn",
typeof(System.Int32));

dcAuto.AutoIncrement = true;

DataColumn dcExpression = new DataColumn("ExpressionColumn", typeof(float));

///dcExpression.Expression = "SUM(ValueColumn)";

dt.Columns.Add(dc1);

dt.Columns.Add(dcAuto);

dt.Columns.Add(dcExpression);

DataRow dro = dt.NewRow();

dro["ValueColumn"] = 10;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro["ValueColumn"] = 20;

dt.Rows.Add(dro);

dro = dt.NewRow();

dro["ValueColumn"] = 30;

dt.Rows.Add(dro);

dgv.DataSource = dt;

float total = 0;

foreach (DataRow drow in dt.Rows)

{


total += (float)drow["ValueColumn"];

drow["ExpressionColumn"] = total;

}

coolburn said:
I've run into this problem twice now and haven't quite figured out how to
approach it properly. I'm using ASP.NET 1.1 and C# to develop a reporting
package. Some of these reports are showing a daily total amount. There
has
been a request to add a running total next to the daily amount. For
example:

1/1/2006 $41 $41
1/2/2006 $23 $64
1/3/2006 $57 $121

Since I know the exact column I'm using to do the calculation, I assume I
just need to loop thru the rows using a foreach row loop. But I'm having
trouble figuring out how to do the calculation and then adding the new
column
with the updated value. Every example I've seen with a foreach loop with
a
calculation uses the Expression property which I don't believe I can use.
Any help would be appreciated.
Click to expand...
Click to expand...
 
Back
Top