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.
- Right-click the field on the Report from the Layout tab
- Select Expression
- Add the IIF statement with a nested OrElse
-
IIF(castAsString(Field.Value) = "The Desired Value" OrElse (field.value = theDesiredValue), "True Value", "False Value")
-
Below is the expression from the report
-
=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.
Donna Edwards 