37 Essential SSRS Expressions: Cheat Sheet for Report Designers

SQL Server Reporting Services (SSRS) is a powerful reporting platform that enables the creation of versatile and reliable reports. The foundation for developing dynamic reports lies in the SSRS expressions language. Bold Reports also offer RDL-based reporting solutions similar to SSRS. We are delighted to share our expertise in utilizing these SSRS expressions, which play a crucial role in both SSRS and Bold Reports.

To assist you in utilizing these SSRS expressions, we have curated a cheat sheet featuring 37 frequently employed ones. Whether you possess expertise in report designing or are just starting with SSRS, this guide will prove invaluable.

Aggregate SSRS Expressions

Title	Expression and Example	Explanation	Output
Sum	Expression:
\=Sum(Fields!FieldName.Value)

Example :
\=Sum(Fields!SalesAmount.Value) | Used to calculate the sum of data set field values. It is best to use this method with tables, charts, and gauges since it does not provide the necessary data set to be used. | Example values:
[10, 20, 20]

Result:
"50" | | | Expression:
\=Sum(Fields!FieldName.Value, DataSetName)

Example:
\=Sum(Fields!SalesAmount.Value, “Sales”) | Used to calculate the sum of field values in a data set with a specific data set name. It is best to use this method with text boxes and images, rather than with tables, as text boxes and images do not retain the data set name. | Example values:
[10, 20, 20]

Result:
"50" | | Average | Expression:
\=Avg(Fields!FieldName.Value)

Example:
\=Avg(Fields!UnitPrice.Value) | Calculates the average of data set field values. It’s suitable for tables, charts, and gauges, without requiring explicit data set specification. | Example values:
[10, 20, 20, 30]

Result:
"20" | | | Expression:
\=Avg(Fields!FieldName.Value, DatasetName)

Example:
\=Avg(Fields!UnitPrice.Value, ”Product”) | Calculates the average of field values in a data set using a specified data set name, commonly used with text boxes and images that don't retain the data set name. | Example values:
[10, 20, 20, 30]

Result:
"20" | | Max | Expression:
\=Max(Fields!FieldName.Value)

Example:
\=Max(Fields!SalesAmount.Value) | Obtains the maximum value from a specified field, often utilized with tables, charts, and gauges without the need for explicit data set specification. | Example values:
[10, 20, 20, 30]

Result:
"30" | | | Expression:
\=Max(Fields!FieldName.Value, DatasetName)

Example:
\=Max(Fields!SalesAmount.Value, “Sales”) | Computes the maximum value of field values in a data set using a specified data set name, frequently employed with text boxes and images that do not retain the data set name. | Example values:
[10, 20, 20, 30]

Result:
"30" | | Min | Expression:
\=Min(Fields!FieldName.Value)

Example:
\=Min(Fields!Quantity.Value) | Obtains the minimum value from a specified field, often utilized with tables, charts, and gauges, without the need for explicit data set specification. | Example values:
[10, 20, 20, 30]

Result:
"10" | | | Expression:
\=Min(Fields!FieldName.Value,
DatasetName)

Example:
\=Min(Fields!Quantity.Value,
”Products”) | Computes the maximum value of field values in a data set using a specified data set name, frequently employed with text boxes and images that do not retain the data set name. | Example values:
[10, 20, 20, 30]

Result:
"10" | | First | Expression:
\= First(Fields!FieldName.Value)

Example:
\= First (Fields!ProductName.Value) | Obtains the first value of the specified field from the data set, commonly used with tables, charts, and gauges, without requiring explicit data set specification. | Example values:
[Bike, cloth, accessories, component]

Result:
"Bike" | | | Expression:
\= First(Fields!FieldName.Value, "DataSetName")

Example:
\= First (Fields!ProductName.Value, "ProductsDataSet") | Retrieves the first value of a specified field from the specified data set, commonly used with text boxes and images that do not retain the data set name. | Example values:
[Bike, cloth, accessories, component]

Result:
"Bike" | | Last | Expression:
\=Last(Fields!FieldName.Value)

Example:
\=Last(Fields!ProductName.Value) | Obtains the last value of the specified field from the data set, commonly used with tables, charts, and gauges, without requiring explicit data set specification. | Example values:
[Bike, cloth, accessories, component]

Result:
"component" | | | Expression:
\=Last(Fields!FieldName.Value, "DataSetName")

Example:
\=Last(Fields!ProductName.Value,
"ProductsDataSet") | Retrieves the last value of a specified field from the specified data set, commonly used with text boxes and images that do not retain the data set name. | Example values:
[Bike, cloth, accessories, component]

Result:
"component" | | Count | Expression:
\=Count(Fields!FieldName.Value)

Example:
\=Count(Fields!ProductName.Value) | Used to calculate the count of data set field values. It is best to use this method with tables, charts, and gauges since it does not provide the necessary data set to be used. | Example values:
[Bike, cloth, accessories, component]

Result:
"4" | | | Expression:
\=Count(Fields!FieldName.Value, DataSetName)

Example:
\=Count(Fields!ProductName.Value, Products) | Used to calculate the number of field values in a data set with a specific data set name. It is best to use this method with text boxes and images, rather than with tables, as text boxes and images do not retain the data set name. | Example values:
[Bike, cloth, accessories, component]

Result:
"4" | | CountDistinct | Expression:
\=CountDistinct(Fields! FieldValue.Value)

Example:
\=CountDistinct(Fields! ProductName.Value) | Used to calculate the distinct count of data set field values. It is best to use this method with tables, charts, and gauges since it does not provide the necessary data set to be used. | Example values:
[Bike, cloth, accessories, component, bike, cloth]

Result:
"4" | | | Expression:
\=CountDistinct(Fields! FieldValue.Value, "DatasetName")

Example:
\=CountDistinct(Fields! ProductName.Value, "sales") | Used to calculate the distinct number of field values in a data set with a specific data set name. It is best to use this method with text boxes and images, rather than with tables, as text boxes and images do not retain the data set name. | Example values:
[Bike, cloth, accessories, component, bike, cloth]

Result:
"4" |

SSRS Expressions for Formatting

Title	Expression and Example	Explanation	Output
IIF	Expression:
\=IIf(Condition, ValueIfTrue, ValueIfFalse)
Example:
\=IIf(Fields!Quantity.Value > 10, "High", "Low")	This method evaluates a condition and returns varying values depending on the outcome. It is advisable to utilize this approach when working with tables, charts, and gauges, as it lacks the required data set for usage.	If Condition True: "High"
If Condition False: "Low".
Switch	Expression:
\=Switch(Condition1, Value1, Condition2, Value2, ...)
Result:
\=Switch(Fields!CategoryID.Value = 1, "Electronics", Fields!CategoryID.Value = 2, "Clothing")	Evaluates multiple conditions and return different values based on the condition that matches.	Provides output options such as electronics and clothing.
Date Formatting	Expression:
\=Format(Fields!FieldName.Value, "dd/MM/yyyy")
Example:
\=Format(Fields!OrderDate.Value, "dd/MM/yyyy")	Puts a date field into a specified format.	Example values:
6/13/2023 12:00:00 AM
Result:
06/13/2023
NOW	Expression:
Now()	Returns the current date and time.	Provides output: current date and time.
Currency Formatting	**Expression:
**="Total FieldName: $" & Format(Fields!FieldValue.Value, "N2")
Example:
\="Total Revenue: $" & Format(Fields!Revenue.Value, "N2")	Formats the Revenue field as a currency value with two decimal places.	Example values:
5000.50
Result:
$5,000.50

General SSRS Expressions

Title	Expression and Example	Explanation	Output
Uppercase	Expression:
\=UCase(Fields!FieldName.Value)
Example:
\=UCase(Fields!ProductName
.Value)	The "UCase" function converts the value of the field to uppercase.	Example values:
“example”
Result:
"EXAMPLE"
Lowercase	Expression:
\=Lower(Fields!FieldName.Value)
Example:
\=Lower(Fields!ProductName
.Value)	The "Lower" function converts the value of the field to lowercase.	Example values:
"EXAMPLE"
Result:
"example"
Row Number	Expression:
\=IIF(RowNumber(Nothing) Mod )
Example: =IIF(RowNumber(Nothing) Mod 2 = 0, "LightGrey", "White")	The expression uses "RowNumber" and "Mod" to add alternate row colors in the table.	Every second row will have a light grey background color.
Page Number	Expression:
\=Globals!PageNumber	Provides the current page number.	Displays the page number of the report:
Concatenation	Expression:
\=Fields!FieldName.Value & " " & Fields!FieldName.Value Example: =Fields!FirstName.Value & " " & Fields!LastName.Value	Combines text or field values into a single string.	Example values:
Firstname: John LastName: Doe Result:
“John Doe”
Replace	Expression:
\=Replace(Fields!FieldName.Value, "old", "new")
Example: =Replace(Fields!FirstName.Value, "James", "John")	Replaces specific text within a field.	Example values:
“Change the old
name.”
Result:
"Change the new
name.”
IsNothing	Expression:
\=IIf(IsNothing(Fields!FieldName.Value), "N/A", Fields! FieldName.Value)
Example: =IIf(IsNothing(Fields!ProductName.Value), "N/A", Fields!ProductName.Value)	Checks if a field value is null and handles it accordingly.	Displays "N/A" if the value is null; otherwise, it displays the actual value.
InStr	Expression:
\=InStr(Fields!FieldName.Value, "keyword") > 0
Example:
\=InStr(Fields!Description.Value, "keyword") > 0	Searches for a keyword within a text field.	Returns true if the keyword is found in the description field; otherwise, it returns false.
Ranking	Expression:
\=Rank(Fields!FieldName.Value)	Assigns a rank to each row based on a specified field or expression.	Example values:
[15, 20, 10, 25, 15] Result:
[2, 3, 1, 4, 2]
Custom Code	Expression:
\=Code.MyFunction(Fields!FieldName
.Value)
Example: =Code.MyFunction(Fields!Value.Value)	Calls a custom function defined in the report's code section.	Executes a custom function named MyFunction, passing the Value field as a parameter.
Lookup	Expression:
\=Lookup(FieldValue.Value, Fields! FieldValue.Value, Fields! FieldValue
.Value, "Dataset2")
Example:
\=Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Name.Value, "Dataset2")	Retrieves a value from another data set based on a matching key.	Looks up the name field in Dataset2 using the ID field as the key.
CEILING	Expression:
\=CEILING(Month(Fields!FieldValue.Value) / 3)
Example: =CEILING(Month(Fields!Date.Value) / 3)	Determines the quarter of a given date.	Example values:
June 15th, 2023 Result:
2 (The value 2 indicates that the date corresponds to the second quarter of the year.)
Group Variables	Expression:
=Variables!GroupTotal.Value	Accesses group-level variables to store and retrieve values during grouping.	Total sales value for a specific region.
Result:
10000
String Manipulation	Expression:
\=StrConv(Fields!FieldValue.Value, VbStrConv.UpperCase)
Example :
\=StrConv(Fields!Text.Value, VbStrConv.UpperCase)	Converts string case or replaces a specific substring within a string.	Multivalue parameter handling.
Multivalue Parameter Handling	Expression:
\=Join(Parameters!SelectedValues.Value, ", ")	Handles multivalue parameters by joining or splitting the selected values.	Example values :
Parameter1: Value1 Parameter2: Value2 Parameter3: Value3 Result:
["Value1", "Value2", "Value3"]

Frequently Used SSRS Expressions

Title	Expression and Example	Explanation	Output
Conditional formatting based
on a parameter
value	Expression:
\=IIF(Parameters!FieldValue
.Value = "TrueCondition",
"True", "False")
Example: =IIF(Parameters!SalesType
.Value = "Revenue", "Revenue Report", "Quantity Report")	This expression checks the value of the "SalesType" parameter and displays different titles based on the selected option. If "Revenue" is selected, it shows "Revenue Report," otherwise it shows "Quantity Report."	Revenue Report
(if SalesType = Revenue) or
Quantity Report
(if SalesType = Quantity)
Calculating Percentage of
Group Total	Expression:
\=(Sum(Fields!FieldValue.Value)
/ Sum(Fields!FieldValue.Value, "GroupName")) * 100
*Example:*
\=(Sum(Fields!Total.Value) / Sum(Fields!TotalCount.Value, "GroupName")) * 100	Calculates the percentage contribution of a value within a group.	Example values:
[500,750]
Result:
Value1: (500 / (500 + 750 + 1000)) 100 = 21.74% Value 2: (750 / (500 + 750 + 1000)) 100 = 32.61%
Dynamic Column Visibility	Expression:
\=IIf(Parameters!FieldValue
.Value = "True", False, True)
Example: =IIf(Parameters!ShowColumn
.Value = "True", False, True)	Controls the visibility of a column based on a parameter value.	Hides the column if the parameter ShowColumn is set to True; otherwise, it shows the column.
Custom Sorting
with Switch Statement	Expression:
\=Switch(Fields!FieldValue
.Value = "A", 1, Fields!FieldValue.Value =
"B", 2, Fields!FieldValue
.Value = "C", 3)
Example: =Switch(Fields!Category
.Value = "A", 1, Fields!Category.Value =
"B", 2, Fields!Category
.Value = "C", 3)	Customizes the sorting order of a field using a switch statement.	Assigns a numerical value to each category for sorting purposes.
Conditional Drill-Through
Navigation	Expression:
\=IIf(Fields!FieldValue.Value = "Truecondition", "True", "False") Example: =IIf(Fields!Category.Value = "Sales", "SalesReport", "InventoryReport")	Determines the target report for drill-through navigation based on a field value.	Navigates to the SalesReport if the category is Sales; otherwise, it navigates to the InventoryReport.
Handling Zero Division Errors	Expression:
\=IIf(Fields!FieldValue.Value = 0, 0, Fields!FieldValue.Value / Fields!FieldValue.Value)
Example: =IIf(Fields!Denominator.Value = 0, 0, Fields!Numerator.Value / Fields!Denominator.Value)	Handles division by zero errors by returning a default value.	Calculates the division of the numerator by the denominator, but if the denominator is zero, it returns zero to prevent the error.
Nested IIf	Expression:
\=IIf(Fields!FieldValue.Value > condition, "High", IIf(Fields!FieldValue.Value > condition, "Medium", "Low")) Example:
\=IIf(Fields!Value.Value > 10, "High", IIf(Fields!Value.Value > 5, "Medium", "Low"))	Implements nested logical conditions to return a value.	Assigns a category (High, Medium, or Low) based on the value field.
Dynamic image
path	Expression:
\="~/Images/" & Fields!FieldValue
.Value & ".jpg"
Example:
\="~/Images/" & Fields!ProductCode.Value & ".jpg"	Generates a dynamic image path based on the ProductCode field.	Example values:
"ABC123”
Result: ~/Images/ABC123.jpg.
Period Over Period	Expression:
\=Sum(Fields!FieldValue.Value) /Previous(Sum(Fields!FieldValue
.Value)) - 1
Example:
\=Sum(Fields!Value.Value) / Previous(Sum(Fields!Value
.Value)) - 1	Calculates the percentage change between the current time period and the previous time period for a given value.	Example values:
If current time period sales: $10,000
Previous time period sales: $8,000
Expression
Result:
0.25 (25%)

Conclusion

I hope this blog provided sufficient guidance for these 37 essential SSRS expressions. To learn more about SSRS expressions, look through our documentation. To experience Bold Reports SSRS expressions live, check out our demo samples.

If you have any questions, please post them in the comments section below. You can also contact us through our contact page, or if you already have an account, you can log in to ask your questions.

Bold Reports offers a 15-day free trial without any credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Try it and let us know what you think!

Catch us on Twitter, Facebook, and LinkedIn for info about upcoming releases.