BusinessObjects Board

delete not used reports

sdk development
#1

I want to develop a program for BO 4.0 that looks when the last time a report was used and if it’s older then X days it will email someone and says they will be deleted soon. And then delete them after X period.

We have a huge amount of user built Webi reports that I want to clean up.

Basically find reports not run in 3 months. And say we will delete if not run in 6 months or something like that.

bcomer :us: (BOB member since 2010-02-25)

#2

I suggest you use the Auditing feature to identify the report “no used”.

Check this blog post on SAP SDN about Auditing in BusinessObjects 4.0.

Also check out the SDK Download area of BOB for the “Repository Documentor” to extract the list of Report & Author using SDK.

Nicolas Hadj-Blaha :new_zealand: (BOB member since 2005-03-10)

#3

Auditor will get me a list of all the reports and when last used. But I wanted something that would auto purge the old ones that are not used.

So something that has not been used in 3 months we start sending emails to users saying this report is no longer used. If you do not use it in the next 3 months it will be purged. And have this run monthly and do the purging sending the email out with the list of reports in it.

We can manually do this stuff with auditor but it’s manual. And all my users are external customers so the lists and emails really need to be customized.

I hope this makes sense.

bcomer :us: (BOB member since 2010-02-25)

#4

You would have to write something using the SDK. Here’s the logic I would use if I were writing this:

  1. Query for a list of all reports that have SI_LASTUPDATETIMESTAMP greater than 3 months ago.

  2. For each report, query to find its instances (SI_PARENTID = the SI_ID of the report and SI_INSTANCE = 1.) Determine when the most recent instance is - if it’s less than 3 months ago, keep the report.

  3. If there are no instances or the newest instance is more than 3 months ago, query the auditing database to get max(AUDIT_EVENT.Start_Timestamp) where AUDIT_EVENT.Event_Type_ID in (11, 21073, 196609, 458753). This is the most recent Last View Date. Here’s the SQL Server query I use for this with the XI 3.1 Audit database:


Select top 1 ae.User_Name, ts.lastRun
from Audit_Event ae
  inner join (
    Select max(ae.Start_Timestamp) lastRun 
    from Audit_Event ae
    where ae.Event_Type_ID in (11, 21073, 196609, 458753) 
      and ae.Object_CUID = @CUID) ts
  on ae.Start_Timestamp = ts.lastRun
where ae.Event_Type_ID in (11, 20173, 1996609, 458753)
  and ae.Object_CUID = @CUID

The audit database structure is different in 4.0 and I haven’t converted this code yet.

  1. If the max last view date is more than 3 months ago, delete the report.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#5

Thanks this is a good start. Still need to “warn” my users a month in advance. That X reports are on the delete list. I can probably pull that out of this code though.

bcomer :us: (BOB member since 2010-02-25)