Parent Child Relationships in Domains / Ad-hocWhat 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 ExampleSuppose 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 | [toc] |
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:
How about a hirearchy or tree type report?
This gets much more tricky in Domain based Ad-hoc, there would be some customization - though not impossible. If your DBMS supports CONNECT BY then you're in luck otherwise you'll need to write a custom datasource that is multi-level aware. The tricky parts come when you're asking questions like, what are the total salaries for all the employees under the VP of Sales?
- CEO
- VP Sales
- Manager 1
- Employee 1
- Employee 2
Manager 2
Employee 3
- Manager 1
- VP Sales
Using Jaspersoft OLAP (Mondrian)
Another posibility is to use OLAP, the Mondrian engine supports hirearchial data even if your database does not. You can read more on the Mondrian project page here - the example Foodmart OLAP schema demonstrates this functionality quite elegantly in an ad-hoc crosstab:
Recommended Comments
There are no comments to display.