# need min(value) greater than zero

• ### Question

• I need to return the first value greater than zero as my min(value). Is that possible? I'm using 2012 SQL and SSRS.

I need to give a summary at the end of my report showing the min() and max() values of a field for that row.

Thank you,

Luke

Monday, September 23, 2013 5:39 PM

• Hi Lukegr,

You can use the expresion to get the min value from the values which greater than zero:
="Minimal value:" & min(IIF(Fields!col.Value<0,abs(Fields!col.Value),Fields!col.Value),"DataSet1")

If you want to get the first value greater than zero, then we need to use costom code to achieve it. We can add those values to array, negative value to Negative array, positive value to Positive array, and then retrun Positive(0) to get the first value greater than zero, the custome code looks like:

```Dim Positive As System.Collections.ArrayList=New System.Collections.ArrayList()
Dim Negative As System.Collections.ArrayList=New System.Collections.ArrayList()
Function SetValue(ByVal value As Integer) As Integer
If value>0 then
Else
End If
return value
End Function
Function GetMin()
If Positive.Count=0 Then
return "Null"
Else
return Positive(0)
End If
End Function```

Then use the expression to set value:
=Code.SetValue(Fields!col.Value)

And use the expression to get Min value:
=Code.GetMin()

The report looks like:

Regards,
Charlie Liao

Charlie Liao
TechNet Community Support

• Marked as answer by Thursday, October 3, 2013 4:34 AM
Tuesday, September 24, 2013 3:25 AM
• Is that possible to solve this by T-SQL

>>>I need to return the first value greater than zero as my min(value).

SELECT TOP 1 val AS min_val FROM tbl WHERE col >0

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

• Marked as answer by Thursday, October 3, 2013 4:34 AM
Tuesday, September 24, 2013 5:36 AM

### All replies

• Hi Lukegr,

You can use the expresion to get the min value from the values which greater than zero:
="Minimal value:" & min(IIF(Fields!col.Value<0,abs(Fields!col.Value),Fields!col.Value),"DataSet1")

If you want to get the first value greater than zero, then we need to use costom code to achieve it. We can add those values to array, negative value to Negative array, positive value to Positive array, and then retrun Positive(0) to get the first value greater than zero, the custome code looks like:

```Dim Positive As System.Collections.ArrayList=New System.Collections.ArrayList()
Dim Negative As System.Collections.ArrayList=New System.Collections.ArrayList()
Function SetValue(ByVal value As Integer) As Integer
If value>0 then
Else
End If
return value
End Function
Function GetMin()
If Positive.Count=0 Then
return "Null"
Else
return Positive(0)
End If
End Function```

Then use the expression to set value:
=Code.SetValue(Fields!col.Value)

And use the expression to get Min value:
=Code.GetMin()

The report looks like:

Regards,
Charlie Liao

Charlie Liao
TechNet Community Support

• Marked as answer by Thursday, October 3, 2013 4:34 AM
Tuesday, September 24, 2013 3:25 AM
• Is that possible to solve this by T-SQL

>>>I need to return the first value greater than zero as my min(value).

SELECT TOP 1 val AS min_val FROM tbl WHERE col >0

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

• Marked as answer by Thursday, October 3, 2013 4:34 AM
Tuesday, September 24, 2013 5:36 AM
• I had the same situation. My records had the potential to be zero, but I needed a MIN() value for the non-zero records. I solved this with:

```IIF(Min(IIF(Fields!SundayTotal.Value = 0, NOTHING, Fields!SundayTotal.Value)) = Nothing,
0,
Min(IIF(Fields!SundayTotal.Value = 0, NOTHING, Fields!SundayTotal.Value)))```

The key piece is returning "NOTHING" if the value is zero, this causes the MIN() function to ignore it. I wrapped the whole thing in an IIF() so that if there were no records at all, it will still return zero rather than being empty.

• Proposed as answer by Friday, February 21, 2014 3:13 PM
Friday, February 14, 2014 6:03 PM
• GMRDev2,

I've been trying different expressions and filters for over an hour now with no luck. I searched the net until I found your comment. The field is in a matrix and the SQL case statement is showing a datediff as an integer and zeros when NULL. In SSRS I needed a minimum of that field when not zero. I plugged your expression in and it worked perfectly. THANK YOU!

-Chris

• Edited by Friday, February 21, 2014 3:14 PM Gave credit to the person I'm replying to
Friday, February 21, 2014 3:12 PM
• have removed both null and dummy value from my report.

=IIf(FORMAT(CDate(IIf(CSTR(Fields!EMP_REVISED_END_DATE.Value)="","1900-01-01",Fields!EMP_REVISED_END_DATE.Value)),"dd-MMM-yyyy")="01-Jan-1900","",

FORMAT(CDate(IIf(CSTR(Fields!EMP_REVISED_END_DATE.Value)="","1900-01-01",Fields!EMP_REVISED_END_DATE.Value)),"dd-MMM-yyyy"))

Tuesday, September 27, 2016 5:54 PM