• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

Command or Query to link subreport to main report

Using Crystal Reports XI
We have a report that lists the IDs of all Salesmen that exist in the fields
"Inside" and "Outside" in the "Invoices" table.  The report is Grouped by
SalesmanID.

In the Detail section, we want a subreport that contains all invoices where
"Inside" OR "Outside" = SalesmanID.

We need to create a SQL command/Query for a subreport that receives the
SalesmanID from the main report as a link/parameter where either of two
columns contains the SalesmanID

Example:
SELECT * FROM Invoices WHERE (`Inside` = @SalesmanID OR `Outside` =
@SalesmanID)

@SalesmanID = Value from main report passed to the subreport command/query.

Invoice table illustration:
INV#      INSIDE      OUTSIDE      INVAMT
001      A      A      $1000
002      B      A      $2000
003      C      B      $3000

Report illustration:
Salesman A
INV#      INVAMT
001      $1000
002      $2000

Salesman B
INV#      INVAMT
002      $2000
003      $3000

Salesman C
INV#      INVAMT
003      $3000

Any help will be appreciated
0
a1doors1
Asked:
a1doors1
  • 2
1 Solution
 
frodomanCommented:
Create your subreport using "SELECT * FROM Invoices".  In your subreport create a new parameter named SalesmanID.  Use the select expert to add the filter to the formula so you have:

select * from invoices where ({table.inside} = {?SalesmanID} OR {table.outside} = {?SalesmanID}

In your main report, rt-click on the subreport and select Edit Links.  Link the {table.SalesmanID} field from your main report to the {?SalesmanID} parameter in the subreport.

One thing - you said you want this subreport in the details section?  That will duplicate the subreport for every record.  You probably want to place this subreport in the group header or footer instead of in Details.

hth

frodoman
0
 
a1doors1Author Commented:
We are still new to Crystal... We should have explained that the 'SalesmanID" is generated from an sql query command on the main report:
SELECT invoices.inside_salesman AS SalesmanID
FROM invoices
 UNION
SELECT invoices.outside_salesman AS SalesmanID
FROM invoices
This results in data:
A
B
C
We are using this data as the parameters for group heading on the main report.  This part is working perfectly as we only get 'SalesmanID's that exist in either of the two fields in table invoices.  We also created a sql query command to create a dataset 'invoices' on the subreport.  We are trying to figure out how to "pass" the "SalesmanID" to the subreport as selection criteria for table "invoices" to get the "Report Illustration" shown in previous post.  From the little that we know we can only set a parameter to 'look' at one field in the talble 'invoices' at a time.  I hope this makes some sort of sense.
0
 
a1doors1Author Commented:
We noodled on this some more here and finally figured it out.  Thanks Frodoman for the input
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now