Solved

Work Around for subreport in a subreport in a report

Posted on 2004-09-24
8
443 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
8 Comments
 
LVL 28

Expert Comment

by:bdreed35
Comment Utility
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
Comment Utility
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 22

Accepted Solution

by:
Ido Millet earned 125 total points
Comment Utility
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
 

Author Comment

by:wykzimme
Comment Utility
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
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.

 
LVL 22

Expert Comment

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

Cheers,
- Ido
0
 

Author Comment

by:wykzimme
Comment Utility
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 22

Expert Comment

by:Ido Millet
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

9 Experts available now in Live!

Get 1:1 Help Now