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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s