Share via

Mostly used SSRS Expressions in day-to-day reports design

  • Article

SSRS Expressions:

 Below are listed most useful SSRS expressions with examples, which are generally used at the time of designing complex reports and dashboards in SSRS:

  • To add comments with Field Value in SSRS expressions

    ="Report Executed By: " + User!UserID

     

  •  To get total number of rows in the tablix or dataset’s result set

    =CountRows("DataSet1")

     

  • Display  Report Execution Time in a textbox:

="Report Execution Time: " & Globals!ExecutionTime

  • To Join all selected values of multi valued parameter in a single string:

    =InStr(Join(Parameters!CityName.Value, ", ")

     

  • To display Page number of Total pages in the footer or header:

    ="Page No. " & Globals!PageNumber & " of Total pages: " & Globals!TotalPages

     

  • To display or print report name with user name and execution time of the report in a textbox as: Report (reportname) generated by DOMAIN/User on 10/23/2015 3:25:08 AM:

    ="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals!ExecutionTime

     

  • If we want to hide or show columns based on the selection of columns name in parameter on the SSRS report , suppose we have 10 columns on the report which are having name as:

    Column1, Column2, Column3, …… , Column10

    And these columns name are listed in a multi value parameter so if user selects Column1, column5 and Column8 from the parameter list then only these three columns should be shown on the report so we can use below expression in the column visibility property, under the Show/Hide based on expressions:

     

    =IIF(InStr(Join(Parameters!ColumnName.Value,","),"ColumnName")=0,True,False)

     

    Decision Functions in SSRS expressions:

     

  • IIF() – IIf function is used to check conditions whether the expression is true or false, to perform next task.

     

    =IIF(Fields!SalesAmount.Value > 100, "LimeGreen","Red")

    ** **

    Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on multiple condition based on the the value of SalesAmount field as:

     

    =IIF(Fields!SalesAmount.Value >= 10, "Green"

    ,IIF(Fields!SalesAmount.Value >=  1, "Blue", "Red"))

    ** **

  • Switch() ­- 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!PctComplete.Value >= 10, "Green",

    Fields!PctComplete.Value >= 1, "Blue",

    Fields!PctComplete.Value = 1, "Yellow",

    Fields!PctComplete.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 TotalSalesOrder controls the color.

     

=Choose(Fields!TotalSalesOrder.Value,"Red","LimeGreen","Yellow")

  • Page Breaks: In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.
  • The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.

=Ceiling(RowNumber(Nothing)/25)

  • To allow the user to set a value for the number of rows per page, create a parameter named RowsPerPage and base the group expression on the parameter, as shown in the following expression:

     

    =Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)

     

  • Conversion Functions: Conversion functions can be used to convert the default data type for a field to the data type needed for calculations or to combine text. 

     

    =CDec(500)

    =CStr(Parameters!MySelection.Count)

     

  • To show field’s value in UpperCase:

     

    =UCASE(Fields!ItemName.Value)

     

  • To show field’s value in proper case, Ist letter in each word must be uppercase and rest of characters should be in lowercase:

     

    =StrConv(Fields!ItemName.Value, VbStrConv.ProperCase)

     

  • Replace Null vlaue with a string or number:

     

    =iif(Fields!SalesAmount.Value = nothing, 0.00,Fields!SalesAmount.Value) =iif(Fields!ItemName.Value = nothing, "Unknown" ,Fields!ItemName.Value)

More on SSRS expressions:

Mostly used SSRS expressions in day-to-day report design

SSRS DateTime Expressions

Thanks!