Solved

Work Around for subreport in a subreport in a report

Posted on 2004-09-24
8
454 Views
Last Modified: 2008-01-09
What is the best way to deal with creating a report that contains a subreport (What I would love to then include a subreport in the subreport - which you cannot do in Crystal reports).
Or maybe there is a better way to do a report?

I have a table called flights that has one or more requests (requests table).  And each request has one or more passengers (passenger table).

How do I report for all flights the requests and passengers?

fltid links the flights and requests table and reqid links passenger table to requests table.
0
Comment
Question by:wykzimme
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 12147607
I am not sure that you even need any subreports.  It sounds like you just need to add your tables to one report, link them, flights to requests, and then requests to passengers.

You could then group on in crystal by flight id, and then reqid, and then put your passenger information in the detail section.

I can do more of a walkthrough if you need it.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12148042
I agree with breed35.  Based on your post, I don't see a reason for subreports.  Can you please provide more detail as to what you're trying to accomplish?

There certainly are valid reasons for using subreports.  For example, one of my previous clients wanted a single 'Master' report which contained a large number of charts that summarized completely unrelated sets of data.  Subreports were definitely the way to go.

Misuse of subreports or inefficient use of subreports, however, can have potentially disastrous performance effects for the report, your database and your network.  Its important to understand when AND where its appropriate to use them.

That being said, there is a workaround to the 'no subreports within subreports' issue for ON DEMAND subreports only.  The work around is to use hyperlinks to a new report file instead of using subreports.

Expample:

The Flights Report has a hyperlink to the Request Report for every Flight Group.  If a user wishes to see the Requests for a Flight Group, then the user can click the hyperlink, which will launch a new report window.

Within the ReqUests report, there is a hyperlink to the Passengers Report for every Request Group.  If a user wishes to see the Passengers for a Request, then the user can click the hyperlink, which will launch a new report window.

While this will work, I generally don't recommend it.  Instead, I would re-evaluate your report requirements and design.  By doing something like this, you've basically created a pseudo-application with significant overhead and that has maintenance issues.
0
 
LVL 23

Accepted Solution

by:
Ido Millet earned 125 total points
ID: 12154438
Assuming you need to use subreports, here's a technique that may work for you.

Imagine you have a report showing for each customer all their orders and all the line items.
You can't create it as a Main report for Customer and within that subreport for Orders, and within that subreport another subreport for line items.  This is because a subreport cannot conain another subreport.

As a side note, we can obviously use grouping levels and avoid the need for subreports in this case.  This is just for demonstration purposes. However, in many cases what seems like a need for a subreport can be removed by better SQL and grouping.

Now assuming we must separate Customers/Orders/Line Items into a main report and subreports, we can still achieve this using what I call a "middle-out" approach:

1. Make the middle layer (Orders) the main report.

2. Make the top layer (Customer) a subreport inserted in the group header of each Order. This subreport will return only one Customer for each order.  If needed, you can suppress it (using a shared variable) if the previous order had the same customer.

3. Make the low layer (Line Items) a subreport
in the group footer of the Orders. This subreport would show all line items for a given order.

Cheers,
- Ido
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:wykzimme
ID: 12219083
My example is the customers/orders/line items issue - I would love to know how to do it with grouping levels to avoid subreports.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 12219346
Is this a follow up to my message?  Can you clarify what you are asking?

Cheers,
- Ido
0
 

Author Comment

by:wykzimme
ID: 12219735
Yes, Ido this is a follow up to your message.  

You said "As a side note, we can obviously use grouping levels and avoid the need for subreports in this case.  This is just for demonstration purposes. However, in many cases what seems like a need for a subreport can be removed by better SQL and grouping."
I was just wondering how to use this method and its it better than the
1. Make the middle layer (Orders) the main report.
2. Make the top layer (Customer) a subreport inserted in the group header of each Order. This subreport will return only one Customer for each order.  If needed, you can suppress it (using a shared variable) if the previous order had the same customer.
3. Make the low layer (Line Items) a subreport
in the group footer of the Orders. This subreport would show all line items for a given order.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 12219795
Simply link flights to requestes and requests to passengers.  
Then, group on:
1. flight
2. request
3. passenger.

Cheers,
- Ido
0
 

Author Comment

by:wykzimme
ID: 12252706
It seems if you use the group method the child records will not be displayed if there aren’t any grandchild records.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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