I was trying below report which was OK. What I wanted collection value e.g. '5000000C'  is to be as user input.

************

SELECT arp.DisplayName0, sys.User_Name0, sys.Netbios_Name0, arp.Publisher0, arp.Version0
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE
 fcm.CollectionID = '5000000C'
AND
( arp.DisplayName0 IS NULL OR (
arp.DisplayName0  NOT LIKE  '%iSeries%'
AND  arp.DisplayName0  NOT LIKE  '%Adobe%'
))
AND 
( arp.Publisher0 IS NULL OR (
arp.Publisher0   NOT LIKE  '%Adobe%'
AND  arp.Publisher0   NOT LIKE  '%Microsoft%'
))
ORDER BY DisplayName0, Version0
*******************************

I have got separate query to get the valu of Collection field:

********************************

begin
 if (@filterwildcard = '')
  SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs)  Where CollectionType = 2 ORDER BY Name
 else
  SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs) 
  WHERE CollectionID like @filterwildcard and CollectionType = 2
  ORDER BY Name
end

********************************

If I click values pop up list should come from 2nd query. based on the value 1st query will display.

How can I pass values from the 2nd query to the 1st query. Please help.

 

 

• Moved by arnavsharmaMVP Sunday, August 18, 2013 2:46 PM :Posted in SCCm 2007

Just open an existing SRS report that also prompts for the input of a collectionID and see how this can be done.

Hi,m

Here is an example on how to do it..

http://asithadesilva.wordpress.com/2013/04/01/how-to-prompt-collection-to-a-report-in-sccm-2012/

Regards,
Jrgen

Thanks Jorgen

I have exactly followed the blog. While I was saving the report at the end below error has come up. any idea?

Yes, I know this is an old post, but Im trying to clean them up. Did you solve this problem, if so what was the solution?

The way I read this error is that the user running the report does not have RBA rights to that collection and therefore cant run the report with the default value.

BTW I never recommend runt he reports via the console, I only recommend running the report via the IE interface.