philipnet

This is something I often want to do. Whilst it’s great being able to see the results for all systems, but when it comes to putting my point across to management about a particular group of machines you want a report to match. 

To that end here is quick How To on how to take a SQL web report and limit it to a collection whilst utilising user friendly collection names :-) .

• Join in the table of SMS Collections which is called v_FullCollectionMembership:
  join v_FullCollectionMembership fcm on <table name>.ResourceID=fcm.ResourceID
• And then the key bit, which is to limit the results return to the specified collection:
  AND fcm.CollectionID = (Select C.CollectionID from v_Collection C where C.Name = @col)
• Lastly, you need to add a prompt that allows the user to specify what collection they are interested in:
  Name: col
  Prompt text: Collection
  Allow an empty value: <unticked>
  Provide a SQL statement:
  begin
   if (@__filterwildcard = '')
    select Name from v_Collection order by Name
   else
    select Name from v_Collection
    WHERE Name like @__filterwildcard
    order by Name
  end

  All being OK you will then be able to limit any reports to a specified collection. But don’t forget to to change the variables and table aliases as required.

  Happy reporting!