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

Work Around for subreport in a subreport in a report

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
wykzimme
Asked:
wykzimme
1 Solution
 
bdreed35Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
wykzimmeAuthor Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Is this a follow up to my message?  Can you clarify what you are asking?

Cheers,
- Ido
0
 
wykzimmeAuthor Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Simply link flights to requestes and requests to passengers.  
Then, group on:
1. flight
2. request
3. passenger.

Cheers,
- Ido
0
 
wykzimmeAuthor Commented:
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: 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.

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