http://www.sqlservercentral.com/blogs/dknight/2012/01/19/session-code-extending-reporting-services-with-custom-code/

Printed 2014/08/29 10:45AM

Session Code – Extending Reporting Services with Custom Code

2012/01/19

This is my wish list of things I’d like to cover in todays session at 11 AM EST https://www1.gotomeeting.com/register/935071481 but seeing that I know I’ll only get to a third of these thought I would post these for everyone.  If you’re seeing this late the sessions are recorded and can be viewed on www.pragmaticworks.com/Resources/webinars.

Next week look for a while paper I’ll be releasing on the same topic but of course with more details and screenshots.  This post is purely to support the webinar Smile.

Embedded Code Examples

1.  Change Background Color based on Data

Public Shared Function SetColor(ByVal Value As Integer) As String

    SetColor = "Green"

    If Value < 500 Then  
        SetColor = "Maroon"

    ElseIf Value < 1000 Then  
        SetColor = "Yellow"

    ElseIf Value < 2000 Then  
        SetColor = "Orange"   
    End If

  End Function   

Using Code in SSRS Expression

=Code.SetColor(Fields!OrderQuantity.Value)   

2.  UPPERCASE all Text FUNCTION

   Function UpperData(ByVal cField As String) As String   
      Return cField.ToUpper()   
   End Function   

Using Code in SSRS Expression

=Code.UpperData(Fields!CompanyName.Value)   

3.  Alternating Row color FUNCTION (From Reporting Services Recipe book)

Private bOddRow As Boolean   
Function AlternateColor(ByVal OddColor As String, _   
         ByVal EvenColor As String, ByVal Toggle As Boolean) As String   
    If Toggle Then bOddRow = Not bOddRow   
    If bOddRow Then   
        Return OddColor   
    Else   
        Return EvenColor   
    End If   
End Function   

Using Code in SSRS Expression

=Code.AlternateColor("AliceBlue", "White", True)   

4.  Comma Separated Parameter Value FUNCTION

Public Function ShowParmValues(ByVal parm as Parameter) as string   
   Dim s as String

      For i as integer = 0 to parm.Count-1  
         s &= CStr(parm.value(i)) & IIF( i < parm.Count-1, ", ","")   
      Next   
  Return s   
End Function   

Using Code in SSRS Expression

=Code.ShowParmValues(Parameters!Department)   

5.  Standardizing Phone Numbers (From Reporting Services Recipe book)      

Function PhoneFormat(PhoneNumber As String) As String   
    Select Case PhoneNumber.Length   
    Case 7   
        Return PhoneNumber.Substring(0,3) & "-" & PhoneNumber.Substring(3,4)   
    Case 10   
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(3,3)&"-" & PhoneNumber.Substring(6,4)   
    Case 12   
    Return "(" & PhoneNumber.Substring(0,3) &")" & PhoneNumber.Substring(4,3)&"-" & PhoneNumber.Substring(8,4)   
    Case Else   
    Return PhoneNumber   
    End Select   
End Function   

Using Code in SSRS Expression

=Code.PhoneFormat(Fields!Phone.Value)

6. Calculating Age

Public Shared Function CalculateAge(ByVal BirthDate As Date) As Integer
Return DateDiff(“yyyy”, BirthDate, DateTime.Now())
End Function

Using Code in SSRS Expression

=Code.CalculateAge(Fields!BirthDate.Value)

External Assembly Example

1. Standardizing Phone Numbers

Public Class ReportFormat  
    Public Shared Function PhoneFormat(ByVal PhoneNumber As String) As String   
        Select Case PhoneNumber.Length   
            Case 7   
                Return PhoneNumber.Substring(0, 3) & "-" & PhoneNumber.Substring(3, 4)   
            Case 10   
                Return "(" & PhoneNumber.Substring(0, 3) & ")" & PhoneNumber.Substring(3, 3) & "-" & PhoneNumber.Substring(6, 4)   
            Case 12   
                Return "(" & PhoneNumber.Substring(0, 3) & ")" & PhoneNumber.Substring(4, 3) & "-" & PhoneNumber.Substring(8, 4)   
            Case Else   
                Return PhoneNumber   
        End Select   
    End Function   
End Class   

Steps to create assembly

2. Change Background Color based on Data
Public Class ValueFormat   
    Public Shared Function SetColor(ByVal Value As Integer) As String

        SetColor = "Green"

        If Value < 500 Then  
            SetColor = "Maroon"

        ElseIf Value < 1000 Then  
            SetColor = "Yellow"

        ElseIf Value < 2000 Then  
            SetColor = "Orange"   
        End If

    End Function  
End Class

Using Code in SSRS Expression
=ColorFormat.ValueFormat.SetColor(SUM(Fields!OrderQuantity.Value))


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.