locked
Passing a Parameter to a Subreport RRS feed

  • 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. 

    Tuesday, January 29, 2008 5:44 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2008 2:14 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2008 2:14 AM
  • 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?

    Monday, February 18, 2008 4:00 AM
  • 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>
    Wednesday, February 27, 2008 3:13 PM
  • 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.

    Wednesday, March 3, 2010 10:56 AM