Asset Management Suite

 View Only

  • 1.  Query Help

    Posted Jul 19, 2012 01:51 PM

    I have a report that pulls the first and last name of the assigned owner.  For some reason, when there is no owner assigned, it does not pull information for that asset.  I need it to pull all assets and include the owner information when it exists.  Can someone take a look below and see what I may be doing wrong? Any help is appreciated.

     

     

    declare @RAAssetUser uniqueidentifier       
           
           
    set @RAAssetUser = 'ed35a8d1-bf60-4771-9dde-092c146c485a'       
           
    --remove rowcount contraints as this will limit result sets - default for reporting is 50000       
    SET ROWCOUNT 0        
           
    select distinct       
    va.[_ResourceGuid] [_ResourceGuid],        
           
    dbo.fnLocalizeStringByGuid('item.name',va._ResourceTypeGuid, '%_culture%') [Asset Type],        
    vri.[Name] [Name],       
           
           
           
    htLoc.Name [Location],       
                 irn.[Number][Room Number],       
                 iai.[SCC Tag #][SCC Tag#],       
    va.[Serial Number] [Serial Number],       
                 va.[Manufacturer] [Manufacturer],     
                 va.[System Number][System Number],   
                 va.[Model] [Model],       
                 u.[Given Name] [First Name], u.Surname [Last Name],   
                 htCC.Name [Cost Center]       
           
           
    from       
    vAsset va       
    join %SecurityFilterParameter% vri        
    on vri._ResourceGuid = va._ResourceGuid       
                 join ResourceAssociation ra ON va._ResourceGuid = ra.ParentResourceGuid   
                 join vUser u ON u.guid = ra.ChildResourceGuid   
                 join Inv_Room_Number irn on (irn._ResourceGuid = va._ResourceGuid)       
                 join Inv_Asset_Information iai on (iai._ResourceGuid = va._ResourceGuid)    
                    
    join ResourceTypeHierarchy rth       
    on (rth.ResourceTypeGuid = va._ResourceTypeGuid)       
    left join ResourceAssociation raAL       
    on (raAL.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C') -- Asset:Location ResourceAssociationType       
    and (raAL.ParentResourceGuid = va._ResourceGuid)       
    left join fnAssetHierarchyTreeExcludeChildren       
    (       
    '834BC951-D70F-48F4-9E8E-D7E32C68788D', -- Location ResourceType       
    '',       
    default,       
    '%Location%',       
    default       
    ) htLoc       
    on (htLoc.Guid = raAL.ChildResourceGuid)       
    and ((htLoc.Guid <> 0x0) or ('%Location%' = cast(0x0 as uniqueidentifier)))       
    left join ResourceAssociation raAC       
    on (raAC.ResourceAssociationTypeGuid = '9BC22D17-C0CF-45D5-9A8F-D62BBAFD955D') -- Asset:CostCenter ResourceAssociationType       
    and (raAC.ParentResourceGuid = va._ResourceGuid)       
    left join fnAssetHierarchyTreeExcludeChildren       
    (       
    '03E525D8-43B1-4AE3-8592-9A9E5AE9C8C0', -- CostCenter ResourceType       
    '',       
    default,       
    '%CostCenter%',       
    default       
    ) htCC       
    on (htCC.Guid = raAC.ChildResourceGuid)       
    and ((htCC.Guid <> 0x0) or ('%CostCenter%' = cast(0x0 as uniqueidentifier)))       
    where (1=1)       
    --// filter by Type //--    
                   
    and ((rth.BaseResourceTypeGuid = '%Type%') or ('%Type%' = cast(0x0 as uniqueidentifier)))       
    --// filter by Location and IncludeAllLoc //--       
    and ((htLoc.Guid = '%Location%') or ('%Location%' = cast(0x0 as uniqueidentifier)) or (%IncludeAllLoc% = 1))       
    and ((htLoc.Guid <> 0x0) or ('%Location%' = cast(0x0 as uniqueidentifier)))       
    --// filter by CostCenter and IncludeAllCC //--       
    and ((htCC.Guid = '%CostCenter%') or ('%CostCenter%' = cast(0x0 as uniqueidentifier)) or (%IncludeAllCC% = 1))       
    and ((htCC.Guid <> 0x0) or ('%CostCenter%' = cast(0x0 as uniqueidentifier))   
                 and (ra.ResourceAssociationTypeGuid = 'ED35A8D1-BF60-4771-9DDE-092C146C485A' ))      
    order by [Location],[Room Number]        

     



  • 2.  RE: Query Help

    Posted Aug 14, 2012 03:48 PM

    Maybe I'm missing the complexity of the problem but I think if you simply modify the select of

     u.[Given Name] [First Name], u.Surname [Last Name], to read

     ifnull(u.[Given Name],'not assigned'), ifnull( u.[First Name], 'not assigned'), ifnull( u.Surname [Last Name], 'not assgned'),

    that would allow you to select the fields even if they are empty. 

    However it may also be a simple join problem try playing around with join vUser u ON u.guid = ra.ChildResourceGuid .  I think this should be a right join as you want to select all the ra objects even if there is no U. objects.  an inner join, which you are using will give you the results you are reporting now.  A right join should select all the ra objects and join them to U if there are any to join.

    I would suggest posting the exported xml of your report so others could test.  As it stands because of the wildcard paramaters I cant test if this would work.

     



  • 3.  RE: Query Help
    Best Answer

    Posted Aug 15, 2012 09:24 AM

    Good Morning,

    Try this..

    Replace this line

    join vUser u ON u.guid = ra.ChildResourceGuid   

    with

    left outer join vUser u ON u.guid = ra.ChildResourceGuid   



  • 4.  RE: Query Help

    Posted Aug 15, 2012 09:55 AM

    It did the trick!!  Thanks so much!