SQLTeam.com | Weblogs | Forums

Designer on each dataset

sql2012

#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

Did you execute the query in SSMS and check the results?

Try this

select distinct fcm.CollectionID,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.User_Name0 = v_R_System.User_Name0
JOIN v_FullCollectionMembership fcm
on fcm.ResourceType = v_R_System.ResourceType
WHERE fcm.Collectionid= @Collection
ORDER BY v_R_System.Netbios_Name0