Folder contents report

We need a report that list inside the container:items &number of version if the item is a documentsuch to obtain a container structure.In oracle we know there is a command called "CONNCT BY PRIOR" but we use SQL server.Can someone help me? Cheers,Giusi

Find more posts tagged with

Comments

Leonardo_Mora_(fordfn01admin_-_(deleted))

Someone posted a store procedure for SQL, but I cannot find it, I am looking for the same thing. Thanks for any lead on this store procedure for SQL. Leo

Etienne_De_Villiers

The following SQL stored procedure can be used to traverse a container item...CREATE PROCEDURE DBO.sp_GetLLChildren (@ParentID int = 2000, @Subtype int = Null)ASDECLARE @Level intSET @Level = 0SELECT DataID as DataID, @Level as ChildLevel INTO #T FROM dbo.Dtree WHERE ParentID = @RowCount > 0BEGIN IF (@Level >= 100) BREAK --if we are going 100 levels down, something must have gone wrong SET @Level = @Level + 1 INSERT INTO #T SELECT DataID, @Level FROM dbo.Dtree WHERE ParentID IN (SELECT DataID FROM #T WHERE ChildLevel = @Level -1)ENDIF @Subtype IS NULL --get all objects SELECT DataID FROM #T ORDER BY ChildLevelELSE --get only objects of particular subtype SELECT #T.DataID FROM #T INNER JOIN dbo.Dtree ON #T.DataID= dbo.Dtree.DataID WHERE dbo.Dtree.subtype=@Subtype ORDER BY ChildLevel DROP TABLE #TGO

Leonardo_Mora_(fordfn01admin_-_(deleted))

Hi Etienne I modified it so that I can get a name,full path, owner, but had many errors, do you have suggestions? Do you know how can I run it accross the system ? I would like to have a run on the entire dtree table for subtype 144. Thanks again for the tip.. Leo

Etienne_De_Villiers

What kind of errors are you receiving? Check the owner of the tables... The procedure I gave you has dbo.DTree, you might have to use somehting like livelink.DTree...The procedure also accepts parameters (@ParentID int = 2000, @Subtype int = Null). Execution: exec DBO.sp_GetLLChildren 2000, 144With that procedure you should be able to run through an entire folder structure (Including Enterprise Workspace) from a specific point by specifying the Livelink object id of the root folder...

Leonardo_Mora_(fordfn01admin_-_(deleted))

Now is working.. I want to build a report to do the same on all projects? For some reason doing the recurse in folders, is not the same when applied to projects. Thanks.. Leonardo

Glenn_Macaluso

Leonardo can you post the finsihed product..I need to add some addtional information also but am having problems.Thanks

Khurram_Amin_(khurramamin_-_(deleted))

I can execute this stored procedure in sql analyzer and it is giving me the results but when i run it through LiveReports then it displays NO RESULTS...???

Bhupinder_Singh

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

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

In order to resolve this, I think you need to add SET NOCOUNT ON in the stored procedure. This will allow the stored procedure to return the results to the LiveReport and then they will show in Livelink.

For additional details, see this discussion thread:

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=12451171&objAction=view&show=2

- Bhupinder

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

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, May 06, 2008 4:56 PM
To: eLink Recipient
Subject: I can execute this stored procedure in sql analyzer and it is giving me the...

I can execute this stored procedure in sql analyzer and it is giving me the...		Posted by khurramamin (Amin, Khurram) on 05/06/2008 04:54 PM
In reply to: stored procedure for container structure		Posted by evilliers (De Villiers, Etienne D) on 02/24/2006 03:53 AM

I can execute this stored procedure in sql analyzer and it is giving me the results but when i run it through LiveReports then it displays NO RESULTS...???

komments

Message from Greg Kellogg <gkellogg@opentext.com> via eLink

eLink

Did you set the LiveReport type toAutoLiveReport? I sometimes forget to do that.

Greg Kellogg

(361) 815-8950

From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, May 06, 2008 3:56PM
To: eLink Recipient
Subject: I can execute this storedprocedure in sql analyzer and it is giving me the...

I can execute this stored procedure in sql analyzer and it is giving me the...		Posted by khurramamin (Amin, Khurram) on 05/06/2008 04:54 PM
In reply to: stored procedure for container structure		Posted by evilliers (De Villiers, Etienne D) on 02/24/2006 03:53 AM

I can execute this stored procedure in sql analyzer and it is giving me the results but when i run it through LiveReports then it displays NO RESULTS...???

Zulkifly Anawi

I would appreciate it if someone can help on query below. I specified the ParentID, Unfortunately, it will return all Project Workspace from Enterprise level:

DECLARE

@Level int,

@ParentID int,

@Subtype int

SET @Level = 0

SET @ParentID = 689083 -- Project Workspace ID

SET @Subtype = 848

SELECT DataID as DataID, @Level as ChildLevel INTO #T

FROM DTree

--WHERE @ParentID = @ROWCOUNT > 0 -- This line is invalid in SQL 2016

BEGIN

IF (@Level >= 100) GOTO InvalidLevel --if we are going 100 levels down, something must have gone wrong

SET @Level = @Level + 1 INSERT INTO #T

SELECT DataID, @Level

FROM DTree

WHERE @ParentID IN (SELECT DataID FROM #T WHERE ChildLevel = @Level -1)

END

InvalidLevel:

IF @Subtype IS NULL --get all objects

SELECT DataID FROM #T ORDER BY ChildLevel

ELSE --get only objects of particular subtype

SELECT #T.DataID FROM #T

INNER JOIN DTree

ON #T.DataID = DTree.DataID

WHERE SubType=@Subtype

ORDER BY ChildLevel

DROP TABLE #T

Zulkifly Anawi

I found alternative solution from: https://forums.opentext.com/forums/developer/discussion/290868/livereport-needed-to-list-all-the-sub-folder-names-within-a-folder#Item_2

--Define CTE

WITH Folders

(

--Anchor member definition

SELECT p.[ParentID]

,p.[DataID]

,p.[Name]

,p.[SubType]

,0 AS [Level]

, CAST(p.[Name] AS VARCHAR(255)) AS Path FROM [DTree] p

WHERE p.[DataID] = 689083 --Starting Folder

AND p.[SubType] IN (0, 848) -- To get workspace under Folder / Workspace

UNION ALL

--Recursive member definition

SELECT c.[ParentID]

,c.[DataID]

,c.[Name]

,c.[SubType]

,[Level] + 1

, CAST(Path + ' / ' + CAST(c.[Name] AS VARCHAR(255)) AS VARCHAR(255))

FROM [DTree] c

INNER JOIN Folders d ON d.[DataID] IN (-(c.[ParentID]), c.[ParentID]) -- Include Folder/Workspace Parent ID

WHERE c.[SubType] IN (0,848) -- To get workspace under Folder / Workspace

)

--Execute Query

SELECT [ParentID],[DataID],[Name],[Level], [SubType], [Path]

FROM Folders

WHERE SubType = 848 -- Adding Filter to return Workspace