nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Group Structure LiveReport

General_Dynamics_IT

Hello,Does anyone know how to create a report that will take a group name as an input and produce an indented list of all of the subgroups that are nested in that group. I'm not interested in the user names that are contained in the groups, just the group structure.For Example, this would be the output:Group1 (the group id or name entered into LiveReport)-Group2.1--Group2.1.1---Group2.1.1.1-Group2.2-Group2.3--Group2.3.1Thanks for any help you can give.Mike

Find more posts tagged with

Comments

Nicole_Bride

Has anyone figured out how to write a LiveReport or SQL query to list all of the groups, indented, showing the list of all of the subgroups?

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

I find this one works well for listing subgroups with indents, though I don't recall who wrote it. It is Oracle-specific, since it uses the "connect by prior" statement:

select lpad('_____', 2 * (a.l - 1)) || kuaf.name || lpad('*', kuaf.type), kuaf.id from kuaf, ( select childid, level l from kuafchildren start with id in (select kuaf.id from kuaf where lower(name) = 'yourparentgroup') connect by ID = PRIOR ChildID ) a where kuaf.id = a.childid and kuaf.type = 1

You would replace 'yourparentgroup' in the above query with the group you are interested in. Ensure you type the parent group name in lowercase in the query when replacing "yourparentgroup").

Let me know if that helps....

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, October 06, 2009 8:33 AM
To: eLink Recipient
Subject: Nested Groups Structure List

Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31
In reply to: Group Structure LiveReport		Posted by genduser6 (IT, General Dynamics) on 2004/03/30 10:20

Has anyone figured out how to write a LiveReport or SQL query to list all of the groups, indented, showing the list of all of the subgroups?

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

eLink

That does help, but we have a MS SQL database. Do you know howto translate the "connect by prior" to MS SQL?

From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, October 06, 2009 9:10 AM
To: eLink Recipient
Subject: RE Nested Groups Structure List

RE Nested Groups Structure List		Posted by bsingh (Singh, Bhupinder) on 2009/10/06 09:10
In reply to: Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

I find this one works well for listing subgroups with indents, though I don't recall who wrote it. It is Oracle-specific, since it uses the "connect by prior" statement:

You would replace 'yourparentgroup' in the above query with the group you are interested in. Ensure you type the parent group name in lowercase in the query when replacing "yourparentgroup").

Let me know if that helps....

Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31
In reply to: Group Structure LiveReport		Posted by genduser6 (IT, General Dynamics) on 2004/03/30 10:20

Has anyone figured out how to write a LiveReport or SQL query to list all of the groups, indented, showing the list of all of the subgroups?

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Group Structure LiveReport
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Nicole,

That depends on which version of MS SQL server you are running.

SQL 2005 and above supports a technique called recursive common table expressions (CTE).

In earlier versions, you have to write a stored procedure which builds a temporary table of your hierarchy of DTree objects of Group structures.

You then call that stored procedure within your livereport statement to get the list of IDs passed in.

There are examples in this discussion forum, I think.

Regards

Lindsay

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 06 October 2009 14:57
To: eLink Recipient
Subject: RE RE Nested Groups Structure List

RE RE Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 09:57
In reply to: RE Nested Groups Structure List		Posted by bsingh (Singh, Bhupinder) on 2009/10/06 09:10

Message from Bride, Nicole <nbride@cvps.com> via eLink That does help, but we have a MS SQL database. Do you know how to translate the "connect by prior" to MS SQL? From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Tuesday, October 06, 2009 9:10 AM To: eLink Recipient Subject: RE Nested Groups Structure List RE Nested Groups Structure List		Posted by bsingh (Singh, Bhupinder) on 2009/10/06 09:10
In reply to: Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

I find this one works well for listing subgroups with indents, though I don't recall who wrote it. It is Oracle-specific, since it uses the "connect by prior" statement:

You would replace 'yourparentgroup' in the above query with the group you are interested in. Ensure you type the parent group name in lowercase in the query when replacing "yourparentgroup").

Let me know if that helps....

Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31
In reply to: Group Structure LiveReport		Posted by genduser6 (IT, General Dynamics) on 2004/03/30 10:20

Has anyone figured out how to write a LiveReport or SQL query to list all of the groups, indented, showing the list of all of the subgroups?

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Group Structure LiveReport
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

eLink

Hi Lindsay,

We are running SQL 2005. I will do some research on CTE.

Thank you!

Nicole

From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, October 06, 2009 10:19 AM
To: eLink Recipient
Subject: RE RE RE Nested Groups Structure List

RE RE RE Nested Groups Structure List		Posted by ldavies (Davies, Lindsay) on 2009/10/06 10:18
In reply to: RE RE Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 09:57

Message from Lindsay Davies <ldavies@opentext.com> via eLink

Hi Nicole,

That depends on which version of MS SQL server you are running.

SQL 2005 and above supports a technique called recursive common table expressions (CTE).

In earlier versions, you have to write a stored procedure which builds a temporary table of your hierarchy of DTree objects of Group structures.

You then call that stored procedure within your livereport statement to get the list of IDs passed in.

There are examples in this discussion forum, I think.

Regards

Lindsay

RE RE Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 09:57
In reply to: RE Nested Groups Structure List		Posted by bsingh (Singh, Bhupinder) on 2009/10/06 09:10

Message from Bride, Nicole <nbride@cvps.com> via eLink

That does help, but we have a MS SQL database. Do you know how to translate the "connect by prior" to MS SQL?

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, October 06, 2009 9:10 AM
To: eLink Recipient
Subject: RE Nested Groups Structure List

RE Nested Groups Structure List		Posted by bsingh (Singh, Bhupinder) on 2009/10/06 09:10
In reply to: Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

I find this one works well for listing subgroups with indents, though I don't recall who wrote it. It is Oracle-specific, since it uses the "connect by prior" statement:

You would replace 'yourparentgroup' in the above query with the group you are interested in. Ensure you type the parent group name in lowercase in the query when replacing "yourparentgroup").

Let me know if that helps....

Nested Groups Structure List		Posted by nbride (Bride, Nicole) on 2009/10/06 08:31
In reply to: Group Structure LiveReport		Posted by genduser6 (IT, General Dynamics) on 2004/03/30 10:20

Has anyone figured out how to write a LiveReport or SQL query to list all of the groups, indented, showing the list of all of the subgroups?

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Group Structure LiveReport
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

[To reply to this thread, use your normal E-mail reply function.]
Topic:	Group Structure LiveReport
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Nicole_Bride

I finally got around to writing this, and as Lindsay suggested, used CTEs in SQL 2005. It doesn't give a tree-like view, rather a list of the groups and their place in the hierarchy. There's probably a way to parse it out in Excel and make it look pretty.This is where I learned how to do it:http://www.4guysfromrolla.com/webtech/071906-1.shtmlWITH GroupListing (ParentID, ParentName, type, ChildID, ChildName, HeirarchyLevel) AS( select a.id as "ParentID", a.name as "ParentName", a.type, b.childid as "ChildID", c.name as "ChildName", 1 as HeirarchyLevel from kuaf a JOIN kuafchildren b ON a.id = b.id JOIN kuaf c ON c.id = b.childid -- Put in group id where a.id = '14317' UNION ALL select e.id as "ParentID", e.name as "ParentName", e.type, f.childid as "ChildID", g.name as "ChildName", h.HeirarchyLevel + 1 AS HeirarchyLevel from kuaf e JOIN kuafchildren f ON e.id = f.id JOIN kuaf g ON g.id = f.childid JOIN GroupListing h ON e.id = h.ChildID)select ParentID, ParentName, ChildID, ChildName, HeirarchyLevelfrom GroupListingorder by HeirarchyLevel asc, ParentName asc, ChildName asc

Josephine_Bush

Hi Nicole, I found a CTE that I modified - and added a view to link kuaf to kuaf children before running the CTE - maybe you will find this helpful for what you need: CREATE VIEW vw_users_parent_children ASselect k.*, c.id as children_id, c.childidfrom ll9501.kuaf k, ll9501.kuafchildren cwhere k.id = c.childidCREATE VIEW vw_users_tree ASwith x (tree,childid,depth)as (select cast(name as varchar(500)), childid, 0from vw_users_parent_childrenunion allselect cast(x.tree+'-->'+e.name as varchar(500)),e.childid, x.depth+1from vw_users_parent_children e, xwhere x.childid = e.children_id )select distinct tree from xwhere depth >= 1