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

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 .

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

• Use above code in Class file
• Set Strong Name -Project Properties – Signing – Strong Name
• Set Security – AssemblyInfo.vb
  • Imports System.Security
  • <Assembly: AllowPartiallyTrustedCallers()>
• Build project and put the .dll in:
  • Global Assembly Cache (GAC) C:\Windows\assembly
  • C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
• Place dll reference in the report
• Use this in the report     
  =ReportAssembly.ReportFormat.PhoneFormat(Fields!Phone.Value)

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))