MSTR's solution to this problem prior to 7i(they refer to this as a recursive table) is to use views of the recursive tables. One big advantage to using this method was that we could filter the views so that a parent only included those records that had a child. The advantage here was that if we ran a report that just listed the parent and you didn't want to see the children (no metrics) you didn't need to bring a filter into the report. We utilized this method for our implementation and it worked well.
Using the aliasing function in MSTR 7i is a viable solution however there are drawbacks. The first is that unlike views, the column names are the same so you may end up with some inadvertent joins unless you go into the attributes and set up the manual relationships over the automatic. The second is that you get a mirror image of the recursive table with no filtering. So if you put the parent attribute on a report and the child attribute on another report without metrics then the lists will be exactly the same.
One last thing to keep in mind with recursive tables, (an Employee Table is a good example.) Say that you have a sales team of (sales numbers included) Jim ($3.00), Pat ($5.00), and Tom ($4.00). Jim and Pat work for Sue and Tom works for Jon. When you want to see the total sales for the managers, Jon’s numbers are $4.00 and Sue’s numbers are $8.00. That works well. Now understand that as well as being a manager Jon is also a salesman. His numbers are $9.00. Unfortunately, even though Jon’s numbers and Tom’s numbers should be added together to get Jon’s total numbers they will not. Jon will be reported under the grandparent manager and that manager will show up in the same attribute as the parent attribute. I hope that I did not confuse anyone more but we have been wrestling with this for some time now.
