Asset Management Suite

 

I need to add additional filters based on user defined fields to this report. I am a newbie to this and know only basic SQL.  Can someone guide me on how to accomplish this?  Here is the Level 0 query so far:



--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],
--va.[Asset Type] [Asset Type],
vri.[Name] [Name],

dbo.fnLocalizeStringByGuid('item.name',va._AssetStatusGuid, '%_culture%') [Status],
--va.[Status] [Status],
htDep.Name [Department],
htLoc.Name [Location],
va.[Serial Number] [Serial Number],
va.[System Number]
from
vAsset va
join %SecurityFilterParameter% vri
on vri._ResourceGuid = va._ResourceGuid
join ResourceTypeHierarchy rth
on (rth.ResourceTypeGuid = va._ResourceTypeGuid)
left join ResourceAssociation raAU
on (raAU.ResourceAssociationTypeGuid = 'ED35A8D1-BF60-4771-9DDE-092C146C485A') -- Asset:User ResourceAssociationType
and (raAU.ParentResourceGuid = va._ResourceGuid)
left join ResourceAssociation raUD
on (raUD.ResourceAssociationTypeGuid = 'D9656701-B8EE-48C1-A4E3-5741CA18471D') -- User:Department ResourceAssociationType
and (raUD.ParentResourceGuid = raAU.ChildResourceGuid)
left join fnAssetHierarchyTreeExcludeChildren
(
'857B18CD-2603-4AC5-B8CC-B116BAE5BBBE', -- Department ResourceType
'',
default,
'%Department%',
default
) htDep
on (htDep.Guid = raUD.ChildResourceGuid)
and ((htDep.Guid <> 0x0) or ('%Department%' = cast(0x0 as uniqueidentifier)))
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)))
where (1=1)
--// filter by Type //--
and ((rth.BaseResourceTypeGuid = '%Type%') or ('%Type%' = cast(0x0 as uniqueidentifier)))
--// filter by AssetStatus //--
and ((va._AssetStatusGuid = '%Status%') or ('%Status%' = cast(0x0 as uniqueidentifier)))
--// filter by Department and IncludeAllDep //--
and ((htDep.Guid = '%Department%') or ('%Department%' = cast(0x0 as uniqueidentifier)) or (%IncludeAllDep% = 1))
and ((htDep.Guid <> 0x0) or ('%Department%' = 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)))
order by [Asset Type], [Name]

I'd be happy to help you out with your SQL if you can tell me exactly what you want the report to do.  I notice that some of your stuff above could be done a little more cleanly (no offense intended) and with some of it, it is hard to discern what you are trying to get back.

If you could give me a list of value you are looking to return, and what input you want the user to be able to give, I can give you a hand.

Just let me know.
Thanks!