An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.