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

The SWITCH() function accepts an unlimited number of expression and value pairs. The last argument
accepts a value that is returned if none of the expressions resolve to True. I can use this in place of the
previous nested IIF() example:

=SWITCH( Fields!Quantity.Value < Fields!ReorderPoint.Value, “Red”,
Fields!ListPrice.Value > 100, “Blue”, 1=1, “Black” )

Unlike the IIF() function, there is no “FalsePart” value. Each expression and return value is passed
as a pair. The first expression in the list that evaluates to True causes the function to stop processing and
return a value. This is why I included the expression “1=1” . Since this expression will always evaluate
to True, this becomes the catch-all expression that returns “Black” if no other expressions are True.

Visual Basic .NET supports many of the old-style VBScript and VB 6.0 functions as well as newer over-
load method calls. In short, this means that there may be more than one way to perform the same action.
The following table contains a few other Visual Basic functions that may prove to be useful in basic
report expressions.

Function Description Example

Returns a string value

FORMAT() =FORMAT(Fields!TheDate.Value, “d”)

formatted using a regular

=FORMAT(Fields!TheDate.Value, “mm/d/yy”)

expression format code or
pattern. Similar to the Format
property but can be
concatenated with other
string values.

Returns a specified number

MID(), =MID(Fields!TheString.Value, 3, 5)

of characters from a specified

LEFT() and =LEFT(Fields!TheString.Value, 5)

position (if using MID() ) and

RIGHT() =Fields!TheString.Value.SUBSTRING(2, 5)

.SUBSTRING() for a specific length. You can
also use the .SUBSTRING()
method.

Returns an integer for the first

INSRT() =INSRT(Fields!TheString.Value, “,”)

character position of one
string within another string.
Often used with MID() or
SUBSTRING() to parse strings.

Converts any value to a string =CSTR(Fields!TheNumber.Value)
type. Consider using the

CSTR()

=Fields!TheNumber.Value.TOSTRING()

newer TOSTRING() method.

Type conversion function

CDATE() =CDATE(Fields!TheString.Value)

similar to CSTR(0) used to

CINT() =CTYPE(Fields!TheString.Value, Date)

convert any compatible value

CDEC()

to an explicit data type.
Consider using the newer
CTYPE() function to convert
to an explicit type.

...