SSRS Operators

The SSRS supports various operators such as Arithmetic, Comparison, Concatenation, Logical/Bitwise, and Bit Shift operators to perform various operations. This article gives the definitions of each operator and the detailed example to understand the real-time scenarios.

We use the Inner join of Customer and Fact Internet Sales tables from the Adventure Works DW dataset to demonstrate the SSRS operators. The below image shows the report.

Source Table

SSRS Arithmetic Operators

The following is the list of Arithmetic Operators available in the SSRS reporting services.

  1. ^ Number raised to the power of given value. Fields!Bonus.Value ^ 2
  2. * It Performs the Multiplication of two numbers. Fields!Orders.Value * 2
  3. / Division of two Numbers and returns the floating-point value. Fields!Income.Value / 2
  4. \ Division of two Numbers and returns the integer value. Fields!Income.Value / 2
  5. Mod Divided two Numbers and returns the remainder value. Fields!Income.Value Mod 5
  6. Addition of two numbers. If you use it on string columns, it performs the string concatenation. Fields!Orders.Value + 10
  7. It Subtracts one number from another. Fields!Orders.Value – 10

For this SSRS Arithmetic operator example, let me add a new column to the right side of the Sales column. Next, right-click the textbox under it and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.

It opens the following expression window to perform Arithmetic operations. The below expression returns the Orders raised to the power of three.

=Fields!Orders.Value ^ 3
SSRS Arithmetic Operators Expression

Similarly, let me create six more columns to show the remaining arithmetic operators. The expressions that we used are shown below. For more functions >> Click Here!

=Fields!Income.Value * 30 / 100

=Fields!Sales.Value /  Fields!Orders.Value

=Fields!Sales.Value \  Fields!Orders.Value

=Fields!Income.Value MOD 12

=Fields!Sales.Value + Fields!Tax.Value

=Fields!Sales.Value - Fields!ProductCost.Value

Please click the preview tab to see the SSRS Arithmetic Operators report.

SSRS Arithmetic Operators Report preview

SSRS Comparison Operators

The following is the list of Comparison Operators available in the SSRS reporting services.

  1. < Less than. Fields!Sales.Value < 100
  2. <= Less than or Equal to. Fields!Sales.Value <= 100
  3. Greater than. Fields!Sales.Value > 2500
  4. = Greater than or Equal to. Fields!Sales.Value >= 2500
  5. = Equal to. Fields!Sales.Value = 3000
  6. <> Not Equal to. Fields!Sales.Value <> 3000
  7. Like: It performs Wildcard Comparison of two strings. Fields!Name.Value Like “J*”
  8. Is: It Compares two object reference variables to check they are the same. Fields!FirstName.Value Is Fields!LastName.Value

In general, <, >, <=, >=, =,<>, Like, and Is operators will perform comparison and return boolean True or False. For instance, Fields!Orders.Value < 3 expression returns True, if the orders are less than three. Otherwise, it returns False. However, it is better to show a message instead of True/False. For this, you must use the IIF Statement.

For this SSRS Comparison Operators example, let me add a new column to the right side of the Tax column. Next, right-click the textbox under it and choose the Expression. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.

It opens the following expression window to perform Comparison operations. The below expression checks whether the order value is less than three. If true, return Good; Otherwise, return Bad.

=IIf(Fields!Orders.Value < 3, "Good", "Bad")
SSRS Comparison Operators Expression

Similarly, let me create eight more new columns to show the remaining comparison operators. The expressions that we used are shown below.

=IIf(Fields!Orders.Value <= 3, "Good", "Bad")

=IIf(Fields!Orders.Value > 2, "Nice", "Nope")
=IIf(Fields!Orders.Value >= 2, "Nice", "Nope")

=IIf(Fields!Orders.Value = 4, "High", "Low")

=IIf(Fields!Orders.Value <> 4, 
Fields!Income.Value * Fields!Orders.Value / 100, 
Fields!Income.Value * 7 / 100)

=Fields!LastName.Value Like "C"

=IIf(Fields!Gender.Value Is "M", "Male","Female")

=Fields!FirstName.Value Is Fields!LastName.Value

Click the preview tab to see the result of the SSRS Comparison Operators.

SSRS Comparison Operators Report Preview

SSRS Concatenation Operators

The following is the list of Concatenation Operators available in the SSRS reporting services.

  • &: It performs the string concatenation. Fields!FirstName.Value & ” ” & Fields!LastName.Value
  • +: Add two numbers. Using on-string columns performs the string concatenation.

For this SSRS Concatenation Operators example, let me add three new columns to the right side of the Sales column. Next, right-click the textbox under & and choose the Expression. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.

It opens the following expression window to perform Concatenation operations. The below expression concat or combines the Employee’s first name, last name, and Gender with retired spaces and the Gender word.

=Fields!FirstName.Value & " " & Fields!LastName.Value
& " Gender = " & Fields!Gender.Value
SSRS Concatenation Operators Expression

When you use the + operator on the string column or text, it acts as the & and performs the string concatenation. The below code returns the full name.

=Fields!FirstName.Value + " " + Fields!LastName.Value

If you use the + operator on the numeric columns, it adds them.

=Fields!Income.Value + (Fields!Income.Value * 5 / 100)

Click the preview tab to see the result of the SSRS Comparison Operators report.

SSRS Concatenation Operators report preview

SSRS Logical/Bitwise Operators

The following is the list of Logical and Bitwise Operators available in the SSRS reporting services.

  1. And For Boolean expressions: it performs a logical conjunction and a bitwise conjunction for numeric expressions.
  2. Not For Boolean expressions: it performs a logical negation and bitwise negation for numeric expressions.
  3. Or For Boolean expressions: it performs a logical disjunction and a bitwise disjunction for numeric expressions.
  4. Xor For Boolean expressions: it performs a logical exclusion and bitwise exclusion for numeric expressions.
  5. AndAlso: It performs a Short-circuiting logical conjunction.
  6. OrElse: It performs a Short-circuiting logical disjunction.

For this SSRS Logical and Bitwise Operators example, let me add seven new columns to the right side of the Sales column. Next, right-click the textbox under And header and choose the Expression. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.

It opens the following expression window to perform Logical operations. The below expression checks whether the income is greater than 100000 and the order value is less than or equal to 2. If True, print Good; Otherwise, return Average.

=IIf((Fields!Income.Value > 100000) And
(Fields!Orders.Value <= 2), "Good", "Average")
SSRS Logical and Bitwise Operators Expression

If you don’t use the IIF statement, it returns a boolean True or False.

=(Fields!Income.Value > 100000) And 
(Fields!Orders.Value <= 2)

The remaining expressions for the Or, Not, Xor, And Also, and Or Else are:

=IIf((Fields!Income.Value > 100000) Or 
(Fields!Orders.Value <= 2), "Good", "Bad")

=IIf(Not(Fields!Sales.Value > 600), "Not Ok", "OK")

=IIf((Fields!Income.Value > 100000) Xor 
(Fields!Orders.Value <= 2), "Good", "Bad")

=IIf((Fields!Income.Value > 100000) AndAlso
(Fields!Orders.Value <= 2), "Good", "Bad")

=IIf((Fields!Income.Value > 100000) OrElse
(Fields!Orders.Value <= 2), "Good", "Bad")

Click the preview tab to see the result of the SSRS Logical and Bitwise Operators report.

SSRS Logical and Bitwise Operators Report Preview

SSRS Bit Shift Operators

The following is the list of Bit Shift Operators available in the SSRS reporting services.

  • << It performs Arithmetic Left shift on a bit pattern. Fields!Counter.Value << 2.
  • >> It performs Arithmetic Right shift on a bit pattern. Fields!Counter.Value >> 2.

For the SSRS Logical and Bitwise Operators example, let me add four (2 for left and 2 for right shift) new columns to the right side of the Sales column. Next, right-click the textbox under And header and choose the Expression. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.

It opens the following expression window to perform the bit shift operations. The below expression shifts the 8 or 16-bit digit in the order column to the left side for two positions.

=(Fields!Orders.Value) << 2
SSRS BIt Shift Operators expression

Right shift for two positions.

=(Fields!Orders.Value) >> 2

Left and Right shift the bit values of the order value to four positions.

=(Fields!Orders.Value) << 4
=(Fields!Orders.Value) >> 4

Click the preview tab to see the result of the SSRS Left and Right Bit Shift Operators report.

SSRS left and right Bit Shift Operators report preview