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

[gdd]

Hi,

You can create report on assets table and keep assigned[user field] group on it, it will show you list of assets assigned to the particular user. here you can dot walk to reference fields. so you can fetch user table values.

put filter condition for the report as :

my company

Thanks

Girish Deshpande

[jonny27]

Hi Girish.

Thanks for taking the time to answer, but that's not quite what I'm after as it wouldn't show users who have zero assets assigned to them.

Thank you

[Dennis R]

I think there's an out-of-the-box related list called Configuration Item ->Assigned To that you can add to the bottom of the user form.

For a report, I don't think you'd be able to get an aggregate very easily without defining a new field and using business rules as assets are added/retired to keep the field synced up. (In my humble opinion, that's one of ServiceNow's weaknesses: The inability to display related aggregate data in a report.)

However, if you just want to query on it, the new report designer helps a lot.

To enable it, look up the system property called "glide.ui.report.new_report_designer" and set it to "true".   (Note that this enables it globally.)   Then in your report, set the source to the user table (sys_user).   Click on the little filter icon at the top just to the right of the Data > Type > Configure > Style panel and add any conditions you want to filter your user list down.

Then in the related list conditions, select Base Configuration Item->Assigned to, and click the aggregate number criteria link to change which users you want shown based on the number of assets assigned to them.   (Shown pointed to by the red arrow below.)

If you put conditions on the related list, you can filter it down prior to counting the number by entering conditions here.   (For example, users that don't have a specific type of computer.)

In the screenshot above, I've filtered the user list down to users that don't have any assets assigned to them.   You can just as easily change it to users who have more than five assets assigned to them, or any arbitrary condition.

Note that whether you have the new report designer enabled or not, the report will still work.   If you don't want to enable it globally, you have a few options:

• Enable it globally long enough for you to create your report, then turn it off.
• Enable it in a lower development instance, design your report there, then export the report record to XML and import it into your production instance.
• Enable it anyway and explain to your users that they can revert to the old designer by clicking the "Switch to classic UI" link at the top of the report designer (see above screenshot to the left of the Save button at the top).
• Manually edit the filter in your report record.   I don't think the RLQUERY operator is documented yet in the documentation, so you'll be kinda on your own in sussing out the syntax of it.   The filter I created above in my personal instance is:
  • ^RLQUERYcmdb.assigned_to,=0^ENDRLQUERY

Hope this helps,

--Dennis R

[jonny27]

Dennis

Thank you so much for your lengthy answer - that really helped me, but only half way there unfortunately. Let me explain:

I've added the assets assigned to on the bottom of the user form, along with consumables, their reportees, etc, etc. It's useful for looking up one by one but I really want that same kind of information available where I can see many people at once.

I've run through your suggestions with the reporting. It's got me so close! However I just cannot get the assets displayed on the resulting report as it only allows me to select fields from the User table and not the asset/hardware/CI table which is really quite frustrating!

It has got me to a decent start, though, where I can pass back to managers the users that have no assets assigned to them to find out what they should have and then I can reverse the tables when I want to show those who do have assets (by reporting on the asset table and showing where assets are assigned to) for now. But if I can get a list of all users and their assets on one ServiceNow table (without having to mash excel reports together) I'd be really chuffed.

Thank you.