Report Prompt is not limiting to collection 1

[billybarty]

This is the prompt I have added to a report that lists specific programs in add/remove

Name: CollID
Prompt Text: Collection ID

begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end

The collection I select only contains our servers on one segment but when the report runs the data it returns is for all systems not just the collection I specify.
An example is that we have Mcafee antivirus on only a few of our servers on that segment but it is installed on all our desktops. If I run the query that has the statement for Mcafee in it and select the collection that only contains our servers in the prompt I get a results page that shows Mcafee installed on 200+ systems, not just the servers in the collection but all systems.

Any help is appreciated

 

[Mich]

The prompt seems fine. What is the query you're using with this prompt?

-If it ain't broke, break it and make it better.

 

[billybarty]

This is one of the statements I have for one product we are following. I have similar statements in the query for all the software products.

SELECT DisplayName0 as 'Adobe Acrobat 6.0 Standard', Count(ProdID0) AS 'Count', Version0
FROM v_GS_ADD_REMOVE_PROGRAMS
WHERE DisplayName0 like "Adobe Acrobat 6.0 St%%"
GROUP BY DisplayName0, Version0
ORDER BY Version0

I added AND adv.CollectionID = @collid
after the WHERE statement and received this error when running the report
Parameters: Collection ID RS1000C6

An error occurred when the report was run. The details are as follows:
The column prefix 'adv' does not match with a table name or alias name used in the query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 107

 

[johnnyb99]

You added "adv.CollectionID", but did not specify the table prefix or join to v_Collection.

Try this:

SELECT arp.DisplayName0 as 'Adobe Acrobat 6.0 Standard', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_Collection adv On arp.ResourceID=adv.ResourceID
WHERE arp.DisplayName0 like "Adobe Acrobat 6.0 St%%" AND adv.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

 

[johnnyb99]

Oops, I made a mistake ....

One needs to join to v_GS_ADD_REMOVE_PROGRAMS to v_FullCollectionMembership, via ResourceID, and check CollectionID in v_FullCollectionMembership.

Try this:

SELECT arp.DisplayName0 as 'Adobe Acrobat 6.0 Standard', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "Adobe Acrobat 6.0 St%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

 

[billybarty]

Thanks for the help. I've been able to modify my statements for some of the software to test the report and it works for some of the software but when I run the whole statement that I am testing I get this error:
Report Name: Test report
Category: Software - Companies and Products
Comment:
Parameters: Collection ID RS1000C6


Test report

An error occurred when the report was run. The details are as follows:
The column prefix 'fcm' does not match with a table name or alias name used in the query.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 107
I have looked at the statements and I don't see any difference between the ones that work and the ones that don't.
Here are the statements I modified so far:

SELECT arp.DisplayName0 as 'Adobe Acrobat', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "Adobe Acro%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Adobe Reader', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "Adobe Read%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'CallPilot', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "CallPi%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Crystal Enterprise', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "Crystal Ent%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Crystal Reports', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID
WHERE arp.DisplayName0 like "Crystal Rep%%" AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Crystal Reports for Exchange', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Crystal Reports for Exchange"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Seagate Crystal Reports', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Seagate Cry%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Data Access Objects', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Data Acc%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'GFI LanGuard NSS', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "GFI Lan%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'IBM DB2', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "IBM DB2"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'IBM Host on Demand', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "IBM H%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0


SELECT arp.DisplayName0 as 'Java2SE Runtime', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Java2SE Run%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Java2 Platform', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Java2SE Pla%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Java 2 Runtime', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Java 2 Run%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0


SELECT arp.DisplayName0 as 'Java 2 SDK', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Java 2 SDK, SE v%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Java 2 Webstart', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Java 2 Web%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Lotus Domino', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Lotus Dom%%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'LOtus iNotes', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Lotus iN%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

SELECT arp.DisplayName0 as 'Lotus Notes', Count(arp.ProdID0) AS 'Count', arp.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS arp
WHERE arp.DisplayName0 like "Lotus No%%"
AND fcm.CollectionID = @collid
GROUP BY arp.DisplayName0, arp.Version0
ORDER BY arp.Version0

 

[johnnyb99]

Your missing "JOIN v_FullCollectionMembership fcm On arp.ResourceID=fcm.ResourceID" from the queries for "Crystal Reports for Exchange" down ... that's why the message regarding "fcm" table prefix.

I would also recommend that in your LIKE statements use only one "%", which represents any string of zero or more characters, and would not use LIKE without "%", for example, change your check for "IBM DB2" to '= "IBM DB2"'.

 

[billybarty]

Thanks again for all your help. Apparently once I screwed up one my copy and paste didn't help matters.