Subreport Help 2

[TimothyP]

I am trying to create a budget solution using Crystal Reports 8.5 and SQL server tables.

I have three Crystal Reports.

Report1 (Grouped by fund)
Report2 (Grouped by fund, subfund, and department)
Report3 (Grouped by fund, subfund, and department)

How would I go about writing a new Crystal Report that combines portions of the above reports in FUND order?

I am trying to use the following table to drive the sequence of subreports.

Driver Table:
Fund Subfund Dept
010 011 AD
010 011 BC
010 092 ND
025 344 AD
025 344 BC
120 505 AD
120 505 BC
120 510 AD
120 510 AS
Etc...

My goal:
1. Read Driver Table
2. Run Report1 for each fund
3. Run Report2 for each subfund/dept
4. Run Report3 for each subfund/dept

In other words...

Report1 (General Fund Report)
Fund: 010
Report1 output for fund 010

Report2 (Subfund/Dept ReportA)
Fund: 010
Subfund: 011
Dept: AD
Report2 output for dept AD only

Report3 (Subfund/Dept ReportB)
Fund: 010
Subfund: 011
Dept: AD
Report3 output for dept AD only

Report2 (Subfund/Dept ReportA)
Fund: 010
Subfund: 011
Dept: BC
Report2 output for dept BC only

Report3 (Subfund/Dept ReportB)
Fund: 010
Subfund: 011
Dept: BC
Report3 output for dept BC only

Report2 (Subfund/Dept ReportA)
Fund: 010
Subfund: 092
Dept: ND
Report2 output for dept ND only

Report3 (Subfund/Dept ReportB)
Fund: 010
Subfund: 092
Dept: ND
Report3 output for dept ND only

Report1(General Fund Report)
Fund: 025
Report1 output for fund 025

Report2 (Subfund/Dept ReportA)
Fund: 025
Subfund: 344
Dept: AD
Report2 output for dept AD only

Report3 (Subfund/Dept ReportB)
Fund: 025
Subfund: 344
Dept: AD
Report3 output for dept AD only

Etc...

If anyone knows a better way to do this please let me know.

Thanks!

 

[MisterMo]

Could you give a sample of the other tables involved here just to understand the structure and how they would link the the driving table.


-Mo

 

[TimothyP]

Reports 1 and 2 use the same single table.

Report 3 also uses just one table and the table driving Report 3 also has fund, subfund, and dept.

 

[MisterMo]

I don't think that you need all that much

Just the tables joind by fund and subfund should do the trick.

Then just add groups for fund, subfund and department.


all the data should fall into each group


-Mo

 

[TimothyP]

I'm not sure I understand your suggestion.

Am I using the driver table as the main report and reports 1, 2 and 3 as subreports?

 

[MisterMo]

I am not convinced that you need subreports, or at the most you could get away with just one.

just Create a report with the two table in it joined by fund and subfund,

add the required fields to the reports and group by fund, subfund and dept.

you can have some of the data in the group header to identify fund, and subfund

and then on the detail you should get the dept details.
depending on the result you might need to add a subreport here.


-Mo

 

[TimothyP]

Reports 1, 2, and 3 are three totally different reports. Reporting on different data elements. Reports 2 and 3 are grouped similarly, however they are independent reports.

I am trying to sequence the reports based on a table.

Maybe I am not explaining what I am trying to do properly.

Let me try to understand your suggestion and I will reply back.

Thanks-
Tim

 

[MisterMo]

Hi Tim,

Sorry but is a bit difficult trying to help without seeing the whole picture. Although the 3 reports are reporting on different elements, the elements are still coming from the same table, therefore by logic you could get away with less work.

Crystal allows you to manipulate data in many different ways by using groups and sections to format the report.

Is only a matter of finding the right approach.


-Mo

 

[TimothyP]

I appreciate you trying to help me. Unfortunately, I'm just not getting this approach. Can you provide a generic example using group names to help me understand how to combine different reports by adding additional groups?


My feeling is the reports are already complex enough with up to 4 groupings.

Isn't there a way to use a driver table in the main report and include subreports off the main report/driver table to determine the sequence?

 

[lbass]

If you want to stick with your original plan, you can group on fund, subfund, and department in your main report, and place Report 1 in the fund group header, and Reports 2 and 3 in GH#3--department group header_a and group_header_b respectively. Link Report 1 on the fund field, and reports 2 and 3 on fund, subfund, and department.

-LB

 

[MisterMo]

You didn’t post enough details of the two tables to give you a better example but based on what you have given:

Main report
Main table with Fund Subfund Dept and the rest of the fields
group by Fund Subfund and Dept

The first group header will hold your Fund info and the second group header the SubFund info.

The Details section will have Dept and the rest of the data.
Add a section to the details and insert a Subreport

On SubReport
Add Second table Group By Fund Subfund and Dept
Details section will have Dept and the rest of the data
Suppress all Group footers and Headers


If you need to get Differen Depts from the other table then
From Main Report go to Edit>SubReport Link
And link by By Fund Subfund

Else if you have another level after Depts eg. Drivers then
From Main Report go to Edit>SubReport Link
And link by By Fund Subfund and Dept.


Having 3 subreports virtually doing the same job is counter-productive especially if you have a large database eg. If you have 1000 Depts then the report will query the database 1000 times for the departments plus however many times the Fund and Subfund will change in the process.



-Mo