Reporting Services ExpressionsIn 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 FunctionsThe 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 use Format 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 to change 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.
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:
- 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.
Using Built-in Fields
Display Report Execution Time in a textbox:
="Report Execution Time: " & Globals!ExecutionTime
Display Page No in a textbox:
="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:
Date & Time Functions
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 |
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)
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, "\"))
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:
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 to change 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")
No comments:
Post a Comment
Hi,
Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.