SSRS Reference Material

Report Dates & Date Functions

Date Functions

FormatDateTime(Parameters!Date.Value,1) = Tuesday, April 10, 2007
FormatDateTime(Parameters!Date.Value,2) = 4/10/2007
FormatDateTime(Parameters!Date.Value,3) = 12:00:00 AM
FormatDateTime(Parameters!Date.Value,4) = 00:00

Format(Parameters!Date.Value,”dd-MM-yyyy”) = 10-04-2007
Format(Parameters!Date.Value,”dd/MM/yyyy”) = 10/04/2007
Format(Parameters!Date.Value,”MMM-dd-yyyy”) = Apr-10-2007
Format(Parameters!Date.Value,”MMM-dd-yy”) = Apr-10-07

=FORMAT(Today(),"M/d/yy") = 8/23/10
=FORMAT(Today(),"MM-dd-yyyy") = 08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy") = Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy") = August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") = Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") = Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") = Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") = Aug 23, 2010 01:43:33 PM

DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

DateDiff - Returns a value specifying the number of intervals of time between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())

DatePart - Returns an Int value containing the component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)

http://sql-bi-dev.blogspot.com/2010/09/ssrs-expressions.html

String Functions

Concatenation=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

•Format dates and numbers in a string with the Format function.
=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

•The Right, Len, and InStr functions are good to use as substrings, in the example the “\” is to pull our the right part of the “\”:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

VB Version of above
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)

• Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")
http://sql-bi-dev.blogspot.com/2010/09/ssrs-expressions.html

Conversion Functions

=CDec(100)
​=CStr(Parameters!MyParameter.Count)

Decision Functions

The IIF function returns one of two values depending on whether the expression is true or false.

=IIF(Fields!PercentComplete.Value >= 10, "Green",
     IIF(Fields!PercentComplete.Value >= 1, "Blue",
       "Red")
      ​)

=IIF(DateDiff("d",Fields!PurchaseDate.Value, Now())>7,"Red","Blue")

The Switch function is useful when you have three or more conditions to test. The Switch function returns the first expression in a series that evaluates to true:
=Switch(Fields!PercentComplete.Value >= 10, "Green",
Fields!PercentComplete.Value > 1, "Blue",
Fields!PercentComplete.Value = 1, "Yellow",
Fields!PercentComplete.Value <= 0, "Red",)

The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. IE: If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color.
=Choose(Fields!MyIndex.Value,"Red","Green","Yellow")

http://sql-bi-dev.blogspot.com/2010/09/ssrs-expressions.html