Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert String to Int or Decimal Then Average Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 17, 2013 3:00 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, May 21, 2015 2:30 AM 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!
Post #1505539
 Posted Thursday, October 17, 2013 7:59 AM
 SSCrazy Group: General Forum Members Last Login: Saturday, September 17, 2016 8:01 AM Points: 2,834, Visits: 2,629
 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"
Post #1505723
 Posted Thursday, October 17, 2013 8:13 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, December 1, 2016 2:26 PM Points: 131, Visits: 309
 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.
Post #1505728
 Posted Thursday, October 17, 2013 10:14 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, May 21, 2015 2:30 AM 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!)
Post #1505799
 Posted Thursday, October 17, 2013 10:15 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, May 21, 2015 2:30 AM 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.
Post #1505801
 Posted Sunday, October 20, 2013 12:21 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, December 1, 2016 2:26 PM Points: 131, Visits: 309
 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))
Post #1506493

 Permissions