[RESOLVED] nesting information sources

[obscuregirl]

Hi

I'm having problems creating a complex report.

My report shows details of Research Grants within an organisation. Each Research Grant has zero or many Applicants. Each Applicant can belong to zero or many Units. I want to be able to display the information as follows:

Research Grant details
Applicant1, Unit1, Unit2, Unit3
Applicant2, Unit1
Applicant3, Unit1, Unit2, Unit3

I am using three different views to bring back the information, one for the Research Grant, one for its Applicants and one for the Units an Applicant belongs to.

I display the Research Grant details in the main report and the Applicant details for each Grant in a subreport. I am having trouble displaying the Unit information for each Applicant as Crystal does not allow me to put a subreport within a subreport. I have tried various formulas but these produce incorrect results.

Please can anyone suggest anything?
Thanks!

[brucevde]

I am using three different views to bring back the information,

Is there a reason you are using 3 views. It seems your query would be a simple 3 table join, which would get you the data needed in the format you need.

[obscuregirl]

It was the only way I could think of doing it! I'll try putting together one view for all of it and see how it goes. Thanks.

[obscuregirl]

Is there a reason you are using 3 views. It seems your query would be a simple 3 table join, which would get you the data needed in the format you need.

I've looked at changing to just one view but I don't think this will be possible because each of my views already join several tables within them. I think if I tried to join all of them together it would produce incorrect results.

[Mark Gambo]

I've looked at changing to just one view but I don't think this will be possible because each of my views already join several tables within them. I think if I tried to join all of them together it would produce incorrect results.

Why not create a temporary table or use a Union?

[obscuregirl]

Why not create a temporary table or use a Union?

I didn't really want to use temporary tables because of the extra overheads. I've managed to condense the three views down to two and have managed to sort it out by putting most of the info in the main report and the rest in a subreport.

Thanks for your help!

[shakti5385]

Why not create a temporary table or use a Union?

I never suggest any one to create temporary table for making the report, use the linking and be the linking expert in the CR.
Temporary table is not the batter solution for making the report because here there is need to fill the table every time when you are calling the report and need to delete the data again.
So try to avoid the temporary table if possible.