Usually, the Stored Procedures that were called in the SSRS report contain practically all of the formatting and conversion already. However, you can still convert or format the data while creating the report in SSDT if you are unsure of the data and when you have control over the report rather than the source code.
Some of the expressions that are frequently used are listed below.
Replace NULL with another value
=IIF(Fields!FieldName.Value = nothing, “NoValue”,Fields! FieldName.Value)
Convert text to the proper case which means the first letter in each word is uppercase.
=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
Alternating row color (Banding effect)
=iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)
The following expression formats the Col1 value as “dd/MM/yyyy”. The Col1 field is a datetime data type.
=FORMAT(Fields!Col1.Value, “dd/MM/yyyy”)
Use multiple IIF functions (also known as “nested IIFs”) to return one of three values depending on the value of Col1. 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!Col1.Value >= 10, “Green”, IIF(Fields!Col1.Value >= 1, “Blue”, “Red”))
Values greater than or equal to 10 display with a green background, between 1 and 9 display with a blue background, and less than 1 display with a red background.
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!Col1.Value >= 10, “Green”, Fields!Col1.Value >= 1, “Blue”, Fields!Col1.Value = 1, “Yellow”, Fields!Col1.Value <= 0, “Red”)
Values greater than or equal to 10 display with a green background, between 1 and 9 display with a blue background, equal to 1 display with a yellow background, and 0 or less display with a red background.
The text and expressions were put together using information from many websites, including Microsoft documentation. I, therefore, decline all credit.
Hope this helps.
Big Data and SQL 