Issues with Custom function Code

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    One of my report users has generated a number of reports for use with a application we have.  On her computer in Visual studio she can run the reports with no problem.  However, when they get deployed to one of our Report servers, an exception occurs for each report with custom code functions. (Not sure if I am calling these correctly, but they are available under Reports --> Report Properties and then the Code tab).

     

    An example of the code is:

    Public Function GetSelections(ByRef prmListBox as object) as String

     Dim intCount as Integer

     Dim strSelectedValues as String

     strSelectedValues = ""

     For intCount = 0 To prmListBox.Count-1

      If strSelectedValues <> "" then

       strSelectedValues = strSelectedValues & ","

      End If

      strSelectedValues = strSelectedValues & "'" & prmListBox.Value(intCount) & "'"

     Next

     Return  strSelectedValues

    End Function

    This looks to me to be standard VB type code.  The error returned is:

    • An error has occurred during report processing.

      • The Value expression for the query parameter ‘@status’ contains an error: Attempt to access the method failed.

    I am sure this is something simple, but I am not overly familiar with this side of Reporting Services.  Any ideas what is causing this to occur?

     

    Jon Kehayias

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    I resolved this with a good bit of research last night.  The solution is:

     

    Original Code:

    Public Function GetSelections(ByRef prmListBox as object) as String

     Dim intCount as Integer

     Dim strSelectedValues as String

     strSelectedValues = ""

     For intCount = 0 To prmListBox.Count-1

      If strSelectedValues <> "" then

       strSelectedValues = strSelectedValues & ","

      End If

      strSelectedValues = strSelectedValues & "'" & prmListBox.Value(intCount) & "'"

     Next

     Return  strSelectedValues

    End Function

    Corrected Code:

    Public Function GetSelections(ByRef prmListBox as parameter) as String

     Dim intCount as Integer

     Dim strSelectedValues as String

     strSelectedValues = ""

     For intCount = 0 To prmListBox.Count-1

      If strSelectedValues <> "" then

       strSelectedValues = strSelectedValues & ","

      End If

      strSelectedValues = strSelectedValues & "'" & prmListBox.Value(intCount) & "'"

     Next

     Return  strSelectedValues

    End Function

     

    I figured I would repost back to share the knowledge if anyone else were to encounter later.

     

    Jon

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • sumit-417494

    Say Hey Kid

    Points: 686

    Or i think if you are getting a comma separated list from the parameter( multi value).

    you can use the =Join(Parameters!prmListBox.Value,",")

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply