Forgot password? | Forgot username? | Register

3 layers of nesting required

3 layers of nesting required

Has anyone discovered a way around the crystal limitation of not being able to put a Subreport inside a Subreport? The data structure I'm trying to report on is:
1. MAIN RPT = Event Module Record (Eg: An Exhibition)
2. SUBREPORT = Objects Attached to the Event (Catalogue records)
3. Table of Dimensions from within one of the Catalogue Records
                > ROW 1; Framed HxWxD mm
                > ROW 2; Unframed HxWxD mm

Unfortunately the csv’s to not contain correlating keys that would allow me to link both 2 & 3 directly to the Main report as separate(equal) Sub-reports.  If I flatten the table of dimensions using the table as text function or group the dimensions with Catalogue Records the dimensions data comes out as “Framed Unframed, H H, x W W, x D, D”  Has anyone managed to devise a formula to re-order this data?

One work-around suggested on the crystal forums was to use a hyperlink at the 3rd layer, however this isn’t really appropriate for the report where I need all the data visible at once.
Many Thanks -
Rowena

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

Hi Rowena.
You can use Groups as a "get out of jail free" card when it comes to subreport. It only gives you one extra layer, but that's usually enough.

When adding the tables to Crystal's Database Expert start with the Catalogue table first. Add the Events module second, with a left outer join from the Catalogue key to the Events key.

Once you've done this, try adding the details you need from Catalogue module to the Details section, including your subreport to Dimensions.

And then, Group By the Event (key, IRN, Title, whatever). You can then include all the event details in Group Header sections.

Hope this helps.

Mark

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

Thanks for the quick reply Mark,

The alternative starting table certainly makes all the difference.

After un-grouping the Attached Objects in the EMu Report window to allow grouping on Dimensions instead, this report is finally behaving nicely!

Eternally grateful... :)
Rowena

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

Excellent! Glad you got it sorted.

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

I spoke too soon, the solution to one thing always seems to reveal another problem...

By using the Attached Objects Ref Key from the AttachedObjects.csv as the primary table and linking out to the other tables from there (Events, Dimensions from Catalogue etc) it prevents grouping the Attached Objects Table with the Notes and Event Section.

Which means Object Notes and Event Sections can only be displayed in the report if they correlate to an Attached Object key. (However we often have data in the Notes or Event Section Column without an Attached Object)

Is there a different join type in crystal, or a different Grouping in EMu that would support display of the entire Object+Section+Notes table while still allowing the Dimensions Subreport from the Attached Objects?

screenshots of linking structure attached:

http://www.kesoftware.com/components/com_agora/img/members/421/Events-Linking-example.JPG

Thanks again,

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

ahmm bummer.  I was expecting you'd only want to report data from the Assoc Objects nested table if there is an object attached.  I think your needs have outgrown my skills.

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

Hi Rowena,

Are you running this report from Events?
If you are, when selecting your fields in EMu try grouping all the Catalogue fields in one group.
So in that one group (called ObjAttachedObjects in my screenshot) you have all the Catalogue fields you require plus another group for you Dimension fields, and you also have in that ObjAttachedObjects group, your Section and Notes fields from Events (ObjEventSection_tab and ObjAttachedObjectsNotes_tab).

In Crystal report, add both tables eevents_csv and ObjAttac_csv (for the group in EMu ObjAttachedObjects) with a left outer join for the eevents_key in both tables.
When you add the fields to the Details section in Crystal, create a subreport for your dimensions (this report should have for a 'Field to link to' : ObjAttac_csv.ObjAttachedObjects_key).

So in your main report you have all the Catalogue information (minus Dimensions) and all the Events information as well. And your Dimensions as a subreport.
I'm not sure what you are trying to achieve but this should produce a report with all the information repeated for each row you have in the EMu table 'Objects Associated With Event' (whether or not there actually is an object attached in the row).

Is that what you needed?

Christelle

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

http://www.kesoftware.com/components/com_agora/img/members/40/Database-Expert-Links.jpg

http://www.kesoftware.com/components/com_agora/img/members/40/Field-Explorer.jpg

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

final screenshot
http://www.kesoftware.com/components/com_agora/img/members/40/Report-fields.jpg

Administrator has disabled public posting. Please login or register in order to proceed.

Re: 3 layers of nesting required

Thanks for the reply Christelle, I had also arrived at this solution through a bit of trial and error over the weekend and hadn't yet posted the solution - so good to know I found the correct way in the end!

There appears to be a restiction in the order you apply groups in the EMu Reports window. I had the Attached Objects, their Notes and Sections in a single group in my original report. When I attempted to update the report to include a new group for dimensions inside this Objects group it wasn't possible.

In the end, I built the report from scratch - and by chance I applied the inner most grouping first (Dimensions) and then applied the Objects group over the top of them - which finally gave me the appropriate groupings
Cheers,
Rowena

Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info

Board Stats
 
Total Topics:
601
Total Polls:
0
Total Posts:
1362
User Info
 
Total Users:
830
Newest User:
Hillery
Members Online:
1
Guests Online:
234