Blank report w/ headers
-
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_Name0Dataset2
SELECT CollectionID, Name FROM v_Collection order by Name
Thanks for any help
-
What do you expect us to do with that? The only thing I can offer is to format the code, but not much more.
select distinct
S.Netbios_Name0 as 'Computer Name',
N.IPAddress0 as 'IP Address',
S.User_Name0 as 'User Name',
U.Full_User_Name0 as 'Full User Name',
S.AD_Site_Name0 as 'Site'
from v_R_System S
join v_RA_System_IPAddresses I on I.ResourceID = S.ResourceId
join v_R_User U on U.User_Name0 = S.User_Name0
join v_GS_NETWORK_ADAPTER_CONFIGURATION N on N.ResourceID = I.ResourceID
left join v_FullCollectionMembership M on M.resourceid = N.ResourceID
join v_Collection C on C.CollectionID = M.CollectionID
where (M.CollectionID = @collid )
AND N.IPAddress0 NOT LIKE 'fe%'
AND N.IPAddress0 NOT LIKE '%::%'
AND N.IPAddress0 NOT LIKE '169%'
group by
S.Netbios_Name0,
N.IPAddress0,
S.User_Name0,
U.Full_User_Name0,
S.AD_Site_Name0
order by S.Netbios_Name0
The only thing that seems to be an issue, is that the LEFT JOIN is being converted to an INNER JOIN by your WHERE clause. However, that's your only filter, so there's no reason to make it an (LEFT) OUTER JOIN. -
What else do you require? screenshots? the Parameter settings? My apologies, I'm a SQL coding newbie
- maravig - Friday, February 24, 2017 9:56 AM
There's no way to know why are you not getting results. The query seems fine, but you might be missing data. Although, you have a DISTINCT and GROUP BY which shouldn't be needed anyway.
You need to be sure that you should get results with the parameters that you're using.
You're not using v_RA_System_IPAddresses or v_Collection so there's no reason to have them in the query unless the DB is missing Foreign Keys.
SELECT S.Netbios_Name0 as 'Computer Name',
N.IPAddress0 as 'IP Address',
S.User_Name0 as 'User Name',
U.Full_User_Name0 as 'Full User Name',
S.AD_Site_Name0 as 'Site'
,M.CollectionID
FROM v_R_System S
JOIN v_R_User U on U.User_Name0 = S.User_Name0
JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION N on N.ResourceID = S.ResourceID
JOIN v_FullCollectionMembership M on M.resourceid = S.ResourceID
where /*(M.CollectionID = @collid )
AND */N.IPAddress0 NOT LIKE 'fe%'
AND N.IPAddress0 NOT LIKE '%::%'
AND N.IPAddress0 NOT LIKE '169%'
ORDER BY S.Netbios_Name0;
If you get any results, then use a value from CollectionID column as a parameter, remove the column and uncomment the WHERE clause.
If you don't get any results, it's because you don't have data available. -
ok, Thank you for responding!
-
I whittled the query down to the essentials. A couple of days ago, I had it working, but the report always displayed the exact the same data regardless of the collection selected.
-
Ok, finally got it working. Thank you for all your help and guidance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply