Forum

This content is now out of date.

Visit Our Community

Report inventory, etc.

Hi,

I'd like to get an inventory of the reports (active/non-active).
Also the main report/co-display relationship.
The current Usage reports do not suffice in this case.

-Total number of unique reports
-Total number of active reports
-Total number of active reports that have been run within the last 18 months
-Total number of active reports that have NOT been run within the last 18 months
-Main report name and the co-display reports attached to it.

The results should include both hidden and not hidden reports.

In v7.1 the default settings for the Event and EventArchive table data is set to 30 and 90 days respectively. So I cannot get the data from those tables.
Is there another way to get at the needed data?

Thanks in advance,

Chris
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 --
Hi Chris,

Oh dang, I forgot that EventArchive is set to 90 days be default. Changing that setting is thankfully pretty easy as it only requires running a query against the YF DB. Check out this forum post for the SQL statement:
https://www.yellowfinbi.com/YFForum-How-to-configure-the-Event-Archive-time-?thread=126417

A quick word of warning though, we've had a few clients who have boosted the maximum days on the EventArchive table and have seen performance impacts as a result. Depending on your use and environment, that table can become very, very large.

The queries look good from our end, although they may of course require some fine tuning depending on what specific data you're seeking to retrieve. ReportInstance and ReportHeader are my go-to tables for report info, and ReportAssociate should give you the association info that you need.

Good luck, and let us know if there's anything else that we can help with.


Thanks,

-Conner

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.