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

Reports on discussion forums in Livelink?

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Does anyone know how to create reports on:1)To list all the discussion forums created and to show the count of postings in each discussion forums 2)To show the list of postings that doesn't have any replyAppreciate for any suggestion!-Michael C

Find more posts tagged with

Comments

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

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

I'm on a hunt for some good SQL examples, so thanks for posting this question! Here are some queries that work -- I haven't considered any performance implications, though (so be careful):

1)To list all the discussion forums created and to
show the count of postings in each discussion forums

select d.dataid, d.name, count(c.dataid)-1 "count of items" from dtree d, dtree c
where d.subtype = 215 and c.ownerid = d.dataid
group by d.dataid, d.name order by count(c.dataid) desc

215 is the subtype for discussions. All of the items in the discussion will have the discussion's dataid as their ownerid. There's a "discussion volume" item for the discussion whose ownerid is also the dataid, and I don't think you'd want to count that, thus the "count-1".

2)To show the list of postings that doesn't have any reply

I decided to interpret this as "All topics that don't have any replies", and that you'd want to list the topic and its discussion...

select t.dataid, t.name, d.name, count(r.dataid) from dtree t, dtree d, dtree r
where t.subtype = 130 and t.dataid *= r.parentid and t.ownerid = d.dataid
group by t.dataid, t.name, d.name
having count(r.dataid) < 1
order by count(r.dataid) desc order by count(r.dataid) desc

Topics have subtype 130, and their ownerid is the discussion's dataid. This statement uses a HAVING clause to look for topics where the count of replies is less than 1. In order to list those topics, an outer join is necessary, so that the join will include all topics, instead of just those with replies. For MSSQL, the outer join is specified with a * ( as in "t.dataid *= r.parentid"); in Oracle, this would be "t.dataid = r.parentid (+)".

It would be possible to get these into LiveReports without too much sweat, if that's what you'd like to do.

Marie Lindsay

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Thanks Marie,The SQL you put out is very useful, it's what I'm looking for, except if I want a list of discussion forums which is create in a particular location inside the application. 'Cuz I have a user only interested in how many disscussion forums being created in a specific topic not all currently inside the application.Appreciate for your help.BTW: What is discussion volume mean when you mentioned in your email?

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Hi Marie,The second sql I can make it work under oracle using "toad" but when I transfer the same code to livelink's livereport, the first "name" column giving out same result as the second "name" column, Why?Appreciate your helpMichael C

eLink User

Message from Alex Kowalenko via eLinkThe LiveReport module treats a column called "NAME" as a special case. Itwill not differentiate between multiple columns called "NAME". Rename yoursecond name column to get correct results.-alex------Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Friday, May 17, 2002 18:55To: eLink RecipientSubject: Hi Marie,Hi Marie,Posted by ASERA01Admin on 05/17/2002 06:50 PMHi Marie,The second sql I can make it work under oracle using "toad" but when Itransfer the same code to livelink's livereport, the first "name" columngiving out same result as the second "name" column, Why?Appreciate your helpMichael C[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Reports on discussion forums in Livelink?https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2729315&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Thanks Alex.Everything works now!Michael C

David_Beliveau_(sanmin013_-_(deleted))

Along these lines, does anyone have a livereport to show hits per discussion topic within a user specified date range?Thanks,Dave Beliveau

Marie_Lindsay_(MLindsay_(Delete)_15608)

the number of times it has been read or something?

Marie_Lindsay_(MLindsay_(Delete)_15608)

I'm hoping that by "particular location", you mean discussions in a certain folder or project. If that's the case, it's pretty easy -- replace "OBJID" in the following query with the nodeID/objID/DTree.DataID (gee, it would be nice if we standardized on a term for this) for the parent folder:select d.dataid, d.name, count(c.dataid)-1 "count of items" from dtree d, dtree c where d.subtype = 215 and c.ownerid = d.dataid AND D.PARENTID = OBJID group by d.dataid, d.name order by count(c.dataid) descRegarding "discussion volume", there are various types of items in Livelink that use volumes to help manage their contents. Some of the things that use volumes include personal workspaces, projects, task lists, the LiveReports Volume, the Categories Volume -- there are many more. If you're trying to get information for everything "from here down", it's quite handy if that starting point is a volume, as in the case of discussions, task lists, and channels. The DTree.OwnerID for all of the items in a discussion, task list or channel is a foreign key to the item at the top of the volume (the discussion, task list or channel). Ach - it's much easier to show by example than to explain with words. We discuss this a bit in the Advanced Schema and LiveReports course.

David_Beliveau_(sanmin013_-_(deleted))

What I am looking for is a report that would generate a list of discussion topics along with a count for the number of times each topic, or it's subset of replies, were read. I was looking through DAudit but didn't see discussion threads as being an audited event.Any help would be appreciated.

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

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

I can't think of a way to do this. The DReadList stores the discussion's dataid, the userid, and a list of discussion topics and replies that the user has read in that discussion, but the list of topics and replies (stored in the ReadList column) is not expressed in a format that can be easily parsed for use in this type of query.

Maybe someone else will have an idea.

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, May 28, 2002 6:03 AM
To: eLink Recipient
Subject: re. "hits" as in..

re. "hits" as in.. Posted by SANMIN013 (Beliveau, David) on 05/28/2002 06:57 AM
In reply to: "hits" as in... Posted by MLindsay (Lindsay, Marie) on 05/24/2002 05:40 PM

What I am looking for is a report that would generate a list of discussion topics along with a count for the number of times each topic, or it's subset of replies, were read. I was looking through DAudit but didn't see discussion threads as being an audited event.
Any help would be appreciated.

David_Beliveau_(sanmin013_-_(deleted))

Thank you for trying anyway Marie. The reports from earlier in this thread were useful as well.