<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Adding an Else to your Switch

Published on
42,183 Points
25,383 Views
13 Endorsements
Last Modified:
Awarded
ValentinoV
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2016: MVP Data Platform
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function.

Two commonly-used functions in SQL Server Reporting Services are the IIF() and the Switch().  These are two functions of the Program Flow type, or Decision Functions as they are called on this MSDN page.

In case you're wondering why it's so difficult to find a function reference for the built-in functions of SSRS, it's because these are actually Visual Basic functions and Microsoft refers to those for any detailed explanation.  Their references are located at the bottom of this article.

Anyone who's done some programming most likely already knows the if <expression> then <some_code> else <other_code> statement.  If <expression> evaluates to true then <some_code> gets executed, else <other_code>  gets executed.

The IIF() works in the same way.  According to its description it:

Returns one of two objects, depending on the evaluation of an expression.

This is its definition:

Public Function IIf( _
    ByVal Expression As Boolean, _
    ByVal TruePart As Object, _
    ByVal FalsePart As Object _
) As Object

Open in new window


Here's a simple example.

=IIf(Fields!YearlyIncome.Value >= 600,"High","Low")

Open in new window


Using this expression, the "High" string is returned when the value of the YearlyIncome field is equal to or above 600, while the string "Low" is returned when the value is below 600.

Now have a look at the following example.  It has been nicely structured with indentation and line breaks to make reading easier.

=IIF
(
    Sum(Fields!LineTotal.Value) >= 100,
    "Violet",
    IIF
    (
        Sum(Fields!LineTotal.Value) < 25,
        "Transparent",
        "Cornsilk"
    )
)

Open in new window


As you see, it shows a nested IIF inside another one.  Imagine that there were several more nestings and that line breaks were not used by the coder.  Would be a nightmare to read, right?

That's why the Switch() was invented.  The description for the Switch function reads:

Evaluates a list of expressions and returns an Object value corresponding to the first expression in the list that is True.

And this is the function definition:

Public Function Switch( _
    ByVal ParamArray VarExpr() As Object _
) As Object

Open in new window


In Reporting Services, the VarExpr parameter is simply an even list of expressions and/or object references separated by commas.  Which comes down to something like this: Switch(<expr1>, val1, <expr2>, val2).

Here's a simple example:

=Switch
(
	Fields!State.Value = "OR", "Oregon",
	Fields!State.Value = "WA", "Washington"
)

Open in new window


This expression says that if the value for the State field is "OR" then the Switch function will return "Oregon", and so on...

Now, to get to the point of this article, the Switch function does not contain an ELSE part like the IIF does.

But I wouldn't be writing this if there wasn't a workaround, would I?  If you read the Switch's description closely, it says that it will return the first expression in the list that is true.  So each expression is evaluated in the order that they are passed to the function.  To get ELSE-like behavior we would need an expression that evaluates to True but only when all other expressions are False.  So, why not use True as expression?  It's the simplest expression that I can think of and it does the works!

Have a look at the following, it's a rewrite of the last IIF example mentioned earlier.

=Switch
(
    Sum(Fields!LineTotal.Value) >= 100, "Violet",
    Sum(Fields!LineTotal.Value) < 25, "Transparent",
    True, "Cornsilk"
)

Open in new window


So, which one do you think is the most readable?  The IIF, or the Switch?  These are only simple examples that I've been using, imagine situations with ten or more possibilities.  Well, I think you've got my point by now.

Thank you for reading this article, and remember: Happy Reporting!  Don't forget the little YES button on the way out ;-)

Valentino.

Originally appeared on my website: http://blog.hoegaerden.be/2009/09/14/adding-an-else-to-your-switch 

References
MSDN VS2008 VB.NET IIf Function
MSDN VS2008 VB.NET Switch Function
13
Comment
Author:ValentinoV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month