Chat Icon
Webinar Rendering Millions of Records Fast: Printable Reports for Data-Driven Decisions —Thursday, June 27, 10:00 A.M. ET SIGN UP NOW
Created with Sketch.
Login-icon
37 Essential SSRS Expressions: Cheat Sheet for Report Designers
Essential SSRS Expressions: Cheat Sheet

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.
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 TwitterFacebook, and LinkedIn for info about upcoming releases.

Tags:

Share this blog

Leave a Reply

Your email address will not be published. Required fields are marked *