Subreport with query inserted into main report

Question

I have a crystal report with a bunch of parameterized fields that are being filled by code and displayed through CRViewer. I need to add a few sections in here to execute a query to return a dataset and display this in the same report. I am not sure if the best way to do this with a subreport. I am trying to add a subreport and then set the datasource of the subreport to my dataset, but I cannot get it to work.

Is this even the proper way or I am just doing something wrong?

Any help would be appreciated.

EDIT:

Example of what I am looking to do.

Lets say there is a customers table. I have FirstName LastName as parametirized fields displayed on the form along with address and basic customer information.

I want to place a subreport showing all orders this customer has made on the SAME report. The is an association table with multiple orders possible.

The report is being placed in a class that can be called by a VB.NET app. Therefore I cannot create the dataconnection for the subreport in the UI (or maybe I can and pass params later).

I am successfully populating the parameterized field by doing the following:

Dim myRpt As New RunReport.RunReport

myRpt.Load("myReport.rpt")

'Set Labels
 myRpt.SetParameterValue("@FirstName", strFirstName)

 CrystalReportViewer1.ReportSource = myRpt

A customerID would be available as well on to bind if needed to the association rows.

This will be one report I just need to display the subreport section to handle all possible orders. This is the part I am having the issue with.

Answer 1

Are you setting your main report's datasource like this?

Dim ds as new Dataset
sqlAdapter.Fill(ds, "Whatever");
oRpt.SetDataSource (ds)

If so, setting it on the subreport might look like this:

Dim oSubReport As ReportDocument
For Each oReportObject As SubreportObject In oRpt.ReportDefinition.ReportObjects  
  If oReportObject.Kind = ReportObjectKind.SubreportObject Then  
    oSubReportObject = CType(oReportObject, SubreportObject)  
    oSubReport = oRpt.OpenSubreport(oSubReportObject.SubreportName)  
    oSubReport.SetDataSource (ds)
  End If  
Next

---

I've read your update above and think I understand what you are going for. See if this works for you:

1) Create the main report. Give is whatever selection criteria you want (ex. CustName or CustID)
2) Create the sub report. Give it the selection selection criteria you want (ex. CustID)
3) Once you have inserted the subreport into the main report, right-click on the subreport object/box in the designer and pick "Change Subreport Links"
4) In the "Fields to link to" box, pick the value from the main report that will drive the subreport. (Ex. CustID)
5) In the "Subreport parameter field to use" dropdown, select the subreport parameter field that you want to link to the main report (Ex. CustID)

When you run the report, the subreport should only show data for the record that is displayed in the main report.

If those steps don't get you what you are looking for, please describe what functionality is missing.

P.S.: If you are trying to pull data only once (instead of once for main and again for sub), my question would be: why?

Answer 2

You will need to create 2 datasets and pass them both in as params. 1 will contain basic customer information and their key. The other will contain orders of all customers in the first dataset with the corresponding customer key. From there, you will bind the main report to the 1st dataset. Your subreport will tie to the second dataset. You will then use the customer key in the parent report as a parameter to the subreport and filter by that parameter. That will limit the subreport to only display orders for that customer key. This can all be done with very little to no code on the report. All code is done in the .Net object calling the report.

Here is an excellent site that will get you started if you haven't already seen it. It helped me out a lot : http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

Answer 3

I hope I understand this correctly.

Basically I think you are trying to create an invoice report where the header shows the customer information and the details shows the details of the invoice. If this is the case then I think the easiest way to accommodate this is by simply adding the customer fields to the dataset that contains the details of the invoice like below:

Data

Name           Address        City    State  InvSeq  Item   Amt
Peter Griffin  36 Spooner St  Quahog  RI     1       Item1  40.00
Peter Griffin  36 Spooner St  Quahog  RI     2       Item2  30.00
Peter Griffin  36 Spooner St  Quahog  RI     3       Item3  20.00
Peter Griffin  36 Spooner St  Quahog  RI     4       Item4  10.00

Then you simply add the customer info fields to either the report header (or a customer group head header if this report is for multiple customers) so that the data from the first record is the only one shown for the customer info. Then add the invoice detail fields to the details section so that they are repeated for each row as below:

Report Layout

*RH*:      Sold To:
           Peter Griffin
           36 Spooner St
           Quahog, RI

*Details*:   InvSeq   Item     Amt.
                1     Item1    $40.00
                2     Item2    $30.00
                3     Item3    $20.00
                4     Item4    $10.00

Hopefully I'm understanding the issue correctly and if so I hope this helps.