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