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.