SQLTeam.com | Weblogs | Forums

Blank report w/ headers


#1

Hey,
I have a report with a prompt for collection that produces the headers, but no data. I've been going in circles. Here is my code:
Dataset1
select distinct v_R_System.Netbios_Name0 as 'Computer Name',
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 as 'IP Address',
v_R_System.User_Name0 as 'User Name',
v_R_User.Full_User_Name0 as 'Full User Name',
v_R_System.AD_Site_Name0 as 'Site'
from v_R_System
inner join
v_RA_System_IPAddresses on
v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceId
join
v_R_User on
v_R_User.User_Name0 = v_R_System.User_Name0
join
v_GS_NETWORK_ADAPTER_CONFIGURATION on
v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID = v_RA_System_IPAddresses.ResourceID
left join v_FullCollectionMembership on
v_FullCollectionMembership.resourceid = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
join v_Collection on
v_Collection.CollectionID = v_FullCollectionMembership.CollectionID
where (v_FullCollectionMembership.CollectionID = @collid ) AND v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0
NOT LIKE 'fe%' AND IPAddress0 NOT LIKE '%::%' AND IPAddress0 NOT LIKE '169%'
group by
v_R_System.Netbios_Name0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,
v_R_System.User_Name0,
v_R_User.Full_User_Name0,
v_R_System.AD_Site_Name0
order by v_R_System.Netbios_Name0

Dataset2
SELECT CollectionID, Name FROM v_Collection order by Name


#2

Probably 1 of your joins should be switched to a Left join to include NULLs;

I can't really say which without knowing what data is in these tables and the relationship between them.


#3

Ok, finally got it working. Thank you for all your help and guidance.