Mostly used SSRS expressions in day to day reports design

Mostly used SSRS expressions in reports design:

Ø  For showing underline based on the expression in SSRS Textbox:

-          Go to Textbox properties and under the Font option, write below expression in the Effects option for showing under line:

=IIF((Fields!MyOrder.Value = 2 Or Fields!OrderItems.Value = 5) and Fields!TotalSales.Value >0, "Underline","NONE")

Ø  If we want to show link for Drill through based on the values only then we can write an expression in the “Specify a report” field of “Go to report option” action of the textbox:

=iif(Fields!MyOrder.Value = 2 Or Fields!MyOrder.Value = 5 , "ReportName", Nothing)

Ø  To show Report’s execution time:

="Report run at: " & Globals!ExecutionTime

Ø  To change the format of a date time field in SSRS expression:

=Format(Fields!OrderMonth.Value,"MM/dd/yyyy")

Ø  To change the data type into integer , decimal, double and string etc., below are expressions:

=CStr(Fields!MyOrder.Value)

=CInt(Fields!MyOrder.Value)

=CDec(Fields!MyOrder.Value)

Ø  Nested IIF condition in SSRS:

=IIF(Fields!MyOrder.Value = 2,1,

     IIF( Fields!MyOrder.Value = 3 and Fields!graphid.Value="N",0,

                IIF( Fields!MyOrder.Value = 4 and Fields!graphid.Value="Y",1,-1))

)

Ø  Switch condition in SSRS:

=SWITCH(Fields!Tolerance.Value = "Low", "Red",

Fields!Tolerance.Value = "Moderate", "Orange",

Fields!Tolerance.Value = "Medium", "Yellow",

Fields!Tolerance.Value = "High", "Blue",

Fields!Tolerance.Value = "Very High", "Green")

Ø  Expression to set Alternate background color in SSRS:

=iif(RowNumber(Nothing) Mod 2 = 0, "LightGrey","LightBlue")

Ø  Expression to set background color in specific condition as:

-          If Sales value is less then 40 then red else LightGrey

=IIF(Fields!TotalSales.Value>=40,”LightGrey”,”Red”)

-          If Address contains “Delhi” then red else LightGrey

=IIF(InStr(Fields!Address.Value,”Delhi”)=0,”LightGrey”,”Red”)

NOTE: Here InStr(Fields!Address.Value,”Delhi”)=0 is working as like ‘%Delhi%’operator of SQL

Ø  To set the font to bold, set the Font-FontWeight property of the table cell to be:

=IIF(Fields!Mark.Value < 80, "Bold", "Normal")

-          To set multi font color and font size go on this link:

Multi Font Color / Multi Font Size within a Single Field / Textbox

Ø  To create a dynamic database connection based on the report’s parameter value, we need to write below expression in the Data Source editor:

="Data Source=PC-Tran305;Initial Catalog=Tran"&Parameters!DatabaseName.Value

Ø  To show parameter values on report in a single text box, we need to write an expression as:

-          & is used to concatenate

-           Join operator is used to combine multiple values for a multivalve parameter so values will be coming as: A, B, C etc.

-          vbcrlf  is used to Print value in New line in SSRS textbox:

-          If we have labels different from the value then we can show Label only by using “.Label” instead of “.Value” in the expression.

="Site Name: " & Parameters!SiteName.Label

+ vbcrlf & "Main Category(s): " & Join(Parameters!MainCategory.Label, ", ")

+ vbcrlf & "Sub Category(s): " & Join(Parameters!SubCategory.Label, ", ")

+ vbcrlf & "Date From: " & Parameters!DateFrom.Label

+ vbcrlf & "Date To: " & Parameters!DateTo.Label

+ vbcrlf & "Refreshed On: " & DateAdd("h",-1,Globals!ExecutionTime) & " CST"

-          toString() function is used to convert value into string.

& vbcrlf & "Monthly/Weekly: " & Parameters!mwparam.Label.toString()

Ø  Opening a link in SSRS within a new window: In SSRS, we want to provide a hyperlink on a textbox value to open a webpage by providing input value i.e. ID along with link, we can use below expression as:

-          Go to Action option of Textbox properties and select “Go to URL” radio button and in the fuction(fx) window, set hyperlink expression:

="javascript:void(window.open('http://www.google.com','_blank'))"

-          If URL value is coming from dataset field:

="javascript:void(window.open('"+ Fields!URLPath.Value + "','_blank'))"

More SSRS expressions:

Mostly used SSRS expressions in day-to-day report design – Vol.2

SSRS DateTime Expressions

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!