?
Solved

Crystal Report Sub Query

Posted on 2011-04-22
9
Medium Priority
?
869 Views
Last Modified: 2012-06-27
I have a crystal report with a sub report embedded in the main report. I am populating the report using sql procedures. I am able to get the main report data. I do not know how to pass parameters to the procedures in the sub query. I tried setting the Sub report link to the relevant column in the main report. The report is not showing up.

My requirement is to be able to set parameters to the sub report query or another mechanism to see the header detail information in the report.

The code to get the header report data is attached.


ReportDocument report = new ReportDocument();
        report.Load(Server.MapPath("specificJobOrder.rpt"));

        ParameterField paramField = null;
        ParameterFields paramFields = null;
        ParameterDiscreteValue paramDiscreteValue = null;

        //paramField.Name = "@partyType";
        //paramDiscreteValue.Value = "1";
        //paramFields.Add(paramField);

        paramFields = report.ParameterFields;

        for (int paramIndex = 0; paramIndex < paramFields.Count; paramIndex++)
        {
            paramField = paramFields[paramIndex];
            if (paramField.Name == "@orderNo")
            {
                paramDiscreteValue = new ParameterDiscreteValue();
                paramDiscreteValue.Value = orderNo;
                paramField.CurrentValues.Add(paramDiscreteValue);
            }
        }

        ReportDocument jobOrderDetailsReport = report.OpenSubreport("jobOrderDetails.rpt");

        SetTableLocation(report.Database.Tables);
        CrystalReportViewer1.ReportSource = report;

Open in new window

0
Comment
Question by:narayanan_mahalingam
  • 5
  • 4
9 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35447526
Are you trying to pass the parameters from code to the subreport or do you want to do it through the sureport links?

mlmcc
0
 

Author Comment

by:narayanan_mahalingam
ID: 35447554
If I can pass it through the code, it would be great. I do not know other mechanisms of passing parameters to sub reports. If an equivalent mechanism solves my problem, I will take it any day.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35447642
Same method just assign the paramfields from the subreport

        ReportDocument jobOrderDetailsReport = report.OpenSubreport("jobOrderDetails.rpt");
        paramFields = jobOrderDetailsReport.ParameterFields;

        for (int paramIndex = 0; paramIndex < paramFields.Count; paramIndex++)
        {
            paramField = paramFields[paramIndex];
            if (paramField.Name == "@SUBREPORTPARAMETERNAME")
            {
                paramDiscreteValue = new ParameterDiscreteValue();
                paramDiscreteValue.Value = orderNo;
                paramField.CurrentValues.Add(paramDiscreteValue);
            }
        }

mlmcc
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:narayanan_mahalingam
ID: 35447910
What you say cannot be d one.
It is crashing in the line  paramFields = jobOrderDetailsReport.ParameterFields.
This cannot be done at the sub reports level..
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35449056
That is the method.  You may have to use a new variable for paramFields or set it NULL first.

mlmcc
0
 

Author Comment

by:narayanan_mahalingam
ID: 35449218
You cannot even acccess the ParameterFields on the sub report. The Crystal Reports gives an exception on this call. This method will not work...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35449368
There are samples in this zip file that do access subreport parameter fields.  I dont have a .Net environemtn so I cant test them or even really look at them

http://www.sdn.sap.com/irj/boc/sdklibrary?rid=/library/uuid/d0bf8496-2a81-2b10-95ac-b1f48d5b63f5

mlmcc
0
 

Author Comment

by:narayanan_mahalingam
ID: 35452558
I am able to set the sub report parameters with the call,

report.SetParameterValue("@orderNo", orderNo, "specificJobOrderDetails.rpt");

The complete code is as follows:

        ReportDocument report = new ReportDocument();
        report.Load(Server.MapPath("specificJobOrder.rpt"));

        ParameterField paramField = null;
        ParameterFields paramFields = null;
        ParameterDiscreteValue paramDiscreteValue = null;

        paramFields = report.ParameterFields;

        for (int paramIndex = 0; paramIndex < paramFields.Count; paramIndex++)
        {
            paramField = paramFields[paramIndex];
            if (paramField.Name == "@orderNo")
            {
                paramDiscreteValue = new ParameterDiscreteValue();
                paramDiscreteValue.Value = orderNo;
                paramField.CurrentValues.Add(paramDiscreteValue);
            }
        }

        report.SetParameterValue("@orderNo", orderNo, "specificJobOrderDetails.rpt");

But the subreport details are not showing up. One added observation is that I am not able to get the subreport show even in the Designer preview.. What am I missing?

I have configured the subreport to link the orderNo field via the 'Subreport Links' menu...
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35453102
Are you sure there is data?

If you are linking on the oreder number field why do you need to pass the parameter?
The link should be to the parameter not a field of the database.

mlmcc
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

579 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