SQL Custom Report – OrElse function

I consider myself slightly above novice when it comes to writing SQL reports in Visual Studio.  I can generate simple select statements, join entities, create case statements, do some date conversions, etc.  Today, I needed to filter a particular field that I added to a table.  I needed the filter to look at two potential values and then return Yes or No if either of the values were found.  There are potentially several ways to get the result needed and here is the one I used:
 
There is a nice little Operator in SQL Reports named OrElse.  I selected to use this operator to check two field values in my Expression for the field property in the report.  Below are the steps I took.  I currently use Visual Studio 2005.
  1. Right-click the field on the Report from the Layout tab
  2. Select Expression
  3. Add the IIF statement with a nested OrElse
    1. IIF(castAsString(Field.Value) = "The Desired Value" OrElse (field.value = theDesiredValue), "True Value", "False Value")

    2. Below is the expression from the report

    3. =IIf(cstr(Fields!vair_IsInterface.Value) =

      "True" OrElse (Fields!CFPCategory.Value = 4), "Yes","No")

One additional function needed for this query was a string conversion for the value retrieved from the SQL statement.  Cstr is the report function to convert a value to a string so I applied that function to the vair_IsInterface to convert the value to a string so I could compare it to the string value of "True"

I hope you were able to follow this and you find it helpful when writing reports.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s