Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Work Around for subreport in a subreport in a report

Posted on 2004-09-24
8
Medium Priority
?
459 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 500 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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