How to Create a LiveReport that shows levels for items and permission list for each item

Cheryl_Brock

I need to create a report that will return the items in a selected folder with the level and the list of users and their permission for each item.Here is my livereport sql:select level,d.dataid,d.parentid,ka.FIRSTNAME || ' ' || ka.LASTNAME || ' (' || ka.NAME || ')' "Owner", substr(Lpad('..',4*(level-1),'*')||d.name ,1,40) "Folder Name", DECODE(da.RIGHTID, -1,'Public Access', -2, 'Compass Administrators', k.FIRSTNAME || ' ' || k.LASTNAME || ' (' || k.NAME || ')' ) "UserGroup", da.PERMISSIONS "Rights" FROM DTREE d, KUAF k, DTREEACL da, KUAF ka WHERE da.DATAID = d.DATAID AND da.RIGHTID = k.ID(+) AND %2 AND d.DATAID IN (SELECT DATAID FROM DTREE START WITH DATAID = %1 CONNECT BY PRIOR DATAID = PARENTID) and da.RIGHTID != -2 and d.userid= ka.ID(+)The current output looks like this:LEVEL DATAID PARENTID Owner Folder Name UserGroup Rights 0 128456104 147499939 customview.html 258207 0 128456104 147499939 customview.html Team 258207 0 128456104 147499939 customview.html Public Access 36995 0 147499939 147505971 Admin Tools and Services Page - Old Admin 258207 0 147499939 147505971 Admin Tools and Services Page - Old Team 258207 0 147499939 147505971 Admin Tools and Services Page - Old Public Access 36995 0 147505971 145241757 Admin Other Customviews Admin 258207 0 147505971 145241757 Admin Other Customviews Team 258207 0 147505971 145241757 Admin Other Customviews Public Access 36995 What I want is the output to show the Level Column.Can someone assist me for this?

Lindsay_Davies

Message from Lindsay Davies via eLinkHi Cheryl,I think your first problem is that "level" will only contain informationwhen used with a connect by prior. In your statement, the connect by prior is in a sub select but level isused in the outer select.But, of course, if you remove the sub-select and put the connect byprior in to the main query, you get the error "cannot have join withCONNECT BY".The trick is to use an inline-view technique.First, let me format up your original SQL to make it more readable andeasier to compare with my suggestion...Your original statement isselect level, d.dataid, d.parentid, ka.FIRSTNAME || ' ' || ka.LASTNAME || ' (' || ka.NAME || ')' "Owner", substr(Lpad('..',4*(level-1),'*')||d.name ,1,40) "Folder Name", DECODE(da.RIGHTID, -1,'Public Access', -2, 'Compass Administrators', k.FIRSTNAME || ' ' || k.LASTNAME || ' (' || k.NAME|| ')' ) "UserGroup", da.PERMISSIONS "Rights" FROM DTREE d, KUAF k, DTREEACL da, KUAF ka WHERE da.DATAID = d.DATAID AND da.RIGHTID = k.ID(+) AND %2 AND d.DATAID IN (SELECT DATAID FROM DTREE START WITH DATAID = %1CONNECT BY PRIOR DATAID = PARENTID) and da.RIGHTID != -2 and d.userid= ka.ID(+)My suggestion is....select d.flevel, d.dataid, d.parentid, ka.FIRSTNAME || ' ' || ka.LASTNAME || ' (' || ka.NAME || ')' "Owner", substr(Lpad('..',2*(d.flevel-1),'*')||d.name ,1,40) "Folder Name", DECODE(da.RIGHTID, -1,'Public Access', -2, 'Compass Administrators', k.FIRSTNAME || ' ' || k.LASTNAME || ' (' || k.NAME|| ')' ) "UserGroup", da.PERMISSIONS "Rights"FROM KUAF k, DTREEACL da, KUAF ka, (SELECT level flevel, dataid, parentid, name, userid FROM DTREE STARTWITH DATAID = %1 CONNECT BY PRIOR DATAID = PARENTID) DWHERE da.DATAID = d.DATAID AND da.RIGHTID = k.ID(+) AND %2 and da.RIGHTID != -2 and d.userid= ka.ID(+)You can see that the select with connect by prior on DTree is nowreturning columns as though it was a new table D and I am selecting allthe columns you needed in the original list.This means we do not select from DTree in that outer list, but from D.We need to alias 'level' as a new column name 'flevel' for the inlineview D and refer to that in the manipulation in the main selectstatement.This should work for you - I tested it without the %1 and %2 and it wasOK.Good luck.RegardsLindsayUK SupportOpen Text -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 13 September 2006 21:02To: eLink RecipientSubject: How to Create a LiveReport that shows levels for items andpermission list for each itemHow to Create a LiveReport that shows levels for items and permissionlist for each item Posted by Brock, Cheryl L on 09/13/2006 04:00 PMI need to create a report that will return the items in a selectedfolder with the level and the list of users and their permission foreach item.Here is my livereport sql:select level,d.dataid,d.parentid,ka.FIRSTNAME || ' ' || ka.LASTNAME || '(' || ka.NAME || ')' "Owner", substr(Lpad('..',4*(level-1),'*')||d.name,1,40) "Folder Name", DECODE(da.RIGHTID, -1,'Public Access', -2,'Compass Administrators', k.FIRSTNAME || ' ' || k.LASTNAME || ' (' ||k.NAME || ')' ) "UserGroup", da.PERMISSIONS "Rights" FROM DTREE d, KUAFk, DTREEACL da, KUAF ka WHERE da.DATAID = d.DATAID AND da.RIGHTID =k.ID(+) AND %2 AND d.DATAID IN (SELECT DATAID FROM DTREE START WITHDATAID = %1 CONNECT BY PRIOR DATAID = PARENTID) and da.RIGHTID != -2 andd.userid= ka.ID(+)The current output looks like this:LEVEL DATAID PARENTID Owner Folder Name UserGroup Rights 0 128456104 147499939 customview.html 258207 0 128456104 147499939 customview.html Team258207 0 128456104 147499939 customview.html PublicAccess 36995 0 147499939 147505971 Admin Tools and Services Page - OldAdmin 258207 0 147499939 147505971 Admin Tools and Services Page - OldTeam 258207 0 147499939 147505971 Admin Tools and Services Page - OldPublic Access 36995 0 147505971 145241757 Admin Other Customviews Admin 258207 0 147505971 145241757 Admin Other Customviews Team 258207 0 147505971 145241757 Admin Other Customviews Public Access36995 What I want is the output to show the Level Column.Can someone assist me for this?[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.