[Webinar] Learn how to a build a cloud-first strategyRegister Now


Passing parameters to subreport in Crystal 10

Posted on 2009-02-20
Medium Priority
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

Expert Comment

ID: 23691278
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

ID: 23692721
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 101

Expert Comment

ID: 23695397
Are you changing the database in code?

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 23705408
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

Expert Comment

ID: 23710196
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

ID: 23719836
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.

Expert Comment

ID: 23720527
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:  http://www.kenhamady.com/vbref.html
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

stueyp34 earned 0 total points
ID: 23755993
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

ID: 33856241
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

ID: 33858013
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

867 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