Hi guys.
Been a while since I posted here. Since you all have been so helpful to me in the past with issues, I figured I'd come back and throw another one at ya'.
So, I have two datatables. One is feeding another. Strange, I know.
The first datatable is being pulled from a stored procedure, here:
Code:
DataTable dataTable = new DataTable();
//populate datatable
dataTable = db.StoredProcedures.FlashDataTable(strFyStartDate, strFyEndDate, strCntrStartDate, strCntrEndDate);
From there, I'm setting values of some variables needed to populate my second datatable - (then creating my second data table)
Code:
foreach (DataRow dRow in dataTable.Rows)
{
#region SetVariableValues
//Sets the values to the given variables in "dataTable"
string strProfName = dataTable.Rows[0]["Event Profile Contact"].ToString();
string strRebId = dataTable.Rows[0]["Rebate Id"].ToString();
string strPromoName = dataTable.Rows[0]["Promo Name"].ToString();
DateTime strPromoStartDate = Convert.ToDateTime(dataTable.Rows[0]["Promo Start Date"]);
DateTime strPromoEndDate = Convert.ToDateTime(dataTable.Rows[0]["Promo End Date"]);
DateTime strPromoFinalDate = Convert.ToDateTime(dataTable.Rows[0]["Promo Final RDMT Date"]);
DateTime strCntrSetDate = Convert.ToDateTime(dataTable.Rows[0]["Counter Set Date"]);
int iTotalRebAmount = Convert.ToInt32(dataTable.Rows[0]["Total Rebate Amount"]);
//Used in calculation within second foreach Loop
int iBgtFy2012 = Convert.ToInt32(dataTable.Rows[0]["BUDGET FY'2012 or Accrued from FY'2010"]);
int iBgtAmt = Convert.ToInt32(dataTable.Rows[0]["Budget Amount"]);
#endregion
//create a secondary datatable
DataTable dt = new DataTable();
dt.Columns.Add("EventProfileContact");
dt.Columns.Add("RebateId");
dt.Columns.Add("Promo Name");
dt.Columns.Add("Start Date");
dt.Columns.Add("End Date");
dt.Columns.Add("Final Date");
dt.Columns.Add("W # Of RBTs");
dt.Columns.Add("W TTL DOLLARS");
dt.Columns.Add("W AVG $ PER RBT");
dt.Columns.Add("FY # Of RBTs");
dt.Columns.Add("FY AVG $ PER RBT");
dt.Columns.Add("FY TTL DOLLARS");
dt.Columns.Add("Budget Amount");
dt.Columns.Add("Variance TO Budget");
dt.Columns.Add("Ending Budget FY2011");
DataRow dr = dt.NewRow();
string newContact = "";
Then, I'm creating the datarows and will be populating them with the variables. After that, I'm TRYING to make some calculations... and this is where I'm having issues.
Code:
if (newContact != strProfName)
{
dr["EventProfileContact"] = strProfName;
dr["RebateId"] = strRebId;
dr["Promo Name"] = strPromoName;
dr["Start Date"] = Convert.ToDateTime(strPromoStartDate).ToString("d");
dr["End Date"] = Convert.ToDateTime(strPromoEndDate).ToString("d");
dr["Final Date"] = Convert.ToDateTime(strPromoFinalDate).ToString("d");
if (Convert.ToDateTime(strCntrStartDate) <= pastDate)
{
//wtd counts
dr["W # Of RBTs"] = dt.Compute("COUNT(" + strRebId + ")", "");
dr["W TTL DOLLARS"] = dt.Compute("COUNT(" + iTotalRebAmount + ")", "");
dr["W AVG $ PER RBT"] = dt.Compute("Sum(" + iTotalRebAmount + ") / COUNT(" + strRebId + ")", null);
}
if (Convert.ToDateTime(strFyStartDate) >= fiscalYearStartDate)
{
//fiscal ytd counts
dr["FY # Of RBTs"] = dt.Compute("COUNT(" + strRebId + ")", null);
dr["FY AVG $ PER RBT"] = dt.Compute("Sum(" + iTotalRebAmount + ") / COUNT(" + strRebId + ")", null);
dr["FY TTL DOLLARS"] = dt.Compute("Sum(" + iTotalRebAmount + ")", null);
//fiscal ytd budget numbers
if (iBgtFy2012 == null || iBgtFy2012 == iBgtAmt)
{
dr["Budget Amount"] = iBgtAmt;
dr["Variance TO Budget"] = dt.Compute("SUM(" + iBgtAmt + "-" + iTotalRebAmount + ")", null);
dr["Ending Budget FY2011"] = iBgtAmt;
}
else
{
dr["Budget Amount"] = iBgtFy2012;
dr["Variance TO Budget"] = dt.Compute("SUM(" + iBgtFy2012 + "-" + iTotalRebAmount + ")", null);
dr["Ending Budget FY2011"] = iBgtFy2012;
}
}
}
dt.Rows.Add(dr);
newContact = strProfName;
reportView.DataSource = dt;
reportView.DataBind();
}
I can't get the calculations to work... what's happening is it's reaching that first calculation and it thinks that the value of the variable is the name of the column, and it can't find that column.
What I want to do is calculate the count of that particular value.
I'm kind of stuck and I need some assistance with the proper syntax of the calculations.
Thoughts?
