I have modified the Installed Software Report to add scoping by Organizational Group, by adding parameters to the report and SQL accordingly. It works fine at the top level. However, when I drilldown to list the PCs with the software I choose by right-clicking in the report, the drilldown does not have the same OG scoping and instead lists all PCs.
Here is my top level report:
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @v2_ScopeCollectionGuid uniqueidentifier
SET @v2_ScopeCollectionGuid = '%ScopeCollectionGuid%'
IF ('%Type%' = 'Managed Software')
SELECT spi.Guid
,spi.Name
,spv.[Version]
,company.Name AS Company
,cnt.cnt AS [Count]
FROM vRM_Software_Product_Item spi
JOIN
(SELECT COUNT(DISTINCT inst._ResourceGuid) AS cnt,ra.ParentResourceGuid
FROM ResourceAssociation ra
JOIN Inv_InstalledSoftware inst
ON inst._SoftwareComponentGuid = ra.ChildResourceGuid and InstallFlag = 1
JOIN vComputer comp
ON comp.Guid = inst._ResourceGuid
WHERE ([comp].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollectionsByScope(@v1_TrusteeScope, @v2_ScopeCollectionGuid, 1))))
AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --product contains component
AND inst._ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
GROUP BY ra.ParentResourceGuid
)cnt
ON cnt.ParentResourceGuid = spi.[Guid]
JOIN Inv_Software_Product_State sps
ON sps._ResourceGuid = spi.[Guid]
AND sps.IsManaged = 1
LEFT JOIN Inv_Software_Product_Version spv
ON spv._ResourceGuid = spi.[Guid]
LEFT JOIN (SELECT rc.Name, ra.ParentResourceGuid AS softProdGuid
FROM RM_ResourceCompany rc
JOIN ResourceAssociation ra
ON ra.ChildResourceGuid = rc.[Guid]
AND ra.ResourceAssociationTypeGuid = 'D5C66D5A-7686-4CA2-B7C1-AC980576CE1D')company
ON company.softProdGuid = spi.[Guid]
WHERE ('%Name%' = '%' OR LOWER (spi.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR spv.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND spi.[CreatedDate] >= '%Discovered since%'
ORDER BY spi.Name
ELSE
IF ('%Type%' = 'All Software')
SELECT sci.[Guid]
,sci.Name
,sc.[Version]
,company.Name AS [Company]
,inst.cnt AS [Count]
FROM vRM_Software_Component_Item sci
JOIN Inv_Software_Component sc
ON sci.Guid = sc._ResourceGuid
JOIN (SELECT COUNT(DISTINCT _ResourceGuid) AS cnt, _SoftwareComponentGuid
FROM Inv_InstalledSoftware iis
Join vComputer comp on iis._ResourceGuid=comp.Guid
WHERE ([comp].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollectionsByScope(@v1_TrusteeScope, @v2_ScopeCollectionGuid, 1))))
AND InstallFlag = 1
AND _ResourceGuid IN (SELECT ResourceGuid from ScopeMembership WHERE ScopeCollectionGuid IN(SELECT ScopeCollectionGuid FROM fnGetTrusteeScopeCollections (@v1_TrusteeScope)))
GROUP BY _SoftwareComponentGuid)inst
ON inst._SoftwareComponentGuid = sci.Guid
LEFT JOIN (SELECT ra.ParentResourceGuid AS sofCompGuid, rc.Name
FROM RM_ResourceCompany rc
JOIN ResourceAssociation ra
ON ra.ChildResourceGuid = rc.Guid
AND ra.ResourceAssociationTypeGuid = '292DBD81-1526-423A-AE6D-F44EB46C5B16')company
ON company.sofCompGuid = sci.Guid
WHERE ('%Name%' = '%' OR LOWER (sci.Name) LIKE LOWER ('%Name%'))
AND ('%Version%' = '%' OR sc.[Version] LIKE '%Version%')
AND ('%Company%' = '%' OR LOWER (company.Name) LIKE LOWER ('%Company%'))
AND sci.[CreatedDate] >= '%Discovered since%'
ORDER BY sci.Name
Currently the drilldown for the report drills down to report item "Drilldown to Installed Software" - I assume that's a standard drilldown report since I've cloned the original top level report.
How do I ensure that my drilldown also includes the same Organizational Group scoping that the top level report includes and only returns the PCs in the OG that I scoped at the top level? I've never editied a drilldown report before, I can't even find them listed!