We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Crystal Report Sub Query

Medium Priority
876 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

Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you trying to pass the parameters from code to the subreport or do you want to do it through the sureport links?

mlmcc
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.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
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..
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc
You cannot even acccess the ParameterFields on the sub report. The Crystal Reports gives an exception on this call. This method will not work...
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
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...
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.