Advanced Report Design - Professional SQL Server 2005 Reporting Services - page 250

seems apropos given that Windows services — including the file and directory systems, web server, and
database transformation services — may all be scripted and automated using Visual Basic code.
When Reporting Services was still in beta test phase, I was asked to make a presentation for the .NET
Programmers' User Group at the Microsoft campus. When I announced that Reporting Services supports
only Visual Basic embedded code, half the group was nearly transformed into a lynch mob — and I was
looking for an exit. Why was VB chosen over C#? Was this an effort to “dumb down” or simplify report
programming? Perhaps VB is the “lowest-common denominator” of the languages. At a lunch meeting
with members of the Reporting Services product development team, I posed this question. Jason Carlson
told me that they chose VB because it's a natural expression language. In most cases, conditional report
logic must be processed in one line of code. The C# language, although powerful, tends to require multi-
ple lines, whereas multiple functions can be nested in one line using a VB expression. I have used both
languages, but as a longtime VB programmer, I was delighted to learn that VB was clearly a better choice
for this job.
Using Custom Code in a Report
A report may contain embedded Visual Basic .NET code that defines a function you can call from property
expressions. The code editor window is very simple and doesn't include any editing or formatting capabili-
ties. For this reason, you may want to write the code in a separate Visual Studio project to test and debug
before you place it into the report. When you are ready to add code, open the Report Properties dialog. You
can do this from the Report menu. The other method is from the Report Designer right-click menu. Right-
click the report designer outside of the report body and select Properties. On the Properties window, switch
to the Code tab and write or paste your code in the Custom Code box.
The following example starts with a new report. Here is the code along with the expressions that you
will need to create a simple example report on your own. The following Visual Basic function accepts a
phone number or social security number in a variety of formats and outputs a standard U.S. phone num-
ber and properly formatted social security number (SSN). The Value argument accepts the value, and
the Format argument accepts the value Phone or SSN . You're only going to use it with phone numbers,
so you can leave the SSN branch out if you wish.
'*************************************************************
' Returns properly formatted Phone Number or SSN
' based on Format arg & length of Value argument.
' PT - 12/12/06
'*************************************************************
Public Function CustomFormat(Value as String, Format as String) as String
Select Case Format
Case “Phone”
Select Case Value.Length
Case 7
Return Value.SubString(0, 3) & “-” & Value.SubString(3, 4)
Case 10
Return “(“ & Value.SubString(0, 3) & “) “ _
& Value.SubString(3, 3) _
& “-” & Value.SubString(6, 4)
Case 12
Return “(“ & Value.SubString(0, 3) & “) “ _
& Value.SubString(4, 3) & “-” & Value.SubString(8, 4)
Case Else
Return Value