Computing columns in dtagrids

[Coleen]

Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen

 

[Coleen]

For anyone looking at this, here is the solution thanks to my very good VB.Net Teacher whom I called in desperation - Thanks Jerry!!!

Dim the variable as Public. On PreRender use:

PublicVariableName = DatatabelName.Row(0).Cell(15).Value

I could not get the option of value after row or cell in anything I tried within my For/Next Loop. I can now use this Variable value to do my calculations for the percentage rate. Again - THANKS Jerry!
Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen

 

[Coleen]

Well, I spoke to soon...this works great if you just want to display the value, but if you want to use the value to perform another calculation, I get "Infinity" in the cell where I'm trying to use the grand total I get from the PreRender.. any other suggestions?

PLEASE? TIA
For anyone looking at this, here is the solution thanks to my very good VB.Net Teacher whom I called in desperation - Thanks Jerry!!!

Dim the variable as Public. On PreRender use:

PublicVariableName = DatatabelName.Row(0).Cell(15).Value

I could not get the option of value after row or cell in anything I tried within my For/Next Loop. I can now use this Variable value to do my calculations for the percentage rate. Again - THANKS Jerry!
Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen

 

[Coleen]

Now I'm back to square one (please see posts below this one)

What I first intended to do was to create another datagrid and dump my grand total into it, and call the value from the new datagrid (I'm calling it dtg_grand_total) and populate a public variable with the value from the grand total in the dtg_grand_total datagrid. I think this will work, but I'm not sure of the correct syntax to call a value from one datagrid to another. Any suggestions on this?

TIA

Coleen
Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen

 

[Alvin Bruney [MVP]]

This is EASILY done in Excel:

and it is EASIER in .net

in your itemdatabound event handler

if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
int total = Int32.Parse( ds.Tables[0].Compute("Sum(["+Column A+"])",String.Empty));
//calculate column b for each row
e.Item.Cells[2].Text = int32.Parse(e.Item.Cells[2].Text) / total;
}


once you understand what you are working with, you can compress it down into this verse

if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
e.Item.Cells[2].Text = int32.Parse(e.Item.Cells[2].Text) / Int32.Parse( ds.Tables[0].Compute("Sum(["+Column A+"])",String.Empty));

i'd recommend a catch block because int32.parse throws on empty fields or u really should use double.tryparse ...
--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen

 

[Coleen]

I'm sorry, but I don't understand the code you've posted...I should have mentioned that we are using VB.Net, so although I understand the principals of what you have here, I don't quite understand your syntax...

I'm not sure why you're using a listitem here, can you please explain? Since we are using a .Net datagrid to calculate the values for multiple columns (like an Excel Spreadsheet) Each column that I'm sending data to is a calculation from some of the preceding columns. I ended up creating a totally separate datagrid and getting the grand total value for my column, then doing the division of the column contents in the second datagrid with public variables to store the values for each row in the column that I have to divide the grand total by. If you can explain your code to me, to show how you are doing this in ONE datagrid, I'd be VERY Grateful!

Thank you!

Coleen

BTW, we are using Double types instead of Integer types, and I do have a pars.double statement to get my grand total value...

This is EASILY done in Excel:

and it is EASIER in .net

in your itemdatabound event handler

if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
int total = Int32.Parse( ds.Tables[0].Compute("Sum(["+Column A+"])",String.Empty));
//calculate column b for each row
e.Item.Cells[2].Text = int32.Parse(e.Item.Cells[2].Text) / total;
}


once you understand what you are working with, you can compress it down into this verse

if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
e.Item.Cells[2].Text = int32.Parse(e.Item.Cells[2].Text) / Int32.Parse( ds.Tables[0].Compute("Sum(["+Column A+"])",String.Empty));

i'd recommend a catch block because int32.parse throws on empty fields or u really should use double.tryparse ...
--
Regards,
Alvin Bruney
[ASP.NET MVP http://mvp.support.microsoft.com/default.aspx]
Got tidbits? Get it here... http://tinyurl.com/27cok
Hi all

I'm having a BIG problem with getting a grand total in a data grid. I'm sorry, this is sort-of a double-post, but I REALLY need some help (please?)

I need to get the grand total of a column in a data grid and use that value to divide the data in the SAME column by the grand total. I know I can get the value by doing a Compute - and have done so. Now I need to get the value from the Computed column back into a variable in order to divide the contents of that column by the grand total for a percentage rate. This is EASILY done in Excel:

Column A Column B
row 1 =Column A Row 1 /Column A Total
row 2 =Column A Row 1 /Column A Total
row 3 =Column A Row 1 /Column A Total

Total Column A

I can't get this to work using a computed column in a datagrid because I'm having trouble getting the value of the Total for Column A into a variable to use to do the calculation for Column B

I've tried:

dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_hh_avg)"))
where dt_stat_report_3b is my datatable, "Sum" is the computed column and Column_10_ld_act_125_gtr_fy_hh_avg is the name of the column I am totaling. I tried to get the value from "Sum" into a variable using:

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl(dt_stat_report_3b.Rows.Item(0).ToString) which gives an error that the Item does not exist. If I try Row(0).Item(15) I get the same type of error.

I've tried:
ld_pct_125_tot_gtr_fy_hh = ld_act_125_gtr_fy_hh_avg / CDbl(dt_stat_report_3b.Columns(15).ToString) which gives me an error that the column can't be converted into a double - it already is described as a double. I'm at my wits end! Can someone please help?

Thanks very much if you can!

Coleen