Hi Alex,
To do this you would need to create a Database View. I actually answered a question very similar to this recently in another forum and am planning to do a blog post, but have not yet had the opportunity. This topic is also covered in the next release of our Asset Management class, as it is a bit of common information that Asset Managers want to report on.
Here are the reasons you need a Database View with a left join for this:
- You want to report based on users, but the one-to-many relationship here means that the assets point to a user. The user record does not have a direct reference to the assets.
- The view allows you to merge the tables based on the sys_id of the user matching the assigned_to of the asset so that each match is displayed as a single row.
- The left join allows all users to display, even if they do not have a corresponding asset, which is vital to the report in this case.
I have an update set, created on Eureka, if you want to take a look at a sample of what I put together in responding to the other question, which was to see all users without an assigned computer. It should give you an idea of what to do.
Here is a screenshot of the Database View I created to do this and the Try It so you can see what columns I selected to display in the view. You can easily create a report that shows where Name is empty to get the information you need.
Please let me know if you have any questions.
Ben
Hi Alex,
To do this you would need to create a Database View. I actually answered a question very similar to this recently in another forum and am planning to do a blog post, but have not yet had the opportunity. This topic is also covered in the next release of our Asset Management class, as it is a bit of common information that Asset Managers want to report on.
Here are the reasons you need a Database View with a left join for this:
- You want to report based on users, but the one-to-many relationship here means that the assets point to a user. The user record does not have a direct reference to the assets.
- The view allows you to merge the tables based on the sys_id of the user matching the assigned_to of the asset so that each match is displayed as a single row.
- The left join allows all users to display, even if they do not have a corresponding asset, which is vital to the report in this case.
I have an update set, created on Eureka, if you want to take a look at a sample of what I put together in responding to the other question, which was to see all users without an assigned computer. It should give you an idea of what to do.
Here is a screenshot of the Database View I created to do this and the Try It so you can see what columns I selected to display in the view. You can easily create a report that shows where Name is empty to get the information you need.
Please let me know if you have any questions.
Ben
