Date & Time Functions
CDateConvert to Date.
=CDate(Fields!BirthDate.Value)
DateAdd
Returns a Date value containing a date and time value to which a specified time interval has been added.
=DateAdd("d",3,Fields!BirthDate.Value)
=DateAdd(DateInterval.Day,3,Fields!BirthDate.Value)
DateDiff
Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,"1/1/2007")
=DateDiff(DateInterval.Year,Fields!BirthDate.Value,"1/1/2007")
DatePart
Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)
DateSerial
Returns a Date value representing a specified year, month, and day, with the time information set to midnight (00:00:00).
=DateSerial(DatePart("yyyy",Fields!BirthDate.Value)-10, DatePart("m",Fields!BirthDate.Value)+3, DatePart("d", Fields!BirthDate.Value)-1)
=DateSerial(DatePart(DateInterval.Year, Fields!BirthDate.Value)-10, DatePart("m",Fields!BirthDate.Value)+3,DatePart("d",Fields!BirthDate.Value)-1)
=DateSerial(2007,10,24)
DateString
Returns or sets a String value representing the current date according to your system.
=DateString()
=DatePart("m",DateString())DateValue
Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).
=DateValue("January 15, 2007")Day
Returns an Integer value from 1 through 31 representing the day of the month.
=Day(Fields!BirthDate.Value)
FormatDateTime
Returns a string expression representing a date/time value.
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
Hour
Returns an Integer value from 0 through 23 representing the hour of the day.
=Hour(Fields!BirthDate.Value)
Minute
Returns an Integer value from 0 through 59 representing the minute of the hour.
=Minute(Fields!BirthDate.Value)
Month
Returns an Integer value from 1 through 12 representing the month of the year.
=Month(Fields!BirthDate.Value)
MonthName
Returns a String value containing the name of the specified month.
=MonthName(10,True)
=MonthName(Month(Fields!BirthDate.Value),False)
="The month of your birthday is " & MonthName(Month(Fields!BirthDate.Value))
Now
Returns a Date value containing the current date and time according to your system.
=Now()
="This time tomorrow is " & DateAdd("d",1,Now())
="This time tomorrow is " & DateAdd(DateInterval.Day,1,Now())
Second
Returns an Integer value from 0 through 59 representing the second of the minute.
=Second(Fields!BirthDate.Value)
TimeOfDay
Returns or sets a Date value containing the current time of day according to your system.
=TimeOfDay()
="Time of the day is " & TimeOfDay()
Timer
Returns a Double value representing the number of seconds elapsed since midnight.
=Timer()
="Number of seconds since midnight " & Timer()
TimeSerial
Returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1.
=TimeSerial(DatePart("h",Fields!BirthDate.Value), DatePart("n",Fields!BirthDate.Value),DatePart("s",Fields!BirthDate.Value))
=TimeSerial(DatePart(DateInterval.Hour,Fields!BirthDate.Value), DatePart(DateInterval.Minute,Fields!BirthDate.Value), DatePart(DateInterval.Second,Fields!BirthDate.Value))
=TimeSerial(23,49,52)
TimeString
Returns or sets a String value representing the current time of day according to your system.
=TimeString()
TimeValue
Returns a Date value containing the time information represented by a string, with the date information set to January 1 of the year 1.
=TimeValue("16:20:17")
=TimeValue(Fields!BirthDate.Value)
Today
Returns or sets a Date value containing the current date according to your system.
=Today()
="Tomorrow is " & DateAdd("d",1,Today())
="Tomorrow is " & DateAdd(DateInterval.Day,1,Today())
Weekday
Returns an Integer value containing a number representing the day of the week.
=Weekday(Fields!BirthDate.Value,0)
=Weekday(Fields!BirthDate.Value,FirstDayOfWeek.System)
WeekDayName
Returns a String value containing the name of the specified weekday.
=WeekdayName(2,True,0)
=WeekDayName(DatePart("w",Fields!BirthDate.Value),True,0)
=WeekDayName(DatePart(DateInterval.Weekday,Fields!BirthDate.Value),True,FirstDayOfWeek.System)
Year
Returns an Integer value from 1 through 9999 representing the year.
=Year(Fields!BirthDate.Value)
SSRS Expressions
Reporting Services Expressions
In Reporting Services, Expressions are used frequently in reports to control content and report appearance. Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.
Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:
Using Built-in Fields
Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Date & Time Functions
The Today() function provides the current date. The expression =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can useFormat function to display required format. Some of the popular date formats are given below:
Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)
DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())
DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)
The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=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,",")
•The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
There are many other function which can be used in expression as shown below:
You can use Visual Basic functions to convert a field from the one data type to a different data type.
The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:
Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. The following expression can be placed in the fill color of a text box tochange the background color depending on the value in the text box.
A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:
A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color.
In Reporting Services, Expressions are used frequently in reports to control content and report appearance. Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.
- Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression=Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.
- Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <
> . You can create complex expressions in the Expression dialog box or type them directly into the Property pane.
Display Report Execution Time in a textbox:
="Report Execution Time: " & Globals!ExecutionTime
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:
Expression
|
Output
|
|---|---|
| =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(DateInterval.Month, 6, Parameters!StartDate.Value)
=DateDiff("yyyy",Fields!BirthDate.Value,Today())
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)
There are many other Date &Time functions which can be used expression:
String Functions
• Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:
=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 useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:
=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)
=Join(Parameters!MyParameter.Value,",")
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")
Conversion Functions
You can use Visual Basic functions to convert a field from the one data type to a different data type.
- The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)
- The following expression displays the number of values selected for the multivalue parameter MyParameter:=CStr(Parameters!MyParameter.Count)
The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:
=IIF(Fields!Total.Value > 100, True, False)
Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. The following expression can be placed in the fill color of a text box tochange the background color depending on the value in the text box.
=IIF(Fields!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))
A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with 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",)
A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. 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")
Check the value of the PurchaseDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:
=IIF(DateDiff("d",Fields!PurchaseDate.Value, Now())>7,"Red","Blue")
Month over month
=DateAdd("m",-1,Today)
MTD
=DateAdd("d",-(Day(today)-1), Today)
Week over week
=DateAdd("ww",-1, Today)
or
=DateAdd("d",-7, Today)
WTD
=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)
Year over Year
=DateAdd("yyyy",-1, Today)
YTD
=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)
Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.
=DateAdd(
"d",-DatePart(DateInterval.DayOfYear,DateAdd("yyyy",-1, Today),0,0)+1,DateAdd("yyyy"
,-1, Today))
First day of week (monday):
=DateAdd("d", -(WeekDay(Today(),2))+1, Today())
Next monday:
=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())
First day of month:
=DateAdd("d",1-DatePart("d",Today()),Today())
First day of next month:
=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))
First day of year:
=DateSerial(YEAR(Today()),1,1)
First day of next year:
=DateSerial(YEAR(Today())+1,1,1)
First Date of last month
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))
=Today.AddDays(1- Today.Day).AddMonths(-1)
Last date of last month
=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))
=Today.AddDays(-1.0 * Today.Day)
First date of current month
=DateSerial(Year(Now()), Month(Now()), 1)
=Today.AddDays(1 - Today.Day)
Last date of current month
=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))
=Today.AddDays(-1 * Today.Day).AddMonths(1)
Yesterday's date
=DateAdd("d", -1, Today)
=Today.AddDays(-1)
Tomorrow's date
=DateAdd("d", 1, Today)
=Today.AddDays(1)
* Monday of current week
=DateAdd("d", 2 - DatePart("w",Today) , Today)
=Today.AddDays(1- Today.DayOfWeek)
* Friday current week
=DateAdd("d", 6 - DatePart("w",Today) , Today)
=Today.AddDays(5- Today.DayOfWeek)
First day of current year
=DateSerial(Year(Now()), 1, 1)
=Today.AddDays(1- Today.DayOfYear)
Last day of current year
=DateSerial(Year(Now()), 12, 31)
=Today.AddDays(-1 * Today.DayOfYear).AddYears(1)
First day of current quarter
=DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)
=Today.AddDays(1- Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) ) -3)
Last day of current quarter
=DateAdd("d",-1,DateAdd("q",1,DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)))
=Today.AddDays(1- Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) )).AddDays(-1)
--Expression to get the 1st day of the previous month (aka Start Date)
DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 1), 1)
--Expression to get the 1st day of the current month (aka End Date)
DateSerial(Year(DateTime.Now), Month(DateTime.Now),1)
--Expression to get the 1st day of the next month
DateSerial(iif( Month(DateTime.Now)=12, Year(DateTime.Now)+1, Year(DateTime.Now)), iif( Month(DateTime.Now)=12, 1, Month(DateTime.Now) + 1), 1)
