By: Scott Murray | Comments | Related: > Reporting Services Development
Problem
SQL Server Reporting Services offers special conditional logic expressions: Xor, AndAlso, and OrElse that can be used in an SSRS report. Can you help explain how these can be used?
Solution
Three special conditional logic examples exist in SSRS which allow you to use several special logic conditions. These expressions: Xor, AndAlso, and OrElse, provide a way to conditionally process values and thus prevent an error when evaluating an expression.
We will use the Adventure Works databases as the basis for our SSRS data source. The 2014 versions of the regular and data warehouse databases backups are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop an example report. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.
SSRS Conditional Logic - Xor
For the first example we will use the XOR expression. The XOR expression evaluates the left side of the expression first. Then processing moves to the right side of the expression (right side of the XOR). The results are evaluated as follows:
- If BOTH expressions are TRUE, then the XOR expression returns FALSE (yes false)
- If BOTH expressions are FALSE, then the XOR expression returns FALSE
- If either the Left side or Right side is TRUE, then the XOR expression returns TRUE
So for XOR we are looking for a single qualifying statement. To help explain this option on a report, I have created the following basic query which lists categories and sports.
SELECT 'Sport' AS Category, 'Soccer' AS Activity1, 'Basketball' AS Activity2
UNION ALL
SELECT 'Sport' AS Category, 'Golf' AS Activity, 'Soccer' AS Activity2
UNION ALL
SELECT 'Sport' AS Category, 'Rugby' AS Activity, 'XC' AS Activity2
UNION ALL
SELECT 'Sport' AS Category, 'Soccer' AS Activity, 'Soccer' AS Activity2
We want to check for any player that either has Soccer listed twice or is not playing soccer at all. The XOR expression is a perfect use of such a test. We first start with a basic report, as shown below, which displays the Category, Activity1, and Activity2 fields.
In the outcome column we use the following expression:
=IIF((Fields!Activity1.Value="Soccer") XOR (Fields!Activity2.Value="Soccer"), "1 soccer season", "Error--No soccer seasons or 2 soccer seasons")
Thus, we check both Activity1 and Activity2 for the presence of "soccer". The below screen print shows how the 1st and 2nd row both have just one instance of soccer and thus are evaluated to "True" which results in the "1 soccer season" tag. However, rows 3 and 4 have no instances and 2 instances of soccer respectively, and thus are evaluated to False. As you can see, the XOR expression makes it easier to check for the lack of an instance but also duplicate instances of a value.
SSRS Conditional Logic - OrElse
The OrElse expression is a cousin expression to XOR. The OrElse expression evaluates the left side of the expression first; if the expression evaluates to True, then further processing stops and the right side of the expression is not evaluated. However, if the left side of the expression evaluates to False, then the right side is evaluated and the results are returned based on the evaluation of the second expression. The results are evaluated as follows:
- If the Left side evaluates to TRUE, then processing stops and the OrElse expression returns TRUE
- If the Left side evaluates to FALSE, then processing continues to the Right
ride.
- If the right side evaluates to TRUE, then the OrElse expression returns TRUE
- If the right side evaluates to FALSE, then the OrElse expression returns FALSE
- If BOTH expressions are FALSE, then the OrElse returns FALSE
Thus for the OrElse expression, we are looking at an either / or qualifying statement. To help explain the OrElse option on a report, we will continue to use the basic query listed in the XOR section and the related report.
In this instance, having soccer in Activity1 or Activity 2 or both is fine. The benefit is that the process short circuits if it finds soccer in Activity1 and does not have to continue on to evaluate Activity2.
In the outcome column we use the following expression:
=IIF((Fields!Activity1.Value = "Soccer") OrElse (Fields!Activity2.Value = "Soccer"), "1 or more soccer seasons", "Error--No soccer seasons")
Thus, we check Activity1 for the presence of "soccer". If soccer is NOT found in Activity1, we continue to Activity2 to check for the presence of Soccer. The below screen print shows how the 1st, 2nd, and 4th rows all have one or more instances of soccer and thus are evaluated to "True" amid results in the "1 or more soccer seasons" tag. However, row 3 has no instances of soccer, and thus is evaluated to False. This examples shows how the OrElse expression makes it quick and simple to check for the evidence of a value in multiple fields.
SSRS Conditional Logic - AndAlso
The third of our logical expressions is the AndAlso expression which acts in the opposite direction of the OrElse expression. AndAlso evaluates the left side of the expression first; if the expression evaluates to False, then further processing stops and the right side of the expression is not evaluated. However, if the left side of the expression evaluates to True, then the right side is evaluated and the results are returned based on the evaluation of the second expression. Thus the AndAlso results are evaluated as follows:
- If the Left side evaluates to FALSE, then processing stops and the AndAlso expression returns False
- If the Left side evaluates to TRUE, then processing continues to the Right
ride.
- If the right side evaluates to TRUE then the AndAlso expression returns TRUE
- If the right side evaluates to FALSE then the AndAlso expression returns FALSE
- If BOTH expressions are TRUE, then the AndAlso returns TRUE
Thus, the AndAlso expression is validating the truth of both statements. We will continue to use the basic query listed in the XOR section and the related report for the AndAlso example.
In this instance, we must have soccer in Activity1 AND Activity 2. The benefit is that the process will also short circuit if it finds something other than soccer in Activity1 and does not have to continue on to evaluate Activity2.
For the AndAlso example, the outcome column will contain the following expression:
=IIF((Fields!Activity1.Value = "Soccer") AndAlso (Fields!Activity2.Value = "Soccer"), "2 Soccer Seasons...Star", "1 or Less Soccer Seasons")
Thus, we check Activity1 for the presence of "soccer". If soccer is found in Activity1, we continue to Activity2 to check for the presence of "soccer". The below screen print shows how the 1st, 2nd, and 3rd rows all have just one instances of soccer and thus are evaluated to "False" which in turn displays "1 or less Soccer Seasons" tag. However, row 4 includes "soccer" in Activity1 and Activity2, and thus is evaluated to True. In turn, row 4 displays: " 2 Soccer Seasons.... Star".. This example shows how the AndAlso expression makes it quick and simple to check that a value exists in two fields.
Conclusion
In this tip we showed how 3 logical / bitwise expressions allow you to make advanced evaluation of two sets of values. The XOR expression provides the opportunity to evaluate that just one of two value checks returns true. No short circuit occurs with XOR; however if both sides of the expression are evaluated to true, XOR will actually also return False. To the contrary, both AndAlso and OrElse allow for the evaluation of two value comparisons, but both these expressions are short circuit operators and will stop processing depending on the outcome of the value 1st evaluation. All three of the operator keep a report designers from having to develop more complete IIF statements.
Next Steps
- Read about SSRS Expressions Tips and Tricks - https://www.mssqltips.com/sqlservertip/3011/sql-server-reporting-services-expressions-tips-and-tricks/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
