Command or Query to link subreport to main report

Posted on 2007-08-07
Last Modified: 2008-01-09
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

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

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

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

Invoice table illustration:
001      A      A      $1000
002      B      A      $2000
003      C      B      $3000

Report illustration:
Salesman A
001      $1000
002      $2000

Salesman B
002      $2000
003      $3000

Salesman C
003      $3000

Any help will be appreciated
Question by:a1doors1
    LVL 42

    Accepted Solution

    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.



    Author Comment

    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
    SELECT invoices.outside_salesman AS SalesmanID
    FROM invoices
    This results in data:
    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.

    Author Comment

    We noodled on this some more here and finally figured it out.  Thanks Frodoman for the input

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now