SSRS Expression Cheat Sheet

SSRS Expression Cheat Sheet
Problems	Expression
Return first day of current Week (ex. Default Start Date parameter to return WTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today) Expression Output Example: 11/7/2010 12:00:00 AM
Return first day of current Month (ex. Default Start Date parameter to return MTD)	Expression on the parameter default value: =DateAdd("d",-(Day(today)-1), Today) Or =DateSerial( year(today()), month(today()), 1) Expression Output Example: 11/1/2010 12:00:00 AM
Return first day of current year (ex. Default Start Date parameter to return YTD)	Expression on the parameter default value: =DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today) Expression Output Example: 1/1/2010 12:00:00 AM
Return period over period (ex. Default date parameters to a rolling year)	Expression on the parameter default value: Week over Week =DateAdd("ww",-1, Today) Month over Month =DateAdd("m",-1,Today) Year over Year =DateAdd("yyyy",-1, Today) Expression Output Example: 10/9/2010 12:00:00 AM
Return current month name	Expression in Text Box: =MonthName(Month(Today())) Expression Output Example: November
Uppercase fields	Expression in Text Box: =UCASE(Fields!FieldName.Value) Expression Output Example: NOVEMBER
Convert text to proper case (ex. 1st letter in each word is uppercase)	Expression in Text Box: =StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Replace NULL with another value	Expression in Text Box: =iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
Alternating row color (Banding effect)	BackgroundColor property on Text Box: =iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
Handling division by zero	Expression in Text Box: =iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/ iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
Remove a given character from a string (ex. Remove “-“ from a social security number)	Expression in Text Box: =Replace(Fields!EmailAddress.Value,"-","")