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. | ||
| 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. | ||
| 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. | ||
| 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. | ||
| 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. | ||
| 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. | ||
| 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. | ||
| 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. |
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, …) Example: =Switch(Fields!CategoryID.Value = 1, “Electronics”, Fields!CategoryID.Value = 2, “Clothing”) |
Evaluates multiple conditions and returns 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”) |
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:
“1090”. |
| 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 old name.” Result: “Change 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(Fields! 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<br>Previous time period sales: $8,000<br>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.
