Changing resource view
Updated: 22 May 2010 | 4 comments
This issue has been solved. See solution.
Can anyone tell me how to change the query choices that exist when viewing Assets? I need to add a user-defined field to the view. For instance, if you choose to Show Assets by Type, Status, Department, and Location -- it shows the following information: Asset Type, Name, Status, Department, Location, Serial Number System Number.
I need to add the room number to the query and then have it available in the Show drop down box. Any help is appreciated.
Discussion Filed Under:
Comments
If it's where I think you're
If it's where I think you're taking about that's pretty easy to do. You'll need to write a report to display that information then add it to the list available on your View.
I wrote an article on that awhile back, you can find it here: https://www-secure.symantec.com/connect/articles/asset-report-writing-tips
Let me know if you have questions.
- Matt
Thanks for the info......still have questions
I read the article.......thanks for the information. I am really new to trying to write reports. I created a user-defined field to hold the room number. I need to add this field to this query. Here is my query:
--remove rowcount contraints as this will limit result sets - default for reporting is 50000
SET ROWCOUNT 0
select
va.[_ResourceGuid] [_ResourceGuid],
dbo.fnLocalizeStringByGuid('item.name',va._ResourceTypeGuid, '%_culture%') [Asset Type],
--va.[Asset Type] [Asset Type],
vri.[Name] [Name],
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 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 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]
Any help you can give would really be appreciated.
You need to find the Table
You need to find the Table and Column your new field is in. That should be really easy to find. Normally the name you need for the Table is the name of the Data class with _ where a space would normally be. The Column is likewise named after the name of the "Attribute" you've given it.
For example:
The attribute "Room number" in the dataclass "Extended Location Details" would be column [Room_number] on table [Extended_Location_Details].
You'd join the new table using the _resourceGuid field your other tables use. You'll connect it to the vAsset or va table.
Example:
join Extended_Location_Details eld on eld._resourceGuid on va._resourceGuid
If you want to display it make sure to add it to the first "Select section"
Example:
eld.[Room_Number] as 'Room Number'
Then add it to your where statement for a select. When you do this you'll use a wildcard to represent the "parameter" so you can put a selection box on it. As a force of habit I always put an underscore in front of my parameters so they're easier for me to spot.
Example:
and eld.[room_number] = '%_room%'
Finally you need to setup the Parameter on your query to do that you can click the "new Parameter" button on either the main queries page or under the "level 0" selection.
There you name the parameter _room.
I recommend starting with a Basic parameter, Check to prompt box, enter some text, and preset the Value to a % sign. That way it pulls all the records at once.
Hopefully that takes care of it. Let me know if it doesn't
- Matt
I finally got it working :)
Just wanted to give a big thanks for the help. I finally got it working :)
Would you like to reply?
Login or Register to post your comment.