This is a migrated thread and some comments may be shown as answers.

calculated column with if condition

5 Answers 472 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Felice
Top achievements
Rank 1
Felice asked on 20 Jun 2014, 11:41 AM
I have a radgrid with 4 columns:
Quantity, Cost, SpecialCost, TotalCost
The first 3 columns are data bound, the 4td column is a Calculated column.
I need to calculate the value of the TotalCost based on Quantity*Cost, this is easy:

DataFileds: Quantity, Cost
Expression: {0}*{1}

but if the SpecialCost cell contains a value then the calculation should be done using SpecialCost*Quantity.
As far as I know, the Expression editor does not take logical functions otherwise I could simply use an if statement to check if SpecialCost contains a value and select it.

How can I achieve the above? 

5 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 20 Jun 2014, 12:15 PM
Hi Felice,

You can set the text from code behind when you want SpecialCost*Quantity as below.

ASPX:
<telerik:GridCalculatedColumn HeaderText="Total Cost" UniqueName="TotalCost" DataType="System.Double" DataFields="Quantity, Cost" Expression="{0}*{1}" />

C#:
protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
{
  if (e.Item is GridDataItem)
  {
    GridDataItem dataItem = e.Item as GridDataItem;      
    if (dataItem["SpecialCost"].Text == "Some Value")
    {
      //Caluculate and set the text of TotalCost
     dataItem["TotalCost"].Text = ((Convert.ToInt16(dataItem["SpecialCost"].Text)) * (Convert.ToDouble(dataItem["Quantity"].Text))).ToString();
    }
  }
}

Thanks,
Princy
0
Felice
Top achievements
Rank 1
answered on 20 Jun 2014, 03:29 PM
Hi Princy,
since with Int16 I get an error about the size of the value, I changed it to Double like this:
if (e.Item is GridDataItem)
       {
           GridDataItem dataItem = e.Item as GridDataItem;
           if (dataItem["AlternCost"].Text !=null)
           {
               //Caluculate and set the text of TotalCost
               dataItem["TotCost"].Text = ((Convert.ToDouble(dataItem["AlternCost"].Text)) * (Convert.ToDouble(dataItem["Quantity"].Text))).ToString();
           }
       }
 But I am getting  another error: "format string not correct". Same problem with Int32.
What is wrong?
0
Princy
Top achievements
Rank 2
answered on 21 Jun 2014, 03:47 AM
Hi Felice,

Such an error comes when the DataTypes doesn't match. Please make sure that types in DB match with the conversions you applied. Convert the data into correct format where necessary.

Thanks,
Princy
0
Felice
Top achievements
Rank 1
answered on 21 Jun 2014, 07:00 AM
Hi Princy, initially I also thought the same.
 protected void RadGrid1_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
 { 
   if (e.Item is GridDataItem)
        {
            GridDataItem dataItem = e.Item as GridDataItem;
            if (string.IsNullOrEmpty(dataItem["AlternCost"].Text))
            {
                //Caluculate and set the text of TotalCost
                dataItem["TotCost"].Text = ((Convert.ToDouble(dataItem["AlternCost"].Text)) * (Convert.ToDouble(dataItem["Quantity"].Text))).ToString();
            }
            else
            {
                dataItem["TotCost"].Text = ((Convert.ToDouble(dataItem["Cost"].Text)) * (Convert.ToDouble(dataItem["Quantity"].Text))).ToString();


            }
        }
}
 
TotCost is a template column DataType: String and is not bound to the DB. This column only shows the value in the grid
Quantity is a bound column DataType: Double and is a float in the DB
Cost is a bound column DataType: Double and is a float in the DB
AlternCost is a bound column DataType: Double and is a float in the DB 

But still I get the exception "input string not correct" and I do not understand why.
If you can help further will be really appreciated.
Thanks a lot,
Felice
0
Felice
Top achievements
Rank 1
answered on 22 Jun 2014, 05:23 AM
Solved!

finally I did the following and I solved the problem:

#region Calculate Total Cost
  //Get the special cost. If the value does not exist, it means the special cost is 0
     double specialcost = ((cell.Text.Trim()) != " " && !String.IsNullOrWhiteSpace(cell.Text.Trim())) ? double.Parse(cell.Text.Trim()) : 0;
      double cost = ((dataBoundItem["Cost"].Text.Trim()) != " " && !String.IsNullOrWhiteSpace(dataBoundItem["Cost"].Text.Trim())) ? double.Parse(dataBoundItem["Cost"].Text.Trim()) : 0;
           
  //If the special cost is 0 than total cost = cost * quantity
   if (specialcost == 0)
          {
              dataBoundItem["TotalCost"].Text = (cost * (Convert.ToDouble(dataBoundItem["Quantity"].Text))).ToString();
          }
          else //If special cost exists than total cost = special cost * quantity
          {
              dataBoundItem["TotalCost"].Text = ((Convert.ToDouble(dataBoundItem["AlternCost"].Text)) * (Convert.ToDouble(dataBoundItem["Quantity"].Text))).ToString();
          }
          #endregion

Thanks to you Princy for the great support and for directing me to the right direction.
Kind regards
Felice
Tags
Grid
Asked by
Felice
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Felice
Top achievements
Rank 1
Share this question
or