How to find unused reports in CE10

[shined]

How can i find out which reports are not being used or reports that do not have usage in CE10.

Any help would me much appreciated

Thanks
shine

 

[Turkbear]

Hi,
Not sure you can, since the system really only 'knows' what is used/scheduled..With auditing you could see what was used and compare, but it is not straightforward..

Perhaps one of the 3rd-party tools will have that capability.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[shined]

yes you are right, from the auditing tables i can find which reports that have been run and do a minus on the reports that are in info objects, it is this where i am having trouble with, how to find all the reports that have been published to a high level folder that has many sub folders below.

apos has a product called KPI but unfortunately our management does not want to spend any money

 

[Turkbear]

Hi,
You can do an InfoObject/IStore based query to return all the ReportIDs in your system, regardless of the folder..You could then create a file with those IDs ( maybe comma-separated) and use that to build a standard table that can then be used to compare against the audit table entries..

Not a simple task, but probably do-able..

Sample query using asp:

<%

Const APS = "YourCMS"

Const UserID = "Usrername"

Const Password= "pass"

Const Aut = "secEnterprise"

Function Logon(ByRef IStore)

    Dim SessionManager 
    Dim Result
    Result = FALSE
    Set SessionManager = Server.CreateObject("CrystalEnterprise.SessionMgr")
    If Err.Number = 0 then
        Dim Sess
        Set Sess = SessionManager.Logon(UserID, Password, APS, Aut)
        If Err.Number = 0 then 
          Set IStore = Sess.Service ("", "InfoStore")
          Set Session("IStore") = IStore
          Result = TRUE
        End If
    end if
   Logon = Result
End Function

Sub Main
   Logon IStore
         Set Result = IStore.Query("Select SI_ID From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report'" )

The Result will have an array of IDs..You can take it from there, maybe..

Hope it gives an idea...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cmmrfrds]

Our audit table is kept in sql server. Here is a query I put together to check reports not viewed in the last 90 days.
SELECT
A.ObjectName,
max(B.UserName) as UserName,
max(A.Timestamp) as TimeStamp,
max(B.AuditString) as AuditString,
max(A.ObjectPath) as ObjectPath
FROM ClarityAuditing.dbo.CMS_AUDITTABLE as A
Left Outer JOIN
/* find viewed reports */
(SELECT
CMS_AUDITTABLE.ObjectName,
CMS_AUDITTABLE.UserName,
max(CMS_AUDITTABLE.TimeStamp) as TimeStamp,
max(Cast(CMS_AUDITTABLE.AuditString as VarChar(50))) as AuditString,
max(CMS_AUDITTABLE.ObjectPath) as ObjectPath
FROM ClarityAuditing.dbo.CMS_AUDITTABLE CMS_AUDITTABLE
WHERE (CMS_AUDITTABLE.ObjectName>N' ' AND CMS_AUDITTABLE.AuditID = '196609.00')
and convert(datetime,left(timestamp,10),101) > getdate()-92
Group By CMS_AUDITTABLE.ObjectPath, CMS_AUDITTABLE.ObjectName, CMS_AUDITTABLE.UserName
) B
ON A.ObjectPath = B.ObjectPath
WHERE (A.ObjectName>N' '
AND Left(Cast(A.AuditString as VarChar(15)),14) = 'Job Successful'
AND convert(datetime,left(A.timestamp,10),101) > getdate()-92)
AND B.ObjectName IS NULL
Group By A.ObjectPath, A.ObjectName
Order By A.ObjectName

 

[shined]

Thank you