Workaround on recursive SQL query

Posted on 2009-04-16
Last Modified: 2012-05-06
I have 2 tables.
One is  "document" with doc_id and all other info.
Second is "doctree" with doc_id and parent_id.
parent_id is related to other doc_id in document

Let say that I have doc A (123) with a child X (124) with a child K (127), and doc D (381) with 2 child B (383) and W (199)  the doctree table would look like below.

I need a list with the parent on top followed by their respective child to be put in crystal report.
row A .............
row X .............
row K .............
row D .............
row B  .............
row W .............

Can anyone give me an idea on how to achieve this.

Note: I can't change the database structure or add anything on the database server.
| doc_id   | parent_id  |


|  124     |  123       |

|  127     |  124       |

|  383     |  381       |

|  199     |  381       |

Open in new window

Question by:bleach77
    LVL 77

    Expert Comment

    This is covered in what CR terms 'Hierarchical Reports' .
    Look for Hierarchical' in Help to get full details of how to implement it.

    It lloks like you would create a group on Doc_id and then usinsgthe hierarchical grouping options (Report menu) to specify Parent_id as the parent field.
    LVL 4

    Author Comment

    Hierarchical function is great. But I still can't figure out how to filter the top parent. Let say if I only want the result which the parent type is "profile" but the childs have a different type "invoice", how can I achieve this?
    LVL 77

    Expert Comment

    I assume that 'profile' and 'invoice' are contained in the document table.
    To do selection like that you will have to add the doctree table and 2 copies of the document table to your report.
    Link one copy of document to the doc-id column in doc-tree and the second copy to the Parent-id column.
    You can then set your selection rules.
    LVL 4

    Author Comment

    It seem that doesn't work like i wanted. I see a lot of example based on company hierarchy.  So i'm gonna use that for a new example.
       Adam is Revenue General Manager
          Bobby is a sales manager
             Charlie is an executive
             Dorian is an executive
          Emily is a Marketing Manager
             Frank is an executive
             Garner is an executive
       Harold is operation GM
          Ian is Procurement manager
         ....... and the list go on.

    How can I filter the people only under Adam? I am losing ways of doing this.

    LVL 77

    Accepted Solution

    That is more difficult than the original rule you specified.  The method below is the only way I know of doing this, but others may have different approaches.

    You build your hierachical report.
    Then in the group header you need a formula field  to test the hierachy level and set a flag to indicate whether this hierarch is to be suppressed.

    booleanvar supp; //used to control suppression of records
    if HierarchyLevel (GroupingLevel ({Employee.Reports To}))=1 and GroupName ({Employee.Reports To})="Adam" then
    supp:= false
    else if HierarchyLevel (GroupingLevel ({Employee.Reports To}))=1  then
    supp:= true
    //do nothing

    You then need 2 conditional Suppress formulas.

    In the Group Header conduitional suppression you have..
    HierarchyLevel (GroupingLevel ({Employee.Reports To}))=1 and GroupName ({Employee.Reports To})<>"Adam"

    and in the Detail conditional suppression formula you have:

    booleanvar Supp;
    supp  //suppress if supp = true

    Basically as you encounter each group header you have to see if it is a top level header for the hierarchy.
    If it is, then you see if it is 'Adam' and if so, you switch off suppression (using the booleanvar Supp)

    However, if you are at the top level and it is not 'Adam' then you switch on suppression.

    Otherwise you leave the suppression setting unchanged.
    LVL 4

    Author Comment

    I can't get the HierarchyLevel or GroupingLevel  function to work. Is there any additional thing I need to install?
    I'm using Crystal Report 10.
    LVL 77

    Expert Comment

    I'm using CR11 & CR2008; I don't have CR10, but i can see that these functions are not in CR9, so I guess you don't have them either.

    I'm sorry, but in that case I don't know of any way to do what are asking.
    LVL 22

    Expert Comment

    Just add the document and doctree tables as aliases, restrict their records to those where document is "Adam", join from doctree child to an alias of doctree (joined to another alias of document, and let the hierarchy rip from there.  In other words, level 1 will be manual, level 2 and below will be using Grouping Hierarchy...

    LVL 4

    Author Comment

    Hey hi,

    I manage to create a formula that return the level of hierarchy. It's a simple one and I think it can be better if I know how to push or pop array in CR which I'm not gonna bother myself to learn. See the code below.

    Note: This code only apply as long as you know the deepest level it can be.
    If you don't know that, go and learn the array in CR and try to implement this code with an array.

     I can't get what you mean by join
    " join from doctree child to an alias of doctree (joined to another alias of document, and let the hierarchy rip from there. "

    Shared NumberVar Lvl1; Shared NumberVar lvl2; Shared NumberVar lvl3; ....
    IF (lvl1 = 0) THEN (
    	lvl1 := child;  //parent are supposed to be null
    	lvl2 := 0; lvl3 := 0;
    	1; //return 1
    ELSE (
    	IF (parent = lvl1) THEN (
    		lvl2 := child;
    		lvl3 := 0;
    	ELSE (
    		IF (parent = lvl2) THEN (
    			lvl3 := child;
    		ELSE (
    			IF (parent = lvl3 THEN (
    				4; //Assume 4 is the deepest level possible
    			ELSE (
    				lvl1 := child;
    				lvl2 := 0; lvl3 := 0;

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now