Service Desk Practitioners Forum
cancel

Reporting on custom fields in Hyperion reports

SOLVED
Go to solution
Highlighted
Justin Trant
Super Contributor.

Reporting on custom fields in Hyperion reports

Hi,

We are using a custom field (scf_cod8_oid) as a boolean field, to track Service Calls that have breached deadline.

When designing reports in Hyperion we are trying to convert the database codes found in the custom field (two, which indicate yes/no) into the actual text itself.

We have linked the custom field to itsm_codes.cod_oid but when we run the query it returns no rows. When we remove the link the report runs fine again.

Has anyone had any experience with reporting on custom fields and how to translate the db values (oid) into meaningful text?

Thanks in advance,
Justin.
5 REPLIES
Tim Schmitt_4
Honored Contributor.

Re: Reporting on custom fields in Hyperion reports

In the case of a boolean field, I don't think that there is any information stored in the database regarding the choices.

When you create the boolean, you can choose to display the field as a checkbox, yes/no question, true/false, or a button. All of the options store the value 1 for yes and 0 for no in the database and the gui interprets how to display the item based on how the custom field is setup.

I could be wrong but that is my understanding of how the data is stored in the db.

Justin Trant
Super Contributor.

Re: Reporting on custom fields in Hyperion reports

Hi,

Thanks for your reply. If I go to 'Show values' in Hyperion designer, it shows two values (see attachment).

My other option is to create a statement to show 'Yes' where Actual Finish is greater than Deadline.

Thanks.
George M. Meneg
Acclaimed Contributor.
Solution

Re: Reporting on custom fields in Hyperion reports

Hello Justin,

I don't know this tool however you have to include three tables

itsm_servicecalls
itsm_ser_custom_fields
itsm_codes_locale

the joins must be

itsm_servicecalls inner join itsm_ser_custom_fields on itsm_servicecalls.ser_oid=itsm_ser_custom_fields.scf_ser_oid left outer join itsm_codes_locale on itsm_codes_locale.cdl_cod_oid=itsm_ser_custom_fields.scf_cod8_oid

You must also include a filter on cdl_lng_oid in order to fetch only one locale. For example cdl_lng_oid=1033 will fetch only US english text.
menes fhtagn
Justin Trant
Super Contributor.

Re: Reporting on custom fields in Hyperion reports

George, that worked great - thanks so much for your help.

Justin.
Justin Trant
Super Contributor.

Re: Reporting on custom fields in Hyperion reports

Problem solved :)

Points assigned.