Jump to content

Reporting on asset Owners / Used by INCLUDING the previous Owners / Used by?


samwoo
 Share

Recommended Posts

Hello,

Does anyone have any tips for creating a report where I can search by the customer's FIrst Name and Last Name, as well as that person's Asset History in a single report?

I've tried doing it using the Audit table and Assets table but it's proving a little tricky... I hardly ever use the Service Manager Reporting Tool in Hornbill and tend to rely on Database Direct for any one off or infrequent queries, but this report may be run multiple times by different users every week... who have little to no SQL knowledge and i'm stuck.

Thanks,

Samuel

Link to comment
Share on other sites

I made a mistake in the title - it should read

Reporting on a Customer's Assets as well as their Asset History

 

AND I also made a mistake in the first line of my post - it should read

Does anyone have any tips for creating a single Report for a Customer's current Assets, as well as their Asset History?

Link to comment
Share on other sites

Hello @samwoo,

if I understand well, you are searching for a report like the one in the image ? 
In the example, the customer is 'Support Manager', and there are only 2 assets associated with him:

Asset 10247 which he's currently using;
Asset 10166 which he was using until he was reassigned to a different user (Change analyst)

Regards

Armando
 

image.png

Link to comment
Share on other sites

Hi @ArmandoDM,

That is effectively what I am looking for, but being able to include some asset details such as the status, description etc.

Thanks,

Samuel

Link to comment
Share on other sites

1 hour ago, ArmandoDM said:

Hi @samwoo

here is the file if  you like to have a look

assets.report.txt

 

Regards

Armando

Hi @ArmandoDM,

Many thanks for that - i'm struggling to also incorporate the "Owned By Name" in there alongside the "Used By Name" :( Might you have an idea of how to put these separate fields, onto a single line? I tried to use 2 audit tables with aliases as well as 2 h_cmdb_assets tables with aliases, but I gave up after trying after returning no results.

Is there a possibility of having a way to incorporate SQL into the Reports area for scheduling one day? I know people have asked for it before, but not sure what the current status is? To be fair a lot of people in my department are fluent in various different types of SQL so i doubt it would be an issue.

Thanks,

Samuel

Link to comment
Share on other sites

Hi @samwoo

if you need extra info about the current values of the asset, you don't need to add any extra join.
If you need extra info about the asset history (e.g. the changes in the Owned By Name ) on separate lines, you do not need to add any extra join.

If you need more info about the asset history on the same line, then you need to add extra joins.  Nonetheless, I strongly discourage from adding extra joins , as the query may become very slow and affect the performances also for other users.

Here is the report definition where I added the current Owned By Name and the historical changes for this property, on separate lines.

Regards
assets.report.txt
Armando




 

Link to comment
Share on other sites

Hello @ArmandoDM,

Thanks for your assistance, I am getting the hang of the Service Manager reporting a little bit. I recently found out that I could actually in fact use part SQL in a custom query to reference other tables if necessary.

Anyhow I will take on your suggestions and give this another go later today.

Many thanks,

Samuel Wood

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...