Passing parameters to subreport in Crystal 10

Posted on 2009-02-20
Last Modified: 2012-05-06
I have a very simple Crystal Report, designed in Crystal 10 which selects its data from an MSSQL stored procedure ("Test"), which requires 1 parameter - "@Num". This stored procedure simply multiplys the parameter by 3 and selects back the result as "ReturnNum".
There is also an on-demand sub-report within this report which does something very similar - selects its data from a separate stored procedure ("Test2") which requires 1 parameter "@Num2" and selects back a calculation result as "ReturnNum".
The subreport is linked to the main report by "Test;1.ReturnNum" from the main report being linked to the "?@Num2" parameter of the subreport.

The idea of the report is that the user supplies the initial parameter ("@Num") and the main report will display the result of the calculation returned from the "Test" stored procedure (as the subreport caption). When the user clicks the on-demand subreport, this result should be passed to the subreport which should display the result of the "Test2" stored procedure, based on this parameter.

The above all works fine from within crystal itself (using the preview button), however when attempting to run the report from within a CrystalReportViewer control within our web app, the report asks for the "@Num2" parameter for the subreport.. It's as if the subreport link has been broken somehow. Does anyone have any ideas?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        oRpt = New ReportDocument



        Catch ex As Exception


        End Try


        Dim ci As New CrystalDecisions.Shared.ConnectionInfo

        With ci

            .ServerName = "localhost"

            .DatabaseName = "MyDatabase"

            .UserID = "MyUser"

            .Password = "MyPassword"

        End With


        ' We are applying logon information to the main report at this stage.

        Dim li As CrystalDecisions.Shared.TableLogOnInfo

        Dim tbl As CrystalDecisions.CrystalReports.Engine.Table

        ' For each table apply connection info.

        For Each tbl In oRpt.Database.Tables

            li = tbl.LogOnInfo

            li.ConnectionInfo = ci


            tbl.Location = tbl.Location

            tbl.LogOnInfo.ConnectionInfo.ServerName = ci.ServerName



        'Loop through all the report objects and locate subreports.

        'If a subreport is found then apply logon information to

        'the subreport.

        Dim obj As CrystalDecisions.CrystalReports.Engine.ReportObject

        Dim subobj As CrystalDecisions.CrystalReports.Engine.SubreportObject

        For Each obj In oRpt.ReportDefinition.ReportObjects

            If (obj.Kind = CrystalDecisions.[Shared].ReportObjectKind.SubreportObject) Then

                subobj = CType(obj, CrystalDecisions.CrystalReports.Engine.SubreportObject)

                ' For each table apply connection info.

                Dim Subli As CrystalDecisions.Shared.TableLogOnInfo

                Dim Subtbl As CrystalDecisions.CrystalReports.Engine.Table

                For Each Subtbl In oRpt.OpenSubreport(subobj.SubreportName).Database.Tables

                    Subli = Subtbl.LogOnInfo

                    Subli.ConnectionInfo = ci


                    Subtbl.Location = Subtbl.Location

                    Subtbl.LogOnInfo.ConnectionInfo.ServerName = ci.ServerName


            End If


        oRpt.SetParameterValue("@Num", 3)


            'CrystalReportViewer1.ParameterFieldInfo = ParamFields

            CrystalReportViewer1.ReportSource = oRpt

            CrystalReportViewer1.DisplayGroupTree = False


            'CrystalReportViewer1.RefreshReport () 

        Catch ex As Exception


        End Try

    End Sub

Open in new window

Question by:stueyp34
    LVL 4

    Expert Comment

    I had the same problem a few years back - the report would work fine in Crystal, but would prompt for the subparm when running within our ERP's crystal runtime...  I can't remember exactly how I fixed the problem, but I KNOW that the solution was either in the Subreport Links box, or the Subparm's Edit box.
    In comparing old versions with new (SourceSafe is wonderful...), the only changes I can spot are:
    1.  I changed the name of the subreport's parameter, and re-established the subreport link.
    2.  I edited the parameter in the subreport and changed "Default Value" to blank, and changed the "Prompt Text" from blank, to a period (".").  If I find anything more specific, I'll respond.

    Author Comment

    Thanks for the response.

    I've tried changing the name of the parameter in the subreport to "@Num2a", but when running the report through the web app, it still asks me for the value of the "@Num2" parameter on the subreport (this is still the name of the parameter within the stored procedure). I've also tried setting the prompt text to a string ("."). There is no default value set, I cant see how to set it to blank so I presume this is how it is already set.

    LVL 100

    Expert Comment

    Are you changing the database in code?


    Author Comment

    Yes, within the code snippet above. The code loops through all items within the tables collection and applies the logon information to each. This loop also occurs for each subreport within the main report
    LVL 4

    Expert Comment

    Have you tried adding the @num2 parameter to your VB just to see what would happen?

    oRpt.SetParameterValue("@num2", 3)

    You may be able to satisfy the num2 prompt this way, and still have the report pass the actual correct value to the subprogram...  I've seen stranger things...

    Author Comment

    I had the same thought before. If I attempt to set the parameter as in your example, I get an 'invalid index' exception. I did notice that this SetParameterValue method can also accept the name of a sub-report, so I also tried the following:

    oRpt.SetParameterValue("@Num2", 3, "SubReport")

    ("SubReport" being the imaginative name of the sub-report!). However, this gives an exception stating "Index was outside the bounds of the array.", so I had no luck with either approach.

    This approach may, however, be a red herring - if I run the report as before, so that it prompts the user to enter the @Num2 parameter for the sub report, it then proceeds to use this number entered as the parameter, and not the value that should be passed from the main report - I imagine it may behave like this using the above approach too.
    LVL 4

    Expert Comment

    All I can suggest at this point is that you consider buying Ken Hamady's "Quick Reference to Crystal Reports in VB".  I realize that you are not actually using VB, but the syntaxes of all the parameter information is identical to your web code.
    Here's the url:
    It's kind of a crap shoot, but it only costs 16 bucks.  I bought it 5 years ago for a project I was having trouble with, and the answers were right there.  Plus, once you buy it, Ken will answer questions you may have directly.  (Sorry - my copy of this pamphlet is long lost...)

    Accepted Solution

    Having not figured out this conundrum I have since redesigned my report to not rely upon a 2nd stored procedure within a sub-report. It's a little dissappointing to have had to do this however. Being more of a T-SQL/VB programmer than a crystal report writer, the ability to use stored procedures within reports is potentially very powerful and the above example would be useful in many ways

    Expert Comment

    Would you believe that I became a Premium Member of Experts Exchange this afternoon, solely for the purpose of viewing your solution?  How disappointing to find out that you had the identical problem to what I am having, but never found a solution!  LOL.

    I don't suppose there have been any further developments since?

    Author Comment

    Just to add extra information to this post. I have since revisited this problem and we have been forced to upgrade to Crystal 2008 to solve it. The exact example above works with no problem in Crystal 2008 so I presumed it to be a bug in the older version

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now