Solved

Crystal Reports group header parent/child problem

Posted on 2014-02-28
11
1,735 Views
Last Modified: 2014-03-03
I need some help with my first Crystal Report.

The group header shows as the first record of the group instead of the parent record that the child records are based on.

I have attached a .pdf identifying my challenge (I think).  Also included the .xls and .rpt I am working with.
Report2.rpt
SMARTSHEET.xls
Notes.pdf
0
Comment
Question by:rjthomes
[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
  • 5
  • 5
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39896320
Can you explain the parent to child relation?
How do I know which are parent rows?

mlmcc
0
 

Author Comment

by:rjthomes
ID: 39896340
Thanks for the inquiry...  I apologize in advance if my terminology is a little off but here it goes.

You can't really identify if a record is a parent but you can identify if any record is a child.

If a record has a number in the "__row_parentRowNumber" field then it is a child of that row number.

If there is not a number in that field then it is possibly a parent record but not necessarily.

Does that help?
0
 
LVL 35

Expert Comment

by:James0628
ID: 39897112
I have only looked at the PDF file.

 As I understand it, you have a group and you want the group header to show a value (the name) from one specific member of that group (the parent for the members of that group), instead of showing the value from each individual group.

 You said that if a record has a number in __row_parentRowNumber__, it is a child.  Otherwise, it may be a parent.

 But in the PDF file, you say that you want "City Of Palm Desert" in the header, implying that that is supposed to be the parent.  But that row has a 1 in __row_parentRowNumber__, which supposedly means that it's a child, not a parent.  It looks like maybe "Documents" should be the parent, but I can't be sure, because you put a note over where __row_parentRowNumber__ would be, so I don't know if there's a number there or not.


 Ignoring all of that for the moment, if you can come up with a test that will identify the parent records, you might be able to use something like the following.

 Let's say that you could identify a parent (or, at least, a "non-child") using:
__row_parentRowNumber__ = 0

 You could create a formula like the following (call it parent_name):
if {table.__row_parentRowNumber__} = 0 then
  {table.name field}
else
  ""

 Replace the field names with your actual field names, of course.
 That formula would only produce the name on the "parent" records.  It would produce an empty string on the child records.

 Then you could use a formula like the following to display the parent name:

Maximum ({@parent_name}, {group field})

 {group field} would be the group above the one with "Documents", "City of Palm Desert", etc.  In the PDF file, it looks like CAT_ENTITLEMENT and CAT_PURCHASE_SALE are part of a group above that one, so you'd probably use that field.

 James
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rjthomes
ID: 39897293
James,

Thank you for your input so far.  I hope you can continue to be patient with me (I am such an amateur).

I took a look at my notes.pdf and it is correct (as I see it).

As part of CAT_ENTITLEMENT (group)

"Documents" is row number 1 and happens to be the parent of "City of Palm Desert".  It is not a child of anything so the parent row = ""

"City of Palm Desert" is row number 2 with a parent row of 1 (child of "Documents").

"City of Palm Desert" is the parent of...

Fee Schedule
Tentative Tract Map Application
Environmental Assessment Form
General Plan Amendment Application
Change of Zone Application
Architectural Review Application

but... "Fee Schedule" appears as the header of that group rather than "City of Palm Desert".

I chose "City of Palm Desert" for this discussion because it is both a parent and a child.

I am not sure that what you have offered so far quite gets me there.

My ultimate goal is to show like a summary...

Documents  
     City of Palm Desert
          Fee Schedule
          Tentative Tract Map Application
          Environmental Assessment Form
          General Plan Amendment Application
          Change of Zone Application
          Architectural Review Application


Does that help?  Any thoughts?

Chad
0
 
LVL 35

Expert Comment

by:James0628
ID: 39897978
I just assumed that you wanted the top level parent for all of the records in the first group (CAT_ENTITLEMENT).  Trying to get the immediate (?) parent for each sub-group complicates things.

 What are the basic rules?  If something is a parent, you want to show its name, but if it's not a parent, you want to show its immediate parent's name?  For example, "Documents" and "City of Palm Desert" are parents, so you show their names on those lines.  But the children of "City of Palm Desert" are not parents, so you want to show their parent's name ("City of Palm Desert")?

 Could there be more levels of parent-child?  For example, could one of the children of "City of Palm Desert" also be a parent, with its own children records?  If so, does the child show its name, because it's also a parent, or does it show "City of Palm Desert"?  What about its children?  What do they show, their parent's name, or "City of Palm Desert" (their grandparent)?

 Are the parents and children always in that order (parent, then children)?  For example, in the PDF, you have "City of Palm Desert", and then its children.  Will it always be like that?

 Could there be more than one "significant" parent (like "City of Palm Desert") within a single main group?  For example, you have "City of Palm Desert" and some children in the CAT_ENTITLEMENT group.  Could there be more records in the CAT_ENTITLEMENT group that include another parent record (eg. "City of Tomorrow"), with its own children?

 If you could have more than one set of parent-child records in the same main group, would they be in order (parent 1 and its children, parent 2 and its children, etc.), or could the records be mixed up (eg. parent 1, children for parent 1 and 2, parent 2, children for parent 1 and 2)?

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 39897996
OK, after I asked all of those questions, I really looked at your "ultimate goal" for the first time.  In that post, it _looks_ like you're basically just trying to indent each child level a bit more, as opposed to trying to display the parent's name (which you have to get from a different group).

 Is that true, or is there more to it than that?  If you're basically just trying to indent the records, based on the parent-child level, we may be able to come at this from a different direction.

 James
0
 

Author Comment

by:rjthomes
ID: 39898016
James,

Ok...  Lots of thoughts.

At the end of the day I am simply trying to display corresponding parents and children in a summary (with indents on each new level).

Like:

Documents  
     City of Palm Desert
          Fee Schedule
          Tentative Tract Map Application
          Environmental Assessment Form
          General Plan Amendment Application
          Change of Zone Application
          Architectural Review Application

Children of "City of Palm Desert" can be parents also.  It might look like this.

Documents  
     City of Palm Desert
          Fee Schedule
          Tentative Tract Map Application
               grand child 1
               grand child 2
                    great grand child 1
          Environmental Assessment Form
          General Plan Amendment Application
          Change of Zone Application
          Architectural Review Application
     City of Tomorrow
          child 1
               grand child 1
                    great grand child 1
etc.....

"Documents" is a top level record because it does not have a parent.

"CAT_ENTITLEMENT" is simply a field entry that the above are grouped by.

I don't mean to state what is probably obvious to you...

I believe that your understanding in your Post 2014-03-01 at 16:56:26 is accurate.

Chad
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 39898869
OK, so when you have parent, child, grand child, etc., are the records in the correct order?  For example, where you have "Tentative Tract Map Application", "grand child 1", "grand child 2" and "great grand child 1", do you already have the records sorted/grouped in that order, or is getting them in the correct order part of the problem?

 And, completely switching tracks here, have you looked at hierarchical grouping?  I didn't think of it earlier because I have never used it, but it just occurred to me that it might be exactly what you're looking for.  You can go Report > "Hierarchical Grouping Options" and look at the options, and check the Help for it.

 James
0
 

Author Comment

by:rjthomes
ID: 39900702
James,

The hierarchical grouping was exactly what I needed.

See attached.

Thank you for your help and patience.

Chad
Hierarchical-example.pdf
0
 

Author Closing Comment

by:rjthomes
ID: 39900706
Outstanding patience and guidance.
0
 
LVL 35

Expert Comment

by:James0628
ID: 39901772
Great.  I just wish I'd thought of it earlier, but, like I said, I've never used it, so it just didn't occur to me.  But at least we got there eventually.  :-)

 James
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

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…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

752 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