Showing Related List attributes in List View

[jonny27]

Hello!

I'm trying to create a view, or a report (either/or) that shows a list of ALL users in my company, their department, manager, location etc, but I'd also like to include the amount of assets, and the asset names that are assigned to them so basically we can see how many assets a user has assigned to them, whether it be 0 or 100.

I think I'm right in saying that I'd need to start from the Users table as that has all of the information I need to start with. I can show unassigned assets no problem, but I really want to find users who have no assets assigned to them and/or users who have more than a normal amount of assets.

Any clues?

Thank you

Your Friendly Yorkshireman

[Josh Cooper]

You may be able to do it with a Database view, by linking the sys_user table with the asset table (where asset assigned to = user.sys_id).   It basically lets you make a big conglomerate table with all the fields from both.

Note that the product is going to be that you're going to get a row for every combination, so if you Group by User, you'll get all of the assets assigned to that user, but you'll also have access to all the fields on the user record as well as all the fields on the Asset record.

You MIGHT be able to use that report to also see users with 0 assets, but I'm not positive - it may be easier to just keep them as two seperate reports, and put them together on the same dashboard or home page, if you need to see them both in one place.

Here's on Database Views:

Database Views - ServiceNow Wiki

And some best practices from the Community:

DATABASE VIEWS* for beginners.

[jonny27]

Hi Josh

Thanks for the suggestion, mate. I'll give it a whirl.

Cheers