Folder path in report output

Hello,

I am trying to write a LiveReport to show the output as follows:

Document Name Folder path

Test1.doc Enterprise\Folder1\Folder2

I can get the data out with the following query, but I am struggling to put the output in one line. We are on SQL Server 2005 and my query looks like

WITH n(parentid, dataid, subtype, name, FolderLevel) AS
   (SELECT parentid, dataid, subtype, name, 0 AS FolderLevel
    FROM proedmsusr.dtree
    WHERE dataid = 1498816
        UNION ALL
    SELECT nplus1.parentid, nplus1.dataid, nplus1.subtype, nplus1.name, FolderLevel + 1
    FROM proedmsusr.dtree as nplus1, n
    WHERE n.parentid = nplus1.dataid
    )
SELECT n.FolderLevel, n.name, n.subtype 'Object Type'
FROM n

Any help would be greatly appreciated.

Thanks

Abu

Find more posts tagged with

Comments

Lindsay_Davies

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

Hi Abu,

Good try.

What I have done in this situation in the past is to build another column that contains the folder path.

However, be aware that you really should avoid trying to use a slash as the path separator.

You may encounter a slash in the name of a document or folder.
The character you should use is the colon ":" as this is the one that Livelink/Content Server itself uses.

Normally I use a recursive Common Table Expression to walk down a tree, so it was a bit of a challenge to cope with the peculiarities of walking up.

Once the path is built, the next challenge was to get the first dtree.name to be stored with every row.

Then finally decide how to identify the one row to select from the CTE, which is of course the volume and has a parentID of -1.

It is probably messy, inefficient and could be tidied up, but the statement below should work for you.

WITH n(parentid, dataid, subtype, name, docname, FolderLevel, pathstr) AS
   (SELECT parentid, dataid, subtype, name, name, 0 AS FolderLevel, CAST ('' as VARCHAR(MAX))
    FROM dtree
    WHERE dataid = 1498816
        UNION ALL
    SELECT nplus1.parentid, nplus1.dataid, nplus1.subtype,nplus1.name, n.docname, FolderLevel + 1,
         case when pathstr ='' then (cast(nplus1.Name AS varchar(max))) else (cast(nplus1.Name AS varchar(max)) + ':' + pathstr) END
    FROM dtree as nplus1, n
    WHERE n.parentid = nplus1.dataid
    )
SELECT n.FolderLevel, n.docname 'Name', n.subtype 'Object Type', pathstr as "Folder Path"
FROM n where n.parentid=-1

Regards

Lindsay

European Escalation Team

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 August 04, Thu 01:28
To: eLink Recipient
Subject: Folder path in report output

Folder path in report output

Posted by abu_mushayeed@affymax.com (Mushayeed, Abu) On 2011/08/03 20:23

Hello, I am trying to write a LiveReport to show the output as follows: Document Name                                      Folder path Test1.doc                                                   Enterprise\Folder1\Folder2 I can get the data out with the following query, but I am struggling to put the output in one line. We are on SQL Server 2005 and my query looks like
WITH n(parentid, dataid, subtype, name, FolderLevel) AS
   (SELECT parentid, dataid, subtype, name, 0 AS FolderLevel
    FROM proedmsusr.dtree
    WHERE dataid = 1498816
        UNION ALL
    SELECT nplus1.parentid, nplus1.dataid, nplus1.subtype, nplus1.name, FolderLevel + 1
    FROM proedmsusr.dtree as nplus1, n
    WHERE n.parentid = nplus1.dataid
    )
SELECT n.FolderLevel, n.name, n.subtype 'Object Type'
FROM n Any help would be greatly appreciated. Thanks Abu

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01