Jump to content

  • 0

Cross reference multiple collections in custom report?



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 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.

Share this post

Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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.

Share this post

Link to post
Share on other sites

  • 0

This will do what you want.

	v_FullCollectionMembership_valid FCMV 
	join v_FullCollectionMembership_valid FCMV2 on FCMV.ResourceID = FCMV2.ResourceID
	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/

  • Like 1

Share this post

Link to post
Share on other sites

  • 0

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!

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.