I am looking for a live report which shows alle files which weren't open for one or two years in a specific folder. Has anyone an idea how to write such a report?
Thanks
I just wrote this for MS SQL:
select dt.DataID, dt.Name, da.AuditStr, da.AuditDate
from dtree dt
join dauditnew da
on dt.dataid = da.dataid
where da.auditstr = 'Fetch'
and da.auditdate < '2009-12-13 00:00:00'
order by da.AuditDate desc
You would have to change the date to whatever you want your cutoff to be. I haven’t tested it as a LiveReport, just as a SQL query.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, December 13, 2011 6:17 AMTo: eLink RecipientSubject: Live Report which shows all files which weren't open for one or two years
Live Report which shows all files which weren't open for one or two years
Posted byparens@viega.de (Arens, Peter) On 12-13-2011 06:11
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center
Thanks for your help. How would it be possible to make such a analysis on one folder including it's subfolders?
What version of Livelink are you running?
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, December 13, 2011 8:51 AMTo: eLink RecipientSubject: RE Live Report which shows all files which weren't open for one or two years
RE Live Report which shows all files which weren't open for one or two years
Posted byparens@viega.de (Arens, Peter) On 12-13-2011 08:48
Topic:
9.7.1 with Oracle.
Do you have your dtreeancestors table populated? We did because we had Recommender (I think) on. That table is the easiest to use for what you are looking for. I also think that Oracle has connect by prior, but I’m not familiar with it. Maybe an Oracle guru can help if you don’t have the dtreeancestors table.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, December 13, 2011 10:39 AMTo: eLink RecipientSubject: RE RE Live Report which shows all files which weren't open for one or two years
RE RE Live Report which shows all files which weren't open for one or two years
Posted byparens@viega.de (Arens, Peter) On 12-13-2011 10:35
Yes we do have the dtreeancestors table enabled. What idea do you have to create a livereport with that table?
Something like this should work. Just make sure you use a small subset of data to verify against first. Change the ancestorid to be whatever the top level folder is. Always test in a test environment with these scripts too, just to make sure they don’t kill anything!
select dt.DataID, dt.Name, da.AuditStr, da.AuditDate, dta.AncestorID
join DTreeAncestors dta
on dt.DataID = dta.DataID
and dta.AncestorID = 2000
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, December 13, 2011 12:19 PMTo: eLink RecipientSubject: RE RE RE Live Report which shows all files which weren't open for one or two years
RE RE RE Live Report which shows all files which weren't open for one or two years
Posted byparens@viega.de (Arens, Peter) On 12-13-2011 12:14
The problem with this report seems to be, that if the document was fetched during the time between 2008 and 2009 this report will find a document which probably was opened again in 2011. To work correctly this report must check if the most actual view happened bevor the time which was written in the report. Otherwise the report will show us documents, which seems to be important until now. Any idea for such a solution?
Good point. Let me think about it.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, December 15, 2011 9:26 AMTo: eLink RecipientSubject: RE RE RE RE Live Report which shows all files which weren't open for one or two years
RE RE RE RE Live Report which shows all files which weren't open for one or two years
Posted byparens@viega.de (Arens, Peter) On 12-15-2011 09:21
This should be a lot better:
select dt.DataID, dt.name, da.auditstr, dta.ancestorid, max(da.AuditDate) as MaxDate
group by dt.DataID, dt.Name, da.AuditStr, dta.AncestorID
having max(da.AuditDate) < '2009-12-13 00:00:00'
order by MaxDate
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Thursday, December 15, 2011 9:41 AMTo: eLink RecipientSubject: RE RE RE RE RE Live Report which shows all files which weren't open for one or two years
RE RE RE RE RE Live Report which shows all files which weren't open for one or two years
Posted bynbride@cvps.com (Bride, Nicole) On 12-15-2011 09:40
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Thursday, December 15, 2011 9:26 AMTo: eLink RecipientSubject: RE RE RE RE Live Report which shows all files which weren't open for one or two years
Posted by parens@viega.de (Arens, Peter) On 12-15-2011 09:21
Thank you very much, this seems to be the solution. I will go on testing that, but it seems to be the solution.
Thank you
