Folder contents report

Options
Giusi_Roselli_(groselli_-_(deleted))
Giusi_Roselli_(groselli_-_(deleted))
in API, SDK, REST and Web Services #1
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

Comments

  • Leonardo_Mora_(fordfn01admin_-_(deleted))
    Leonardo_Mora_(fordfn01admin_-_(deleted))
    #2
    Options
    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
    Etienne_De_Villiers
    #3
    Options
    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))
    Leonardo_Mora_(fordfn01admin_-_(deleted))
    #4
    Options
    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
    Etienne_De_Villiers
    #5
    Options
    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))
    Leonardo_Mora_(fordfn01admin_-_(deleted))
    #6
    Options
    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
    Glenn_Macaluso
    #7
    Options
    Leonardo can you post the finsihed product..I need to add some addtional information also but am having problems.Thanks
  • Khurram_Amin_(khurramamin_-_(deleted))
    Khurram_Amin_(khurramamin_-_(deleted))
    #8
    Options
    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
    Bhupinder_Singh E
    #9
    Options
    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:
     
     
    - 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
    komments E
    #10
    Options
    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
    Zulkifly Anawi
    #11
    Options
    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, 

    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
    Zulkifly Anawi
    #12
    Options
    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 
    AS
    (
    --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

Categories

image
OpenText CE Products
Extended ECM
Docs & APIs