Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Session Code – Extending Reporting Services with Custom Code

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

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

Comments

Leave a comment on the original post [www.bidn.com, opens in a new window]

Loading comments...