Workaround on recursive SQL query

Posted on 2009-04-16
Medium Priority
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
  • 4
  • 4
LVL 77

Expert Comment

ID: 24156114
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.

Author Comment

ID: 24165554
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

ID: 24165636
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 24182034
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

peter57r earned 1500 total points
ID: 24182438
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.

Author Comment

ID: 24182718
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

ID: 24182802
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 23

Expert Comment

by:Ido Millet
ID: 24184056
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...


Author Comment

ID: 24201162
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
	IF (parent = lvl1) THEN (
		lvl2 := child;
		lvl3 := 0;
		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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 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