List reports that have not been viewed over a period to date? 1

[jambu]

I would like to get a list of reports that have not been viewed for a period.

Anyone have any suggestions how to go about this?

Thanks in advance.

 

[goranm]

Hi!

We use CE10, and there is a sample report in install\crystal e\samples\reports\AdminReports\Reports Viewed By Date Range.rpt

Maybe that can be to some help for you.

/Goran

 

[jambu]

Thanks Goranm,
I have that report already and it shows those reports that have been viewed.

What I am after is some sql that would get me all the instances not in the viewed set in the last few months. I will probably have some time to get into this next week, if I get a solution I will post it here.

I should have given my version of CE in the previous post, I am on CE10 too.

 

[cmmrfrds]

Our organization has upgraded to XI so it uses a different database model for the Audit database (actually provides less information, especially on folder structures). With CE10 we used SQL Server to store the Audit data and here is the query I wrote to get unviewed reports. It is in SQL Server syntax.

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()-62
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()-62)
AND B.ObjectName IS NULL
Group By A.ObjectPath, A.ObjectName
Order By A.ObjectName

Paste this query into a Command Object in the Crystal Report.

Also, I parsed the directory structure from the ObjectPath like this in a Formula.

WhileReadingRecords;
global stringvar hold1;
global stringvar hold2;
global stringvar dir1;
global stringvar dir2;
if instr({Command.ObjectPath},"/") > 0 then
dir1 := mid({Command.ObjectPath},2,instr(2,{Command.ObjectPath},"/")-1);
if instr({Command.ObjectPath},"/") > 0 then
hold1 := mid({Command.ObjectPath},instr(2,{Command.ObjectPath},"/"),len({Command.ObjectPath})-2);
if instr(2,{Command.ObjectPath},"/") > 0 then
dir2 := mid(hold1,2,instr(2,hold1,"/")-1);
hold1;

 

[jambu]

Thanks cmmrfrds!

That's very usefull.

I have added the following to the where clause of the outer sql to exclude the few reports that are picked up despite being run live within the time period.

AND A.ObjectID not in
(
select top 100 percent

ObjectID

from

ServerName.CE10AUDIT.ceuser.CMS_AUDITTABLE

where

convert(datetime,left(timestamp,10),101) > getdate()-62 and

AuditString like 'Report with live data viewed successfully%'

)

 

[jambu]

Ignore the sql above, it should be...

AND A.ObjectName not in
(
select Distinct

ObjectName

from

ServerName.CE10AUDIT.ceuser.CMS_AUDITTABLE

where

convert(datetime,left(timestamp,10),101) > getdate()-62 and

AuditString like 'Report with live data viewed successfully%'
)

 

[cmmrfrds]

I am glad you found it useful. I wish the XI Audit data model was as useful. The problem with the XI data model is that the directory stucture is only stored at report create time or modify time. My organization purchased the Auditing for XI in June and there is no directory information on the 1500 reports created prior to June. I talked to BOXI tech support on this and they said I would need to make an enhancement request to their development team, but I am not optimistic on this. It seems like a lot of hassle for a customer to get this type of basic management information. Anyway, happy reporting, but be aware your reports will probably need to change when upgrading to XI.