Complex, hierarchical data in subreports

[selvirino]

I am a CR newbie. I'm trying to create a report that contains many subreports, which link to SQL Server 2000 stored procedures as a datasource. Some of these subreports contain data collected from two tables with a one-to-many relationship (a parent table and a corresponding update table).

To make matters more complicated, the datasource for the parent report is unrelated to that of the subreports in question. This means I need to display data from a row in the parent table in a detail section of my subreport. In the same section, I need to display all the update records for the parent record.

This problem would be easily solved by using nested subreports, but I guess CR doesn't support that. I also tried using XML as a datasource, figuring I can express the hierarchical nature of my data in XML pretty easily, but CR 8.5 really doesn't support complex XML, at least that I can tell. I only seems to support one level of nesting in the XML, using row & column hints. My guess is that CR has to 'flatten' the XML out to use it, which means the data winds up in a table-like format for internal use by CR. I'd love to be wrong about this and get some advice on how to use XML as a datasource, since I can make that my 'standard' for producing reports.

Since I'm using stored procedures, I do not believe I can use data shapes or data cubes. Is there another way I can solve what must be a somewhat common problem?

Any help will be most appreciated!

 

[IdoMillet]

If you are looking for a recursive indented Parent-Child display, you could simply use the "Hierarchical Grouping
Options..." under the Report menu.

Cheers,
- Ido ixm7@psu.edu

 

[selvirino]

Unfortunately, I believe the 'hierarchical grouping' feature applies to the case where the report connects directly to a database, allowing a parent-child relationship to be estalished across tables. Since I'm getting an ADO recordset from a SQL Server 2000 stored proc, I don't think I have such a luxury. I'd love to be wrong about this and be able to provide the report the data it needs from a stored proc to use the hierarchical grouping, but I don't know if this is possible.

 

[IdoMillet]

Why not test it?

Cheers,
- Ido ixm7@psu.edu

 

[selvirino]

I wasn't able to work with Hierarchical groups for a bit since I got sidetracked on other tasks. I finally got to this today, and I can use hierarchical grouping options to partially satisfy my requirement. I can show the parent record in a group header and have the related children show in the detail section.

There is a problem that I can't quite figure out, though. I need to show multiple child types for the same parent in the same report. I can't figure out how to add multiple subgroups under the same parent group in Crystal without nesting the subgroups.

What I'd like to create is something like this:

System Manager: Joe
------------------
Operator: Fred Application: MicroBook
Operator: Nancy Application: CRS
Operator: Sarah Application: SMARP
Application: BART
Application: SAMS

Where there is no relationship between Operators and Applications.

Any advice will be very, very appreciated.