|
jferguson982 -> Help with report 204 (6/5/2008 4:36:18 PM)
|
I'm trying to customize report 204 (Management 1 - Updates required but not deployed) so that it won't prompt for a specific collection and vendor. I want to automatically populate the collectionID and vendor so that I can put it in a dashboard for 2 separate collections. I've cloned the report and started modifying it, but i can't get it to work. when i run it with my customizations, i get zero results, but when i run the original report, i get results. I was hoping someone could look at it and show me what i'm missing. Here's my code (with no prompts):
set nocount on
declare @VendorID int; select @VendorID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Company' and CategoryInstanceName='Microsoft'
declare @ClassID int; set @ClassID='0'
declare @CollID char; set @CollID='IA10001E'
declare @NumTotal int; select @NumTotal=count(*) from v_ClientCollectionMembers where CollectionID='IA10001E' and IsClient=1
declare @CI table(CI_ID int primary key); with CA as (select AssignmentID from v_CIAssignmentTargetedCollections where CollectionID='IA10001E')
, CM as (select ResourceID from v_ClientCollectionMembers where CollectionID=@CollID and IsClient=1)
insert @CI
select ci.CI_ID
from v_UpdateCIs ci
where ci.IsHidden=0
and (@VendorID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@VendorID))
and (@ClassID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))
and exists(select 1 from CM join v_UpdateComplianceStatus cs on cs.CI_ID=ci.CI_ID and cs.ResourceID=CM.ResourceID and cs.Status=2)
and not exists(select 1 from CA join v_CIAssignmentToCI ac on ac.AssignmentID=CA.AssignmentID and ac.CI_ID=ci.CI_ID)
select
ui.ArticleID as ArticleID,
ui.BulletinID as BulletinID,
ui.Title as Title,
NumMissing as Missing,
PComputers=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(@NumTotal, 0), 1)),
ui.InfoURL as InformationURL,
ui.CI_UniqueID as UniqueUpdateID
from @CI ci
left join v_UpdateInfo ui on ci.CI_ID=ui.CI_ID
left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID='IA10001E' and cs.NumMissing>0
order by NumMissing desc
Thanks for the help.
Jerrett
|
|
|
|
