Cross reference multiple collections in custom report?

[auhn]

Hello,

I'm new to SQL and I need help understanding the best approach to our current problem. I need to build a custom parameter-based report in Microsoft SQL Report Builder 3.0 where you select two or more collections as parameters. The report should then cross reference which computers exist in all selected collections and output their name it to the report.

 

I have built a static query I can run against the SQL server directly, but we need to be able to dynamically select a collection for easier reporting purposes. This is the query I use:

SELECT v_GS_SYSTEM.Name0
FROM v_GS_SYSTEM
WHERE Name0 IN
 (SELECT Name from v_CM_RES_COLL_CMS0020B)
AND Name0 IN 
 (SELECT Name from v_CM_RES_COLL_CMS000D1)

I don't know how to proceed in how to make the above query into a parameter report in Report Builder, or how to otherwise get what we're looking for. Does anyone know? Any help is greatly appreciated.

[GarthMJ]

Are you taking about just two collection or more than two.

 

BTW I don't recommend using Report build to create reports, instead us SQL Server Data Tools. It can do more than RB.

[auhn]

Thank you for your response Garth. The objective is to cross reference 2 collections as that would definitively solve the great majority of our needs.

 

I'm unfamiliar with SQL Server Data Tools, but I'll be sure to look into it though per your recommendation.

[GarthMJ]

This will do what you want.

Select 
	* 
from 
	v_FullCollectionMembership_valid FCMV 
	join v_FullCollectionMembership_valid FCMV2 on FCMV.ResourceID = FCMV2.ResourceID
Where 
	FCMV.collectionid = @Var1 
	and FCMV2.collectionid = @Var2 

I know this is a bit self promo but IMO this is a great book. :-) https://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/

[auhn]

Gary, you hit the nail right on the head. The report now does exactly what we need it to do. After reading through the query multiple times and testing it out, I think I understand where I went wrong and how I can use this for future queries.

 

Thank you very much for your help!