Blank report w/ headers

  • maravig

    Mr or Mrs. 500

    Points: 582

    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

    Thanks for any help

  • Luis Cazares

    SSC Guru

    Points: 183581

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • maravig

    Mr or Mrs. 500

    Points: 582

    What else do you require?  screenshots?  the Parameter settings?  My apologies, I'm a SQL coding newbie

  • Luis Cazares

    SSC Guru

    Points: 183581

    maravig - Friday, February 24, 2017 9:56 AM

    What else do you require?  screenshots?  the Parameter settings?  My apologies, I'm a SQL coding newbie

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • maravig

    Mr or Mrs. 500

    Points: 582

    ok, Thank you for responding!

  • maravig

    Mr or Mrs. 500

    Points: 582

    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.

  • maravig

    Mr or Mrs. 500

    Points: 582

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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply