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

[samwoo]

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

[samwoo]

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?

[ArmandoDM]

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
 

[samwoo]

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

[ArmandoDM]

Hi @samwoo,

I can send to you the report definition file for you to have a look and modify according to your requirements, if you like ?

As far the asset State and Operation State, you can get their 'keys' ,  not sure if you can get the String values  

 

[ArmandoDM]

Hi @samwoo

here is the file if  you like to have a look

assets.report.txt

 

Regards

Armando

[samwoo]

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

[ArmandoDM]

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




 

[samwoo]

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

[ArmandoDM]

Hello @samwoo

no problem at all. Please let me know if you need further assistance

Regards

Armando