VS 2013 SSRS Excel like conditional formattting-VBForums
Results 1 to 4 of 4

Thread: SSRS Excel like conditional formattting

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    9

    SSRS Excel like conditional formattting

    I have used the following public function (thanks to David Lean) to show a Excel like Red Yellow Green color scale to a text box value based on a minimum and maximum value.

    Code:
           Public Function ColorRYG(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As String
        'Example: =code.ColorBack(expression, Max(expression), Min(expression), 0)
        '=code.colorback( Fields!Sales.Value,max( Fields!Sales.Value),min( Fields!Sales.Value),0)
        'Find Largest Range
        Dim decRange As Decimal
        Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)
        Dim decNegRange As Decimal = Math.Abs(MaxNegative - Neutral)
    
        decRange = IIf(decPosRange > decNegRange, decPosRange, decNegRange)
    
        'Force color into Max-Min Range. Important if you want to Clip the color display to a subset of the data range. 
        Value = Switch((Value > MaxPositive), MaxPositive, Value < MaxNegative, MaxNegative, True, Value) 
        'Find Delta required to change color by 1/255th of a shade
        Dim decColorInc As Decimal = 255 / decRange
    
        'Find appropriate color shade
        Dim iColor As Integer = CInt(Math.Round((Value - Neutral) * decColorInc))
    
        'Return Appropriate +ve or -ve color
        Dim strColor As String
        If iColor >= 0 Then
            'Green
            iColor = 255 - iColor 'Thus 0 = White & 255 = Green
            strColor = "#" & iColor.ToString("X2") & "FF00"
        Else
            'Red
            iColor = iColor + 255  'NB iColour is -ve;  -1 - -255 
            strColor = "#FF" & Math.Abs(iColor).ToString("X2") & "00"
        End If
        Return strColor
    End Function

    I then use the following expression in the textbox :

    =code. ColorRYG(Fields!BlokkeklaarPersentGelewerKultAliasnaam.Value,max(Fields!BlokkeklaarmaksimumKultAlias naam.Value),min(Fields!BlokkeklaarminimumKultAliasnaam.Value),0)

    However when I run my VB.net application I only get the hex value of the color and not the actual color in the text box.

    Any help would be much appreciated.
    Regards

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    21,536

    Re: SSRS Excel like conditional formattting

    Return strColor???

    Code:
    Public Function ColorRYG(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal MaxNegative As Decimal, ByVal Neutral As Decimal) As Color
    ...
    ...
    ...
        Return Color.fromARGB(strColor)
    End Function

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    9

    Re: SSRS Excel like conditional formattting

    Thank you .paul

    How do I declare Color. I get the following error when I change the code as you suggested : 'Color' is not declared.

    I have just copied and pasted the code from : http://blogs.msdn.com/davidlean/arch...stom-code.aspx

    I was hoping this would work.

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    21,536

    Re: SSRS Excel like conditional formattting

    Imports System.Drawing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.