We help IT Professionals succeed at work.

Using ASP and ssrs 2008 report execution service

KSUF
KSUF asked
on
The following code is not sending parameters to the report which resides on a report server.
Sub RenderReport()
        'Private wsre As New finreporting.ReportExecutionService()
        wsrs = New finreporting.ReportingService2005()
        wsre = New finreporting.ReportExecutionService()

        wsre.Credentials = System.Net.CredentialCache.DefaultCredentials
        wsre.Url = "http://finreporting:8080/reportserver/ReportExecution2005.asmx?wsdl"

        wsrs.Credentials = System.Net.CredentialCache.DefaultCredentials
        wsrs.Url = "http://finerporting:8080/reportserver/ReportService2005.asmx?wsdl"
        'Render Arguments
        Dim format As String = Nothing
        Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
        Dim extension As String = Nothing
        Dim mimeType As String = Nothing
        Dim encoding As String = "en-us"
        Dim warnings As finreporting.Warning() = Nothing
        Dim streamIDs As String() = Nothing

        If rbPDF.Checked = True Then
            reportfilepath = "/DonorRelations/FundContactsAndFinancialsPDF"
            format = "PDF"
            extension = "PDF"
            mimeType = "application/pdf"
            filestreamfilepath = "c:\inetpub\wwwroot\ScholarshipContactsAndFinancials\appfiles\FundReportPDF.pdf"
            fileattachmentname = "attachment;filename=FundReportPDF.pdf"
        Else
            reportfilepath = "/DonorRelations/FundContactsAndFinancials"
            format = "Excel"
            extension = "xls"
            mimeType = "application/excel"
            filestreamfilepath = "c:\inetpub\wwwroot\ScholarshipContactsAndFinancials\appfiles\FundReportExcel.pdf"
            fileattachmentname = "attachment;filename=FundReportExcel.pdf"
        End If


        ' Prepare report parameter.
        Dim parameters(4) As finreporting.ParameterValue

        parameters(0) = New finreporting.ParameterValue()
        parameters(0).Name = "fundcoll"
        parameters(0).Value = tbCollege.Text.ToString
        parameters(1) = New finreporting.ParameterValue()
        parameters(1).Name = "funddept"
        parameters(1).Value = tbDepartment.Text.ToString
        parameters(2) = New finreporting.ParameterValue()
        parameters(2).Name = "funds"
        parameters(2).Value = tbFund.Text.ToString
        parameters(3) = New finreporting.ParameterValue()
        parameters(3).Name = "fundopenattrb"
        parameters(3).Value = tbFundStatus.Text.ToString

        Dim credentials As finreportingReportExecution.DataSourceCredentials() = Nothing
        Dim showHideToggle As String = Nothing
        Dim result As Byte() = Nothing

        Dim historyID As String = Nothing
        Dim reportHistoryParameters As finreportingReportExecution.ParameterValue = Nothing

        Dim execInfo As New finreporting.ExecutionInfo()
        Dim execHeader As New finreporting.ExecutionHeader()
        Dim SessionId As String



        wsre.ExecutionHeaderValue = execHeader

        execInfo = wsre.LoadReport(reportfilepath, historyID)
        wsre.SetExecutionParameters(parameters, "en-us")
        SessionId = wsre.ExecutionHeaderValue.ExecutionID

        File.AppendAllText(ErrorFile, vbCrLf + Today.ToString + "@" + TimeOfDay.ToString _
                   + " Setting Parameters")
        File.AppendAllText(ErrorFile, vbCrLf + "P0=" + parameters(0).Value.ToString)
        File.AppendAllText(ErrorFile, vbCrLf + "P1=" + parameters(1).Value.ToString)
        File.AppendAllText(ErrorFile, vbCrLf + "P2=" + parameters(2).Value.ToString)
        File.AppendAllText(ErrorFile, vbCrLf + "P3=" + parameters(3).Value.ToString)
        'File.AppendAllText(ErrorFile, "SessionID: {0}" + wsre.ExecutionHeaderValue.ExecutionID + vbCrLf)


        Try
            result = wsre.Render(format, devInfo, extension, mimeType, encoding, warnings, streamIDs)
            execInfo = wsre.GetExecutionInfo()
            Dim stream As FileStream = File.Create(filestreamfilepath, result.Length)
            stream.Write(result, 0, result.Length)
            Response.Clear()
            'Response.Redirect
            Response.Buffer = True
            Response.AddHeader("Content-Disposition", fileattachmentname)
            Response.ContentType = mimeType
            Response.BinaryWrite(result)
            Response.Flush()
            Response.Close()
            stream.Close()
        Catch e As Exception
            File.AppendAllText(ErrorFile, vbCrLf + Today.ToString + "@" + TimeOfDay.ToString _
                               + " Catch Block in Render")
            File.AppendAllText(ErrorFile, e.Message + vbCrLf)
        End Try
    End Sub
Comment
Watch Question

Author

Commented:
I know there are values in the text boxes for the parameters, but the error in the catch block says: This report requires a default or user-defined value for the report parameter 'fundcoll'. To run or subscribe to this report, you must provide a parameter value. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterValueNotSetException: This report requires a default or user-defined value for the report parameter 'fundcoll'. To run or subscribe to this report, you must provide a parameter value.
Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.


http://support.microsoft.com/kb/971068
Commented:
Additional investigation has uncovered the following dilemma: in the report there is a multi-select parameter option. You can select an ‘O’, ‘P’ or both (Which I thought rs combined as a string like ‘O,P’)
No matter how I passed the parameter from the asp side (‘O,P’ or ‘OP’ or single quotes or double quotes etc), I could never get it to work until I included both the O and P as a single selection in my parameter in the report. In other words, report parameters are now ‘O’, ‘P’ and ‘OP’ with no multi-select needed. This works for me in this case because there are so few choices.
I’m wondering now about the multi select parameters…is there a way to use them with an ASP front-end?

Author

Commented:
Another case happens when parameters may contain trailing blank spaces in the ASP but available parameter values in the report do not, or vice versa. This causes the input parameter from ASP to not match up with available values from the report and caused a null value error. Make sure values are timmed from the ASP and SSRS side.