Computers in a specific state for a deployment with collection prompt?

[maravig]

I would like to add a prompt for collection to this report.  I made a copy and added the CollectionID parameter prompt and what I thought was the right code, but I keep getting "Must declare the scalar variable '@CollectionID'.  If I do declare it, I start syntax errors on a join.  Has anyone ever attempted this before?

Thanks,
Jeff

[GarthMJ]

Without your query, there isn't a lot anyone can do to help you.

[maravig]

Sorry about that.  I have the report running without errors, but I only get headers so I'm wondering if adding the collection at the end and how it's being joined (I tried left join and just join) is preventing any data from displaying?!?

 

declare @asnid int = (select AssignmentID from fn_rbac_CIAssignment(@UserSIDs)  where Assignment_UniqueID=@DEPLOYMENTID and AssignmentType in (1,5))
          select 
            s.ResourceID,
            m.Name0 as ComputerName0,
            m.User_Domain0+'\'+m.User_Name0 as LastLoggedOnUser,
            asite.SMS_Assigned_Sites0 as AssignedSite,
            m.Client_Version0 as ClientVersion,
            s.StateTime as DeploymentStateTime,
            (s.LastStatusMessageID&0x0000FFFF) as ErrorStatusID,
            sn.StateName as Status,
            a.Assignment_UniqueID as DeploymentID,
            statusinfo.MessageName as ErrorStatusName 
          from fn_rbac_CIAssignment(@UserSIDs)  a
          join (
            select AssignmentID, ResourceID, StateType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentState_Combined(@UserSIDs)  where @STATUS/10000 in (300,301)
            union all
            select AssignmentID, ResourceID, TopicType, StateID, StateTime, LastStatusMessageID from fn_rbac_AssignmentStatePerTopic(@UserSIDs)  where @STATUS/10000 in (302)
            ) s on s.AssignmentID=a.AssignmentID and s.StateType=@STATUS/10000 and s.StateID = @STATUS%10000
          left join fn_rbac_StateNames(@UserSIDs)  sn on sn.TopicType=s.StateType and sn.StateID=isnull(s.StateID, 0)
	  join fn_rbac_R_System(@UserSIDs)  m on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)=0
          left join fn_rbac_RA_System_SMSAssignedSites(@UserSIDs)  asite on m.ResourceID = asite.ResourceID
          left join fn_rbac_AdvertisementStatusInformation(@UserSIDs)  statusinfo on statusinfo.MessageID=nullif(s.LastStatusMessageID&0x0000FFFF, 0)
          left join v_FullCollectionMembership_Valid on v_FullCollectionMembership_Valid.resourceid = m.ResourceID
		where m.ResourceID in (select ResourceID from v_FullCollectionMembership_Valid where CollectionID = @CollectionID) and a.AssignmentID=@asnid
          order by m.Name0