locked
need min(value) greater than zero RRS feed

  • 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

Answers

  • 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  
          Positive.Add(value)
       Else
          Negative.Add(value)
       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:

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao 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 Charlie Liao 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  
          Positive.Add(value)
       Else
          Negative.Add(value)
       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:

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao 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 Charlie Liao 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 CReynolds35 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 CReynolds35 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