Parent Child Relationships and Ad-hoc Reports

Parent Child Relationships in Domains / Ad-hoc

What is a Parent Child Relationship?

This concept is well explained here: http://msdn.microsoft.com/en-us/library/aa198072%28v=sql.80%29.aspx

Jaspersoft Example

Suppose you have an employee table like the one shown below. The idea here is that every employee has a supervisor except for Sheri Nowmer who is the president (her supervisor id is 0).

This is what the unjoined table looks like, suppose now we want to display the full_name of the supervisor. We need to join the table on itself

Domains don't allow you to join a table on itself, instead they allow you to create a copy of a table, then a join can be made, here's the domain designer and how table copies work within the Joins tab:

Once the table copy is made you will need to now join the tables. In this case a LEFT JOIN was performed to handle the special case of the president, otherwise an inner join would of been ok:

Now we'll want to display the information so that it makes sense to the end user, this can be done in the Display tab:

And finally, here's an ad-hoc report showing for each supervisor, the employees directly underneath him/her:

 

Feedback
randomness