"Recently viewed" reports contains duplicates

I've noticed that the My Recently Viewed report contains many duplicates for documents that have more than one version. Before I dive into the SQL, has anyone had to modify it so that it contains only one entry per document?Note that it isn't one entry per version of the document either. All the results link to the same version of the document (the most recent one).ThanksKyle

Find more posts tagged with

Comments

Appu_Nair

That is kind of weird...The report is pulling info from duaditnew for the performerid and would not know if one Ftched,Viewed it more than one time.This usually happens if there is a gif/jpg image that I hit everytime I hit livelink.If you are sure you have viewed that document only once I would look at duaditnew and see if there is something going on(things like webdav saving it even when we are not).maybe a distinct thrown cleverly could filter it but I would no doubt check if something freaky is going on

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

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

We have 2 different "Recently Viewed" reports setup. I threw them together quite some time ago, so they made need some tweaking or optimizing. They both exclude our custom logo.

Recently viewed docs - most recent access time

select %4, max( DAuditNew.AuditDate ) as AuditDate, DAuditNew.AuditStr, DVersData.MimeType

from DTree, DAuditNew, DVersData

where (DTree.DataID=DAuditNew.DataID) and (DTree.DataID = DVersData.DocID) and (DTree.DataID!=42083) and (DTree.VersionNum = DVersData.Version) and (DAuditNew.PerformerID=%1) and (DAuditNew.AuditDate>%2) and ( (DAuditNew.AuditStr='Fetch') OR (DAuditNew.AuditStr='View') ) and %3 and %5 group by %4, DAuditNew.AuditStr, DVersData.MimeType order by DAuditNew.AuditDate desc

Recently viewed docs - each access time

select %4, DAuditNew.*, DVersData.MimeType

from DTree, DAuditNew, DVersData

where (DTree.DataID=DAuditNew.DataID) and (DTree.DataID!=42083) and (DTree.VersionNum = DVersData.Version) and (DTree.DataID = DVersData.DocID ) and (DAuditNew.PerformerID=%1) and (DAuditNew.AuditDate>=%2) and ( (DAuditNew.AuditStr='Fetch') OR (DAuditNew.AuditStr='View') ) and %3 and %5 order by DAuditNew.AuditDate desc

%1 = UserID
%2 = User Input 1 (Date)
%3 = Filter Permissions
%4 = Report Fields
%5 = Filter Document

Display colums:

AuditDate Audit Date

AuditStr Event

I hope this helps.

-Nicole

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 22, 2009 1:20 PM
To: eLink Recipient
Subject: "Recently viewed" reports contains duplicates

"Recently viewed" reports contains duplicates

Posted by libbyrome (Baley, Kyle) on 2009/01/22 13:15

I've noticed that the My Recently Viewed report contains many duplicates for documents that have more than one version. Before I dive into the SQL, has anyone had to modify it so that it contains only one entry per document?

Note that it isn't one entry per version of the document either. All the results link to the same version of the document (the most recent one).

Thanks
Kyle

Kyle_Baley

Thanks Nicole. The first one worked for us. Made two minor changes. The first was to remove the (DTree.DataID!=42083) condition (which I assume is specific to your environment) and changed the order by expression to MAX(dauditnew.auditdate). I suspect it's a difference between SQL Server and Oracle syntax.

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

eLink

I’m glad they could help. I ampretty sure I took the LiveReport that came with the system and modified it. The DTree.DataID!=42083 is specific to our system. We have a custom logo onthe home page and if I don’t exclude it, it shows up multiple times inusers’ recently viewed documents.

From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 22, 20095:46 PM
To: eLink Recipient
Subject: RE- RE "Recently viewed"reports contains duplicates

RE- RE "Recently viewed" reports contains duplicates		Posted by libbyrome (Baley, Kyle) on 2009/01/22 17:41
In reply to: RE "Recently viewed" reports contains duplicates		Posted by nbride (Bride, Nicole) on 2009/01/22 14:00