Passing a Parameter to a Subreport

Question

User-1607658070 posted

Hi everyone, I have a Crystal Report with several Subreports embedded within the main report. These subreports are built from stored procedures that require parameters. Is there a way to pass parameters programatically to the subreport? I have tried the following code which causes an exception when I try to set the parameters for the subreports.

 

Private Sub displayReport(ByVal strReportType As String)

        Try

            Dim reportPath As String = Server.MapPath(System.Configuration.ConfigurationManager.AppSettings("DocsPath"))

            If dlReports.SelectedIndex > -1 Then
                Dim str As String = dlReports.SelectedValue.ToString
                reportPath = reportPath & dlReports.SelectedValue
            Else
                reportPath = reportPath & dlReports.Items(0).Value
            End If

            Dim dbConnection As String = ConfigurationManager.ConnectionStrings("DatabaseName").ConnectionString
            'Dim sqlCon As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings(dbConnection).ConnectionString)
            Dim sqlCon As New System.Data.SqlClient.SqlConnection(dbConnection)
            Dim myConnectionInfo As New ConnectionInfo

            myConnectionInfo.ServerName = ConStringSegment(sqlCon.ConnectionString, "Server=", ";", True)
            myConnectionInfo.DatabaseName = sqlCon.Database
            'myConnectionInfo.IntegratedSecurity = CBool(ConStringSegment(sqlCon.ConnectionString, "Trusted_Connection=", ";", True))
            myConnectionInfo.IntegratedSecurity = True

            reportDoc.Load(reportPath)
            reportDoc.DataSourceConnections(0).SetConnection(myConnectionInfo.ServerName, myConnectionInfo.DatabaseName, myConnectionInfo.IntegratedSecurity)

            Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo
            Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

            For Each myTable In reportDoc.Database.Tables
                myLogin = myTable.LogOnInfo
                myLogin.ConnectionInfo = myConnectionInfo
                myTable.ApplyLogOnInfo(myLogin)
            Next

            crViewer.ReportSource = reportDoc

            Select Case strReportType
                Case Is = strRP1
                    Dim pv As Integer = CInt(txtAd_ID.Text)
                    Dim pn As String = "@Ad_ID"
                    reportDoc.SetParameterValue(pn, pv)
                Case Is = strRP2
                    Dim pv As Integer = CInt(txtAd_ID2.Text)
                    Dim pn As String = "@Ad_ID"
                    'Dim pv1 As Integer = CInt(dlDe_ID.SelectedValue)
                    'Dim pn1 As String = "@De_ID"
                    reportDoc.SetParameterValue(pn, pv)
                    'reportDoc.SetParameterValue(pn1, pv1)
                    For Each rd As ReportDocument In reportDoc.Subreports
                        Dim pv2 As Integer = CInt(dlDe_ID.SelectedValue)
                        Dim pn2 As String = "@De_ID"
                        rd.SetParameterValue(pn2, pv2)
                    Next
            End Select

            'If reportType = REPORT_TYPE_ORDER Then
            '    If Not (Session("orderParam") Is Nothing) Then
            '        paramValue = CType(Session("orderParam"), Integer)
            '    End If
            'Else
            '    paramName = "@PurchaseOrderID"
            '    If Not (Session("purchaseOrderParam") Is Nothing) Then
            '        paramValue = (CType(Session("purchaseOrderParam"), Integer))
            '    End If
            'End If

            'reportDoc.SetParameterValue(paramName, paramValue)

            '-----
            'crViewer.EnableParameterPrompt = True
            'crViewer.EnableDatabaseLogonPrompt = True
            'crViewer.DataBind()
            '----

        Catch ex As Exception
            SetErrorLabel("Error: The report failed to load", ex.ToString)
        End Try

    End Sub 'Show the crystal report.

 
 I get the error

NotSupportedException was caught

Not supported within subreports. 

Answer 1

User-1607658070 posted

Hi everyone, I had a request for a solution to this problem. Here is how I solved the problem for me.

(1) Add the parameter that you require for the subreport to the main report. To do this open the Field Explorer window, right click Parameter Fields and select new. If you are using a stored procedure for the subreport use the same name for the parameter as you have used in the stored procedure e.g. @De_ID; Also ensure that the datatype for the parameter is compatible with the stored procedure e.g. Use number if the stored procedure parameter type is int.

(2) Once you have added your parameter right click the subreport and select Change Subreport Links... This will open the Subreport Links window. Scroll down the Available Fields Treeview in the top left of the window. Under Report fields locate the parameter you added in step one. Select the parameter and press the > button. Click the "Subreport parameter field to use:" dropdownlist and select your parameter from the list. Press the OK button. Now the subreport should use whatever value is in the parameter you added to the main report in step 1.

(3) You can populate the value of a reports parameter programatically by doing the following.

 

Dim pv As Integer = 1 'Parameter value
Dim pn As String = "@De_ID" 'Parameter name
reportDoc.SetParameterValue(pn, pv)

 Note you have to setup the Report Document object before you add parameters to it. See code above.

Good luck. Hope this helps

Answer 2

User-1986503153 posted

hi ernie99, I followed the step you mention but the report keep prompt me for the subreport parameters, and when I try enter the values manually I get the following error:

Error in File C:\DOCUME~1\WEB\ASPNET\LOCALS~1\Temp\Supplier {01CA777D-05B5-4AB2-9439-C34F59F976EE}.rpt:
Operation illegal on linked parameter.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: Error in File C:\DOCUME~1\WEB\ASPNET\LOCALS~1\Temp\Supplier {01CA777D-05B5-4AB2-9439-C34F59F976EE}.rpt:
Operation illegal on linked parameter.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[COMException (0x80000290): Error in File C:\DOCUME~1\WEB\ASPNET\LOCALS~1\Temp\Supplier {01CA777D-05B5-4AB2-9439-C34F59F976EE}.rpt: Operation illegal on linked parameter.] CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.GetPromptParameterFields(PromptingRequestInfo promptingRequestInfo) +0 CrystalDecisions.ReportSource.EromReportSourceBase.GetReportInfo(RequestContext reqContext) +472 [ParameterFieldException: Error in File C:\DOCUME~1\WEB\ASPNET\LOCALS~1\Temp\Supplier {01CA777D-05B5-4AB2-9439-C34F59F976EE}.rpt: Operation illegal on linked parameter.] CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e) +700 CrystalDecisions.ReportSource.EromReportSourceBase.GetReportInfo(RequestContext reqContext) +968 CrystalDecisions.CrystalReports.Engine.FormatEngine.GetReportInfo(RequestContext reqContext) +44 CrystalDecisions.ReportSource.LocalReportSourceBase.GetReportInfo(RequestContext reqContext) +132 CrystalDecisions.Web.ReportAgentBase.GetParameterFieldsForPrompting() +116 CrystalDecisions.Web.CrystalReportViewer.OnPreRender(EventArgs e) +906 System.Web.UI.Control.PreRenderRecursiveInternal() +86 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

any idea?

Answer 3

User-1607658070 posted
Hi SaeedKUD,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p><o:p> </o:p>

I had a quick look at the error you’re receiving. You should check that your sub-report does not contain parameters that you have not linked to the main report.

<o:p> </o:p>‘This message usually means there are extra parameters in the Crystal Report that are not being used.’<o:p></o:p>http://blogs.vandamme.com/kb/2005/09/imis_report_err.html<o:p></o:p>

 

All the best<o:p></o:p>

Answer 4

User-519393231 posted

I know it has been a while, but I am new to this and trying to execute a crystal report as well.  Is ReportDoc declared as a CrystalDecisions.Web.Report?  When I add the line reportDoc.Load(reportPath), I get error saying that Load is not a member CrystalDecisions.Web.Report.

Any help would be appreciated.