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; } } }

How to know howmany categories are applied on a documents through Livelink Report

Livelink_Admin

Can someone help me please on the below?

We have lot of documents inside a folder. Every document should have 3 categories. Unfortunately few documents has only 2 categories applied, 1 missing. How to find out those documents using LiveReport?

Thanks in advance.

Find more posts tagged with

Comments

Narasimha_Rao

Hi,

Try below query.

Inputs: Container - Select a Folder

SQL : SELECT Dtree.Name AS 'Document Name', LLAttrData.ValStr AS 'Category Name'
          FROM Dtree, LLAttrData
          WHERE Dtree.DataID = LLAttrData.ID
          AND Dtree.ParentID = %1
          AND Dtree.Subtype = 144
          AND LLAttrData.KeyID = 1

Param %1: User Input1

Report Format: Auto LiveReport

Output:

Document Name       Category Name
xyz                            Cat A
xyz                            Cat B
abc                            Cat B
123                            Cat A
123                            Cat B
123                            Cat C

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Mon, Jan 7, 2013 at 10:47 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

How to know howmany categories are applied on a documents through Livelink Report Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-07-2013 12:11

Hi Can someone help me please on the below? We have lot of documents inside a folder. Every document should have 3 categories. Unfortunately few documents has only 2 categories applied, 1 missing. How to find out those documents using LiveReport? Thanks in advance.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Narasimha_Rao

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Mon, Jan 7, 2013 at 10:47 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

How to know howmany categories are applied on a documents through Livelink Report Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-07-2013 12:11

Hi Can someone help me please on the below? We have lot of documents inside a folder. Every document should have 3 categories. Unfortunately few documents has only 2 categories applied, 1 missing. How to find out those documents using LiveReport? Thanks in advance.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Livelink_Admin

Thank you very much for your reply Narsi. Can't we display Cat A and Cat B in one line.

Narasimha_Rao

Hi,

Use below query to get that result.

Inputs: Container - Select a Folder

SQL : SELECT Dtree.Name AS "Document Name",
           CategoryList =STUFF((SELECT ','+ LLAttrData.ValStr
           FROM LLAttrData
           WHERE Dtree.DataID = LLAttrData.ID
           AND LLAttrData.KeyID = 1
           GROUP BY LLAttrData.ValStr
           FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
           FROM Dtree
           WHERE Dtree.Subtype = 144
           AND Dtree.ParentID = %1

Param %1: User Input1

Report Format: Auto LiveReport

Output:

Document Name       Category Name
xyz                            Cat A , Cat B
abc                            Cat B
123                            Cat A , Cat B, Cat C

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Tue, Jan 8, 2013 at 5:12 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re How to know howmany categories are applied on a documents through Livelink Report 2 Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-08-2013 06:39

Thank you very much for your reply Narsi. Can't we display Cat A and Cat B in one line.
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Narasimha_Rao

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Tue, Jan 8, 2013 at 5:12 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re How to know howmany categories are applied on a documents through Livelink Report 2 Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-08-2013 06:39

Thank you very much for your reply Narsi. Can't we display Cat A and Cat B in one line.
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Livelink_Admin

Narsi,

Thanks a ton for replying to my query. Some how that is not executing. I tried to debug it but couldn't. Can you help me please?

This is the query you have given me.

SELECT Dtree.Name AS "Document Name",
CategoryList =STUFF(
(SELECT ','+ LLAttrData.ValStr
FROM LLAttrData
WHERE Dtree.DataID = LLAttrData.ID
AND LLAttrData.KeyID = 1
GROUP BY LLAttrData.ValStr FOR XML PATH(''),
TYPE
).value('.','VARCHAR(max)'), 1, 1, '')
FROM Dtree
WHERE Dtree.Subtype = 144
AND Dtree.ParentID = %1

It is giving following error.

ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 2 Column: 15

Narasimha_Rao

Hi,

The second query which I have sent you will work in MS SQL only. I think you have Oracle as Database.

Try below query which will work in Oracle.

Inputs: Container - Select a Folder

SQL : SELECT Dtree.Name AS "Document Name",
           LISTAGG(LLAttrData.ValStr, ',') WITHIN GROUP (ORDER BY LLAttrData.ValStr) AS "Category Name"
          FROM Dtree, LLAttrData
           WHERE Dtree.DataID = LLAttrData.ID
           AND Dtree.ParentID = %1
           AND Dtree.Subtype = 144
           AND LLAttrData.KeyID = 1
           GROUP BY Dtree.Name

Param %1: User Input1

Report Format: Auto LiveReport

Output:

Document Name       Category Name
xyz                            Cat A , Cat B
abc                            Cat B
123                            Cat A , Cat B, Cat C

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Wed, Jan 9, 2013 at 3:38 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re How to know howmany categories are applied on a documents through Livelink Report 2 2 Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-09-2013 05:07

Narsi, Thanks a ton for replying to my query. Some how that is not executing. I tried to debug it but couldn't. Can you help me please? This is the query you have given me. SELECT Dtree.Name AS "Document Name",
CategoryList =STUFF(
(SELECT ','+ LLAttrData.ValStr
FROM LLAttrData
WHERE Dtree.DataID   = LLAttrData.ID
AND LLAttrData.KeyID = 1
GROUP BY LLAttrData.ValStr FOR XML PATH(''),
    TYPE
).value('.','VARCHAR(max)'), 1, 1, '')
FROM Dtree
WHERE Dtree.Subtype = 144
AND Dtree.ParentID = %1 It is giving following error. ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 2 Column: ! 15
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Narasimha_Rao

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Wed, Jan 9, 2013 at 3:38 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re How to know howmany categories are applied on a documents through Livelink Report 2 2 Posted bylivelinkadmin@pdo.co.om (Admin, Livelink)On 01-09-2013 05:07

Narsi, Thanks a ton for replying to my query. Some how that is not executing. I tried to debug it but couldn't. Can you help me please? This is the query you have given me. SELECT Dtree.Name AS "Document Name",
CategoryList =STUFF(
(SELECT ','+ LLAttrData.ValStr
FROM LLAttrData
WHERE Dtree.DataID   = LLAttrData.ID
AND LLAttrData.KeyID = 1
GROUP BY LLAttrData.ValStr FOR XML PATH(''),
    TYPE
).value('.','VARCHAR(max)'), 1, 1, '')
FROM Dtree
WHERE Dtree.Subtype = 144
AND Dtree.ParentID = %1 It is giving following error. ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 2 Column: ! 15
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Mukesh_Giri

Hi Narsi:

Got interested in the report you wrote, how about having a recursion in the report with the path of documents and or folders within the chosen folder (parentid) and return everything?

Cheers!

Quoted narsi@supaisystems.com on 2013-01-09 10:15:

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Tue, Jan 8, 2013 at 5:12 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re How to know howmany categories are applied on a documents through Livelink Report 2 Posted by livelinkadmin@pdo.co.om (Admin, Livelink) On 01-08-2013 06:39

Thank you very much for your reply Narsi. Can't we display Cat A and Cat B in one line.
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Livelink_Admin

Thank you Narsi

Colin_Schmidt

Here's one you can setup to browse or key in a Document DATAID. I can't say it's all inclusive of all possible Attribute types, but it can be adjusted as you make them.

I have another that'll display all Attributes for immediate documents under a chosen folder.

I tried to upload/attach the guts of the LR, but the Wizard was failing, so here it is pasted:

-------------------------------------------------------------------------------------------------------------------------------------

select dt1.parentid, dt3.name "Parent Folder", dt1.dataid, dt1.name "Object", llad1.vernum "Version",
llad1.valstr "Category", llad1.defid, NVL(crm.attrname, llad2.valstr) "Attribute name",
llad2.attrid "AttrID",
case
when llad2.attrtype=-18 then 'Category: ['||llad2.valstr||']'
when llad2.attrtype=-7 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valdate, 'Mon-dd-yyyy')
when llad2.attrtype=-4 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valreal)
when llad2.attrtype=-1 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr
when llad2.attrtype=2 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valint)
when llad2.attrtype=5 and llad2.valint=0 then '{'||to_char(llad2.attrtype)||'}'||'False'
when llad2.attrtype=5 and llad2.valint=1 then '{'||to_char(llad2.attrtype)||'}'||'True'
when llad2.attrtype=5 and llad2.valint is NULL then '{'||to_char(llad2.attrtype)||'}'||'n/a'
when llad2.attrtype=10 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr
when llad2.attrtype=11 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr
when llad2.attrtype=12 then '{'||to_char(llad2.attrtype)||'}'||to_char(llad2.valint)
when llad2.attrtype=14 then '{'||to_char(llad2.attrtype)||'} User ['||to_char(llad2.valint)||' ]'||k.name
when llad2.attrtype=10013 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr
when llad2.attrtype=10131 then '{'||to_char(llad2.attrtype)||'}'||llad2.valstr
else 'Other AttrType ['||to_char(llad2.attrtype)||']' end AttributeValue
from llattrdata llad1
join dtree dt1 on llad1.id = dt1.dataid and llad1.vernum = dt1.versionnum /* Object */
left outer join dtree dt2 on llad1.defid = dt2.dataid
left outer join dtree dt3 on dt1.parentid = dt3.dataid /* Parent record for the Name */
left outer join llattrdata llad2 on llad1.id = llad2.id and dt2.dataid = llad2.defid and llad1.vernum = llad2.vernum
join catregionmap crm on crm.catid = llad2.defid and crm.regionname = 'Attr_'||llad2.defid||'_'||llad2.attrid /* Get the title of the Attribute */
left outer join kuaf k on llad2.valint = k.id /* For AttryType = 14, a UserID is used */
/* Get the LLATTRDATA record for the latest version of the document/object */
where llad1.id = %1 and llad1.attrtype = -18 and llad1.parentkeyid = -1 and llad1.vernum = (select dt.versionnum from dtree dt where dt.dataid = %2)
order by dt1.name, llad2.defid, llad2.attrid

Peter Stegl

Hi,

please specify what %1 and %2 Input labels should be used.

Regards, Peter

Chris Meyer

Hi Peter,

The following SQL should do what you need:

select count(*) as Count from DTree, LLAttrData where DTree.DataID=LLAttrData.ID AND DTree.VersionNum=LLAttrData.VerNum AND LLAttrData.AttrID=1 AND DTree.DataID=13991

The join is required to limit the query to the latest version of any versioned item (i.e., documents). You'll need to replace the 13991 with the specific DataID of the item you wish to query, or replace it with %1 to make it dynamic within your LiveReport.

I hope this helps.

On 26 June 2013 11:11, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Re How to know howmany categories are applied on a documents through Livelink Report 2 2 2 5 Posted bypeter.stegl@axpo.ch (Stegl, Peter)On 06-26-2013 05:08

Hi, please specify what %1 and %2 Input labels should be used. Regards, Peter
[To post a comment, use the normal reply function]
Topic: How to know howmany categories are applied on a documents through Livelink Report
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center