Decoding values in Reporting

[samwoo]

Hello,

I was wondering if it was possible to decode values in Reporting.

I need to report on Assets, but the Asset States are returning as numerical values. I am aware these values are hard-coded into the system so they is no table to get them out of... Can someone point me in the direction of being able to decode these values (0 = Active, 1 = Current, 2 = Archived) for instance without having to do it in Excel each time the report is extracted?

Alternatively... might we see the possibility of being able to change/add Asset states and to be able to access their values from a table? Substates have been good but some Substates still doesn't fit with the current available States.

Many thanks,

Samuel

[samwoo]

Hi,

I am also finding that the operational states are returning as numerical values... I cannot find a table where this information is held.

I realised that DECODE in MySQL is different from Oracle SQL.

To change my query slightly - I am looking for a way to define alternative values based the results returned. So if the field returns '1' I want to replace '1' with 'Current' for example. If the field returns '2' I want to replace '2' with 'Active' and so forth... allowing multiple values to be defined for multiple fields.

It's confusing the people in my Team as they do not know what the numerical values mean when they do reporting.

Thanks,

Samuel

[Victor]

@samwoo you could try using the CASE statement. For example:

SELECT <field_list>,
CASE status WHEN 1 THEN 'Current' WHEN 2 THEN 'Active' END AS translated_status

I am not sure if the above syntax is 100% correct (Google might correct me  ) but this is the general idea...

[samwoo]

12 minutes ago, Victor said:

@samwoo you could try using the CASE statement. For example:

SELECT <field_list>,
CASE status WHEN 1 THEN 'Current' WHEN 2 THEN 'Active' END AS translated_status

I am not sure if the above syntax is 100% correct (Google might correct me  ) but this is the general idea...

Hi @Victor

Thank you for your response - yes that is correct (we could use an IF statement or a CASE statement), but sadly we cannot write SQL in the Reporting -> Reports area (https://admin.hornbill.com/<instance name>/app/com.hornbill.servicemanager/reporting/), only in Database Direct... which isn't ideal to the user who just wants to click a button to produce the report.

However if we could schedule and produce outputs from Database Direct then I would have most certainly done it this way, but this isn't the case.

Thank though,

Samuel

[Victor]

Ah...reporting... *sigh... maybe one of my colleagues knows more or has a better idea. I'll ask around.

[samwoo]

8 minutes ago, Victor said:

Ah...reporting... *sigh... maybe one of my colleagues knows more or has a better idea. I'll ask around.

Thanks Victor - I'm a raw SQL guy myself, having a system that does it for me is great... but is severely limited, I would prefer to be able to type it all myself but the way it is set up in Hornbill isn't user-friendly to someone without SQL knowledge, so I can see why Reporting is good for these users.

[Lyonel]

35 minutes ago, samwoo said:

Thanks Victor - I'm a raw SQL guy myself, having a system that does it for me is great... but is severely limited, I would prefer to be able to type it all myself but the way it is set up in Hornbill isn't user-friendly to someone without SQL knowledge, so I can see why Reporting is good for these users.

Totally agree with that! I would also love to be able to design my own query in pure SQL (alike the data > direct db screen). That would make reporting much easier for me