Wednesday, September 1, 2010

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:
  • 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.
Examples of SSRS Expression

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
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 use Format function to display required format. Some of the popular date formats are given 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

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) 
Decision Functions

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") 

21 comments:

  1. Excellent summary. Thanks for putting this together.

    ReplyDelete
  2. But some time iif condition in text color change is not working
    here is my sample code
    =IIF((Fields!act_op.Value)-(Fields!plan_op.Value)
    <0,"RED","BLACK")

    ReplyDelete
    Replies
    1. Try this... it puts the subtraction within a pair of brackets and that may sort it. Let us know if it works?
      =IIF(((Fields!act_op.Value)-(Fields!plan_op.Value))<0,"RED","BLACK")

      Delete
  3. Great.......super Article................


    Also see my www.sqltechi.blogspot.in.......

    ReplyDelete
  4. Hi, I'm having problems returning the count of a field which checks if value1 = value2, then return a certain background colour. Whenever the count of the field is zero, the background colour expression is thrown off. any ideas?

    ReplyDelete
    Replies
    1. Use Conditional formatting. On the textbox properties, FILL then set your expressions for different colors...

      Delete
  5. Hi.. How to convert mins to hrs at RDLC? Thanks in advance...

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Hi, I have some question. How can i change my column color based on some condition. For example, If no value showed in the column, the background should be transparent while if value showed, the background should be changed to green color. I have tried code like =IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red")) but that code works only if i have value.

    ReplyDelete
    Replies
    1. One way to do that is to make sure your cell shows a value, such as this: =Fields!Uniques.Value OR "0"
      Now I'm assured of having *something* in each cell of the column and the color expression I use in the BackgroundColor of the column works.
      I hope that helps.

      Delete
  8. Thanks for the great tip on formatting and eliminating the domain name of the report executor's user name by use of the InStr, Len and Right functions.

    ReplyDelete
  9. Hi ,
    Can you please tell how format excel cell in to hh:mm:ss form SSRS

    ReplyDelete
  10. Hi iRohan,

    From SSRS design view, right-click the text-box with the time value to format, from "Text Box Properties" dialog windows pop-up, select the "Time" option and then the desired format of "HH:mm:ss".

    ReplyDelete
  11. The most common fact for the data representation is that this is information being pulled from a stored procedure and we don't really know what the query was and which kind of data formatting is using in the script. This data formatting can be easily done with some very common expression formatting in SSRS. If we are developing an SSRS report to show gross sales which are broken down in order date, cost, gross profit, gross profit %, order count, total sales etc.
    SSRS – Format() Function

    ReplyDelete
  12. i like this page
    I really like you post.
    I would like to share page collection with you!

    gclub casino
    golden slot mobile
    gclub

    ReplyDelete
  13. very nice.
    How can I fetch records based on minute and seconds in datetime field in SSRS? Say I have dateandtime column in my sql server table as
    2017-06-12 22:17:14.000
    2017-06-12 22:17:16.000
    2017-06-12 22:17:19.000
    2017-06-12 22:17:21.000
    In which data is dumped automatically after n seconds, lets say after every 2 seconds.
    My req is that I need to select those records only which are not dumped after every 2 seconds (means which are dumped after every != 2 (1 or 3 or 5 or n) seonds) while putting 2 (second) in textbox parameter in SSRS report designer.
    Any solution will be appreciated. Anybody?
    i need to filter this because this report has very large pages, more than 100, as data is dumping after every 2 seconds or n seconds for same day/date.
    Thanks

    ReplyDelete
  14. It's really good post because it helpful for learners. check it once through MSBI Online Training Hyderabad

    ReplyDelete
  15. Hi how can we change color for fail and success in column

    ReplyDelete

Here are few FREE resources you may find helpful.