Actively Puslished Reports

Question

Actively Puslished Reports

enis.ertem

SSC-Addicted

Points: 429
More actions
July 7, 2010 at 10:07 am

#237308

Hi All,
As part of our report consolidation/clean up process I need to find out all the reports that haven't been run for the last year or so.
It is easy enough to do with a script like this:
SELECT Name, Path, UserName
FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID
LEFT JOIN C
WHERE Type = 2 AND path NOT LIKE '%/Old%' AND path NOT LIKE '%/ECRBank%'
and
Catalog.ItemID NOT IN
(
SELECT ExecutionLog.ReportID
FROM ExecutionLog
WHERE ExecutionLog.TimeStart BETWEEN '01Jan2010' AND GETDATE()
)
ORDER BY Name
Hovewer the problem is that the query returns all the reports regardless of whether they are currently published or not.
Rather than going through all the results row by row I am trying to find a flag or something which indicates whether a report is currently published, visible etc. and I can't find anything like that.
I am sure there are other people out there with similiar if not same requirements so I was wondering what would be the best approach for this problem?
Thank you
Enis

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gustavo Alvarez SSC Journeyman Points: 97 More actions · Answer 1

If a report is not published it should not be in the Catalog table. I just delete a report from report manager, and I don't see it any more in the Catalog table.

enis.ertem SSC-Addicted Points: 429 More actions · Answer 2

Hi,

That's true, but most of the unused reports are hidden in the reporting services. I am trying to return only the ones that are visible by everyone.

I don't think there is a way to return visible ones only.

E

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 3

You should be able to edit sql script and filter it down so that it only returns reports which are visible, it should be easier than looking at the data row by row.

enis.ertem SSC-Addicted Points: 429 More actions · Answer 4

Hi ,

I just don't know what the filter should be to return only the visible reports.

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 5

The sql scirpt can be edited to filter the reports as shown below

SELECT Name, Path, UserName

FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID

--LEFT JOIN C

WHERE Type = 2 AND path NOT LIKE '%/Old%' AND path NOT LIKE '%/ECRBank%'

and

Catalog.ItemID NOT IN

(

SELECT ExecutionLog.ReportID

FROM ExecutionLog

WHERE ExecutionLog.TimeStart BETWEEN '01Jan2010' AND GETDATE()

)

and name in ('Report A','Report B')

ORDER BY Name

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 6

Are you trying to filter out the reports that have been marked as hidden in report manager?

you could try

WHERE Type = 2 AND path NOT LIKE '%/Old%' AND path NOT LIKE '%/ECRBank%'

and hidden = 0

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 7

I think if the reports are hidden and not accessed through report manager you could place them in a certain folder and then just look at the reports in that folder?

enis.ertem SSC-Addicted Points: 429 More actions · Answer 8

adding the hidden = 0 did the trick. was looking for this field for ages. I missed it somehow.

Thanks every so much guys

Appreciated.

Enis