Hi Chris,

Quick clarification question here, but have you tried building your own custom reports with the Admin Audit Package? I ask because while the prebuilt reports in the pack are useful, we have many users who create their own unique reports based off the Yellowfin Configuration Database view that gets generated.

You should be able to pretty easily display the number of active/unique reports and the number of reports that have/have not been opened in the last 18 months. This can be accomplished with the fields that are already set up, and a couple of filters.

Getting a report's name and the name of any co-display reports that are attached to it is a bit tricky, however. It would require editing the Audit view to include and join the backend table called "reportassociate". You will need to enable the columns in this table as well. From there, you will need to generate a report that retrieves the various co-display "child" reports through the use of some filters, etc.

Please let me know if this gets you on the right path, or if I've misinterpreted any part of your question.


Thanks!

-Conner

Hi Conner,

I had not imported the Admin Audit Package. Did that this morning and poked around in it.
The issue is that it uses the Event and Event Archive tables which we had at default settings. So I can only go back 90 days.

I have been looking at using the ReportInstance and ReportHeader tables to create a query for the data. I can get data back, but not being familiar with the YF schema, I'm unsure about the validity of the results (see query enclosed).
Still trying to work out how to determine the active but not run in 18 months.

I did work up a query for the parent/child data. That query is enclosed also.

Let me know if I'm on the right track with the ReportInstance and ReportHeader tables for data on active/run and active/not run.

Regards,
Chris

-- START QUERY Reports active/run and active/not run query --

SELECT DISTINCT
CONVERT(VARCHAR(7), ReportInstance.ReportDate, 126)
, ReportInstance.ReportId
, ReportHeader.ReportName
, COUNT(ReportInstance.ReportId) as RunCount

FROM ReportInstance LEFT OUTER JOIN
ReportHeader ON ReportInstance.ReportId = ReportHeader.ReportId
WHERE ReportInstance.ReportDate between '1/1/2015' and '5/30/2016' AND ReportInstance.ReportStatusCode = 'OPEN'
GROUP BY CONVERT(VARCHAR(7), ReportInstance.ReportDate, 126)
, ReportInstance.ReportId
, ReportHeader.ReportName

-- END QUERY --

-- START QUERY Parent/Child --

select p.[ParentReportId], p.ReportName as ParentReport, p.RltshpTypeCode, p.CategoryCode, p.SubCategoryCode, c.[ChildReportId], c.ReportName as ChildReport

FROM
(
select a.[ParentReportId], b.ReportName, a.[RltshpTypeCode], b.[CategoryCode],b. [SubCategoryCode], a.[AssociationId]
from [dbo].[ReportAssociate] a
left outer join dbo.ReportHeader b
ON a.[ParentReportId] = b.ReportId
where b.ReportStatusCode = 'OPEN') p

JOIN

(
select a.[ChildReportId], b.ReportName, a.[AssociationId]
from [dbo].[ReportAssociate] a
left outer join dbo.ReportHeader b
ON a.[ChildReportId] = b.ReportId
where b.ReportStatusCode = 'OPEN') c ON c.[AssociationId] = p.[AssociationId]

Order by p.ReportName

-- END QUERY --