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.

Am I doin this right? SELECT / ExecuteNonQuery statement

I am working on a web service method that will return weather or not a page url is stored in the database but the ExecuteNonQuery keeps returning -1. I was just wondering if i was doing anything wrong or why the ExecuteNonQuery method does not return a value of 1 or more indicating that the pageurl exists in the database? I have tried using the SQLDataReader as well to no effect and I have verified that SELECT statement refers to valid table and field names. Any help or pointers would be appreciated. I'm still kind of a newb when it comes to db programming.

1 <WebMethod()> _
2Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
45 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
78Dim cmdAs String9 cmd ="SELECT [" & Constants.SourceFieldName &"] "10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase
1516 conn.Open()
1718Dim existsAs Boolean =False19 exists = (C.ExecuteNonQuery > 0)
2021 conn.Close()
22 C.Dispose()
23 C =Nothing24 conn.Dispose()
25 conn =Nothing
2627 Return exists
28End Function29

ExecuteNonQuery:Runs theAdomdCommand without returning any results.

For the purpose I understand, you can use ExecuteReader or ExecuteScalar if you required only one column value is return.

I think you have to use. ExecuteScalar.

Please make sure you click the Answer button if this is true answer.

Regards.

|||

In addition to what the prior person mentioned, if all you want to know is that the record exists then query for a count. SELECT COUNT(primaryKey) FROM tblName WHERE .....<add all your where conditions here>. Then you can use ExecuteScalar - much more efficient.

|||

<WebMethod()> _
2 Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
4
5 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
7
8 Dim cmdAs String

cmd="IF EXISTS("
9 cmd &="SELECT [" & Constants.SourceFieldName &"] "
10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "
11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"

cmd &= ") SET @.RETVAL=1 ELSE SET @.RETVAL=0"
12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase

c.Parameters.Add("@.RETVAL", SqlDbType.Int).Direction = ParameterDirection.Output

15
16 conn.Open()
17
18 Dim existsAs Boolean =False

c.ExecuteNonQuery
19 exists = c.Parameter("@.RETVAL").Value
20
21 conn.Close()
22 C.Dispose()
23 C =Nothing
24 conn.Dispose()
25 conn =Nothing
26
27 Return exists
28 End Function

Using the EXISTS allows SQL Server to stop retrieving records when it finds the very first record that matches, instead of having to find them all. This also should force SQL Server to optimize it's query plan.

I've also changed the code from the above examples to use an output parameter instead of returning a value as a resultset. This is a more efficient way to return a simple result as well.

|||

Thanks for the tips every one, they helped me greatly!