Jump to content


auhn

Cross reference multiple collections in custom report?

Recommended Posts

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.

Share this post


Link to post
Share on other sites


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.

Share this post


Link to post
Share on other sites

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

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/

  • Like 1

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

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

Loading...

×
×
  • Create New...