We help IT Professionals succeed at work.

Need help getting tree like data for Crystal Report

205 Views
Last Modified: 2010-03-19
Greetings,

I have a few tables that are usually viewed in the form of a tree.  I need to recreate the tree view in a query / stored procedure that can be loaded into a Crystal Report.  I know I can do this with a few cursors but I am looking for a better way if possible.  

after the tables are sorted in the correct order, it should look like this tree type of view below.  parent_id and relation_seq_id in table 1 derive how the layout is done.  

Application Submittal            
Plan Review            
*      Building Review      
*      Current Planning Review      
*      Plan Check      
CO Review            
*      Building CO      
*      Current Planning CO      
*      Resubmit Revised Plans      
*      Revised Plan Review      
      *      Building Review
      *      Current Planning Review
      *      Plan Check
*      CO Check
Closure      

table 1
PARENT_ID      PARENTTASKNAME      RELATION_SEQ_ID
0      NULL                                                9647
9647      Plan Review                                                9648
9647      CO Review                                                9649
9649      CO Review>Revised Plan Review      9650

table 2
RELATION_SEQ_ID      Key1      Key2      Key3      Order      Desc
9647                        07BCN      0      1058      1      Application Submittal
9647                        07BCN      0      1058      3      Plan Review
9647                        07BCN      0      1058      11      Closure
9648                        07BCN      0      1058      2      Building Review
9648                        07BCN      0      1058      3      Current Planning Review
9648                        07BCN      0      1058      11      Plan Check
9649                        07BCN      0      1058      1      Building CO
9649                        07BCN      0      1058      2      Current Planning CO
9649                        07BCN      0      1058      12      Resubmit Revised Plans
9649                        07BCN      0      1058      13      Revised Plan Review
9649                        07BCN      0      1058      11      CO Check
9650                        07BCN      0      1058      2      Building Review
9650                        07BCN      0      1058      3      Current Planning Review
9650                       07BCN      0      1058      11      Plan Check


Thanks,
Comment
Watch Question

Commented:
No need for cursors indeed, however, I'm trying to figure out how to 'link' the data ...

could you give some extra explanation on what means what ? I'm unable to properly follow the logic between both tables here =(






IF DB_ID('EE_Q__23027425') IS NOT NULL DROP DATABASE EE_Q__23027425
GO
CREATE DATABASE EE_Q__23027425
GO
USE EE_Q__23027425
GO
 
CREATE TABLE t_table1 ( PARENT_ID           int NOT NULL,
                        PARENTTASKNAME      varchar(500),
                        RELATION_SEQ_ID     int)
 
INSERT t_table1 (PARENT_ID, PARENTTASKNAME, RELATION_SEQ_ID) VALUES (0, NULL, 9647)
INSERT t_table1 (PARENT_ID, PARENTTASKNAME, RELATION_SEQ_ID) VALUES (9647, 'Plan Review', 9648)
INSERT t_table1 (PARENT_ID, PARENTTASKNAME, RELATION_SEQ_ID) VALUES (9647, 'CO Review', 9649)
INSERT t_table1 (PARENT_ID, PARENTTASKNAME, RELATION_SEQ_ID) VALUES (9649, 'CO Review>Revised Plan Review', 9650)
 
CREATE TABLE t_table2 (RELATION_SEQ_ID  int NOT NULL,
                       Key1             varchar(50),
                       Key2             int,
                       Key3             int,
                       [Order]          int,
                       [Desc]           varchar(500))
 
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9647, '07BCN', 0, 1058, 1, 'Application Submittal')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9647, '07BCN', 0, 1058, 3, 'Plan Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9647, '07BCN', 0, 1058, 11, 'Closure')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9648, '07BCN', 0, 1058, 2, 'Building Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9648, '07BCN', 0, 1058, 3, 'Current Planning Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9648, '07BCN', 0, 1058, 11, 'Plan Check')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9649, '07BCN', 0, 1058, 1, 'Building CO')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9649, '07BCN', 0, 1058, 2, 'Current Planning CO')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9649, '07BCN', 0, 1058, 12, 'Resubmit Revised Plans')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9649, '07BCN', 0, 1058, 13, 'Revised Plan Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9649, '07BCN', 0, 1058, 11, 'CO Check')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9650, '07BCN', 0, 1058, 2, 'Building Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9650, '07BCN', 0, 1058, 3, 'Current Planning Review')
INSERT t_table2 (RELATION_SEQ_ID, Key1, Key2, Key3, [Order], [Desc]) VALUES (9650, '07BCN', 0, 1058, 11, 'Plan Check')
 
go

Open in new window

Author

Commented:
Deroby,
The original tables were fairly large so I tried to size them down so that I could focus on the how to get the main data back that I need.  The three columns Key1, Key2, Key3 should exist in table1.  The link is done by RELATION_SEQ_ID, Key1, Key2, Key3.  The PARENT_ID column is used to determining who the parent is and 0 is the starting point.  There should of been another field in the Table1 which is another id field used to get only the records with the 9647 or whatever set of records needs to be accessed.  
                     these columns below should be in Table1  
                      PERMIT_ID    varchar(50),
                       Key1             varchar(50),
                       Key2             int,
                       Key3             int,
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Deroby, this is how the data is already in these tables from vendor software.  I don't have control over these.  I just need to get the data out of it for a report.  Thanks.

Author

Commented:
Since there has not been any solutions to this problem I am going to request that the question be closed.  

Commented:
What's wrong with the code I suggested ?
It's not exactly based on the source-data you provided, but that's kinda hard without proper documentation of the related fields... the example provided should get you started imho... if not, please elaborate on what part doesn't work for you.

Author

Commented:
I will agree that the solution would work but it work so I will go ahead and give credit for a solution.  thanks.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.