Blank report w/ collection prompt

1

Hi,
My report properly prompts for a collection name, but then yields nothing. If I run Query Designer on each dataset, they work fine. Any idea what I'm missing?

Dataset1
select distinct v_R_System.Netbios_Name0 as 'Computer Name',
V_GS_OPERATING_SYSTEM.Description0 as 'Image',
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 as 'Last Logged On User',
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 as 'Last Logged On Time',
V_R_User.Full_User_Name0 as 'Full User Name'
from v_R_System
JOIN
v_GS_OPERATING_SYSTEM on
v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
JOIN
V_GS_SYSTEM_CONSOLE_USER on
V_GS_OPERATING_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID
JOIN
v_R_USER on
v_R_USER.ResourceID = v_R_System.ResourceID
JOIN
v_FullCollectionMembership fcm on
fcm.ResourceType = v_R_System.ResourceType
WHERE fcm.Collectionid= '@Collection'
ORDER BY v_R_System.Netbios_Name0

Dataset2
select CollectionID, Name from v_Collection order by Name

2 

WHERE fcm.Collectionid= '@Collection'

should the above be


WHERE fcm.Collectionid= @Collection
3

Thank you for responding. I made the change, but the report still comes up blank.

4

is @Collection of data type int?

5

No, it's text because I'm having the entire list of collection names to choose from. Not the Collection ID field.

6

is fcm.Collectionid also text?

7

In the database, it's string

8
  1. While you have your report open also open SQL Profiler then see what that parameter looks like in the wire. What does the parameter value look like in SQL Profiler?
  2. try also
WHERE rtrim(ltrim(fcm.Collectionid)) = rtrim(ltrim(@Collection))

This is just for test purposes to see if there could be some empty padding of some sort is causing this issue

9

ok will do, thanks

10

In the Profiler, I found this:

WHERE fcm.Collectionid= @Collection
ORDER BY v_R_System.Netbios_Name0',N'@Collection nvarchar(8)',@Collection=N'TH10012E'

',@DataSourceInfo=0x0001000000FFFFFFFF01000000000000000C020000006E4D6963726F736F66742E5265706F7274696E6753657276696365732E50726F63657373696E67436F72652C2056657273696F6E3D31312E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579...and just keeps going

11

The main part you want to look at is the following

WHERE fcm.Collectionid= @Collection
ORDER BY v_R_System.Netbios_Name0',N'@Collection nvarchar(8)',@Collection=N'TH10012E'

so the question for you is: is TH10012E a valid Collectionid?
Do the following and if it exists

select * from v_FullCollectionMembership fcm WHERE fcm.Collectionid= N'TH10012E'
12

yes, it is valid CollectionID

13

Ok, almost there, got it working finally. However, my distinct is not actually applying. Here is the output:

14

and here is the code:

Anyone know why my distinct is not actually working??

15

with the 3 columns IP, Computername and username you should have less rows but adding the other rows last hw scan and last scan date might not make things distinct. show us those rows for that same user bhj3772

16

The picture was just first 3 columns to display the issue. There are multiple columns as this is a detailed report based on collection request. I've never been able to get the "distinct" to actually work. Is there some fundamental circumstances that need to be in place for it to be effective?

17

yeah so if there are more columns then you would have the issue for ex

10.52.2.174    2DCWDS    bhj3772   1.0.000000001
10.52.2.174    2DCWDS    bhj3772   1.0.000000002

In this case because the last column is slightly different your query will return two rows even if you have DISTINCT on this. So your other columns are making it so that you get multiple rows bcs they are indeed DISTINCT

18

ok, that makes sense. Thank you for all your help, it's greatly appreciated!