Script to identify Elite reports and the number of times the report is generated

--Function to get a count of how many times a report is generated
 
CREATE FUNCTION [dbo].[usf_EliteReportCount] (@ReportPK int)
RETURNS NUMERIC
AS
BEGIN
 
DECLARE @ReportCount NUMERIC
SET @ReportCount = (SELECT  COUNT(dbo.osReportJob.PK)
FROM         dbo.osReportJob WITH (NOLOCK) INNER JOIN
dbo.osReport WITH (NOLOCK) ON dbo.osReportJob.fkosReport = dbo.osReport.PK INNER JOIN
dbo.osProgram WITH (NOLOCK) ON dbo.osReport.PK = dbo.osProgram.fkosReport
GROUP BY dbo.osReport.Pk, dbo.osProgram.Author, dbo.osProgram.Title, dbo.osProgram.ExeName
HAVING dbo.osReport.Pk = @ReportPK)
RETURN @ReportCount
END
 
-- Script to identify Elite reports and the number of times each report is generated
 
SELECT     TOP (100) PERCENT dbo.MLSosModule.Description AS Module, dbo.usf_EliteReportCount(dbo.osReport.PK) AS ReportGenCount,
dbo.MLSosModule.ModuleCode, osProgram_Child.Title, osProgram_Child.Author, osProgram_Child.ProgramType,
osProgram_Parent.Title AS MenuLocation, dbo.osMenuItem.MenuGroup, osProgram_Parent.ProgramType AS ReportLocation,
osProgram_Child.ExeName, osProgram_Child.IsActive, dbo.osReport.IsProductionReport, dbo.osReport.UseParameterObjects,
osProgram_Parent.ConfigNumber, osProgram_Child.Pk AS osProgramPk, dbo.osReport.PK AS ReportPk
FROM         dbo.osReport INNER JOIN
dbo.osProgram AS osProgram_Child WITH (NOLOCK) ON dbo.osReport.PK = osProgram_Child.fkosReport INNER JOIN
dbo.MLSosModule WITH (NOLOCK) ON osProgram_Child.fkMLSosModule = dbo.MLSosModule.PK INNER JOIN
dbo.osMenuItem WITH (NOLOCK) ON osProgram_Child.Pk = dbo.osMenuItem.fkosProgramChild INNER JOIN
dbo.osProgram AS osProgram_Parent ON dbo.osMenuItem.fkosProgramParent = osProgram_Parent.Pk