## Convert String to Int or Decimal Then Average

 Author Message dan.gaze Grasshopper Group: General Forum Members Points: 12 Visits: 80 Hello all, hope you are well!Just after a bit of assistance with an expression that's turning into a bit of a head scratcher...I have a string column containing mostly numeric values, but also 'Unknown' values too. In an expression I am using an IIf statement to convert the 'Unknown' values to NULL, whilst leaving the numeric values as they are. Surrounding the IIf statement I then need to Average the result set. Which means the string column requires converting to either int or decimal in order to do the average.The problem is, when converting string to int/decimal, the NULL values I have defined are then converted to 0. Which throws the average out.Is there any way to convert the string column to allow averaging, whilst leaving the NULL's intact?Expression is as follows: =AVG(CDec(IIf(Fields!SCORE.Value = "Unknown", Nothing, Fields!SCORE.Value)))I'm using SQL Server 2012.Cheers! Daniel Bowlin SSCrazy Group: General Forum Members Points: 2954 Visits: 2629 I would probably use AVG and the OVER clause in TSQL to work this one out.http://technet.microsoft.com/en-us/library/ms189461.aspxLook at the examples under "Using the OVER clause with aggregate functions" lynns SSC-Enthusiastic Group: General Forum Members Points: 131 Visits: 310 I would try using the decimal conversion on only the numeric values.=AVG(IIf(Fields!SCORE.Value = "Unknown", Nothing, CDec(Fields!SCORE.Value)))I think converting "Nothing" to a decimal creates the zeroes that are causing the problems. dan.gaze Grasshopper Group: General Forum Members Points: 12 Visits: 80 Daniel Bowlin (10/17/2013)I would probably use AVG and the OVER clause in TSQL to work this one out.http://technet.microsoft.com/en-us/library/ms189461.aspxLook at the examples under "Using the OVER clause with aggregate functions"Hi Daniel, my preference was to handle the conversion in the SQL statement itself, but unfortunately I can't as the dataset column is used elsewhere. I could possibly create an additional column but I wanted to see if there was a simpler solution first (more for future use than anything!) dan.gaze Grasshopper Group: General Forum Members Points: 12 Visits: 80 lynns (10/17/2013)I would try using the decimal conversion on only the numeric values.=AVG(IIf(Fields!SCORE.Value = "Unknown", Nothing, CDec(Fields!SCORE.Value)))I think converting "Nothing" to a decimal creates the zeroes that are causing the problems.Thanks lynns, I thought that might have been it then...but unfortunately I'm getting '#Error' when applying the conversion that way. lynns SSC-Enthusiastic Group: General Forum Members Points: 131 Visits: 310 This was interesting. Regardless of where I introduced a conversion function within the expression it created an error for the non-numeric entries. This is a little different approach, but I think it will work.=SUM(CDec(IIF(IsNumeric(Fields!SCORE.Value),Fields!SCORE.Value,0)))/SUM(IIF(IsNumeric(Fields!SCORE.Value),1,0))