calculation within datatable question

[Mike]

I'm creating a datatable (see below) that is going through each row of my
dataset and add new rows per each column created. Is there a way to store
the GrossSales figures so I can use
those numbers in for a calculation within the Expenses row?

My data for the dsSales dataset like this:

year totalGross SalesmanLastName
2005 45,000 Smith
2004 25,000 Jensen
2003 12,000 A. Smith

My data for dsExpenses is coming out like this:
year Expense SalesmanLastName
2005 4,000 Smith
2004 1500 Jensen
2003 12,000 A. Smith

is there a way to store the TotalGross and use that for a calculation within
the foreach {} loop for the dsExpense so I can have the net calculated? I
have to seperate dataset due to the data is coming from 2 seperate tables
within the database

public DataTable Results()
{
dtFigures = new DataTable();
dsSales = GetSalesValues();

foreach (DataTable dt in dsSales.Tables)
{
foreach (DataRow dr in dt.Rows)
{
dr = dtFigures.NewRow();
dr["SubHeader"] = "Sales Figures"
dr["Year"] = dr["Year"].ToString();
dtFigures.Rows.Add(dr);

dr = dtFigures.NewRow();
GrossSales = Convert.ToDecimal(dr["totalGross"]) / 1000;

dr["MetricType"] = "Gross Sales
dr["Year"] = dr["Year"].ToString();
dr["Year One"] =
Convert.ToInt32(GrossSales).ToString("c0");
dtFigures.Rows.Add(dr);
}
}

dsExpenses = AllExpenses();
foreach (DataTable dt in dsExpenses.Tables)
{
foreach (DataRow dr in dt.Rows)
{
dr = dtFigures.NewRow();
dr["SubHeader"] = "Expenses";
dr["Year"] = dr["Year"].ToString();
dtFigures.Rows.Add(dr);

dr = dtFigures.NewRow();
dr["MetricType"] = "Year to date expenses";
dr["Year"] = dr["Year"].ToString();
dr["Year One Expenses"] = Convert.ToDecimal(dr["Expense"])
/ 1000;
dtFigures.Rows.Add(dr);
}
}
}

 

[Mark Rae [MVP]]

is there a way to store the TotalGross and use that for a calculation
within the foreach {} loop for the dsExpense so I can have the net
calculated?

Can't you just declare a module-level variable and increment it as
required...?

 

[Mike]

I have something like that and its only storing the last value, I need all
values for each year

 

[Mark Rae [MVP]]

I have something like that and its only storing the last value, I need all
values for each year

Are you overwriting the class-level variable each time rather than
incrementing it...?

 

[Mike]

I was trying to increment it but it was only storing the last years value.
I was trying this on Saturday actaully and I deleted the code, so I can't
show you what I had either to help out.

 

[Mark Rae [MVP]]

I was trying to increment it but it was only storing the last year's
value.
I was trying this on Saturday actually and I deleted the code, so I can't
show you what I had either to help out.

If you need to store individual values per year, then you could use a
generic e.g.

Dictionary<int, decimal> dicTotals = new Dictionary<int, decimal>();

Then, within your foreach loop:

if (!dicTotals.Contains(2004)
{
dicTotals.Add(2004, MyRunningTotal);
}
else
{
dicTotals[2004] += MyRunningTotal;
}

N.B. the above is probably not particularly efficient, but should work...

 

[Mike]

I had something like that and it was only storing the last value.

I was trying to increment it but it was only storing the last year's
value.
I was trying this on Saturday actually and I deleted the code, so I can't
show you what I had either to help out.

If you need to store individual values per year, then you could use a
generic e.g.

Dictionary<int, decimal> dicTotals = new Dictionary<int, decimal>();

Then, within your foreach loop:

if (!dicTotals.Contains(2004)
{
dicTotals.Add(2004, MyRunningTotal);
}
else
{
dicTotals[2004] += MyRunningTotal;
}

N.B. the above is probably not particularly efficient, but should work...