/build/static/layout/Breadcrumb_cap_w.png

K1000 Reports: Truncate Results?

We run a daily report to cobble together the previous days' tickets to send off to various managers/directors for their review. For the most part, it works well, but it returns every bit of text in a custom "Description" field. This is a custom field for the client to describe the issue, and some have paragraphs of text. As you can imagine, if paragraphs of text are in that field, the reports, if created in PDF as they request, look crazy. 

I'd like to modify the report query to truncate the results from that Description field to 100 characters, but am at a total loss as to how to do so, as my query skills are pretty shaky. Any help would be greatly appreciated.

Here's the query:

 select HD_TICKET.ID, 
       HD_TICKET.TIME_OPENED,
       HD_TICKET.TITLE,
       HD_TICKET.CUSTOM_FIELD_VALUE3 as DESCRIPTION,
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_TICKET.TIME_OPENED > DATE_SUB(NOW(), INTERVAL 24 hour)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
To return the first 100 characters, try replacing HD_TICKET.CUSTOM_FIELD_VALUE3 as DESCRIPTION with substring(HD_TICKET.CUSTOM_FIELD_VALUE3, 1, 100) as DESCRIPTION
Posted by: grayematter 10 years ago
5th Degree Black Belt
1

To return the first 100 characters, try replacing

HD_TICKET.CUSTOM_FIELD_VALUE3 as DESCRIPTION

with

substring(HD_TICKET.CUSTOM_FIELD_VALUE3, 0, 100) as DESCRIPTION

For details on the substring function, check http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substr.

 


Comments:
  • Thanks. That almost worked. As written, it returned nothing for the description field. Using that link, I modified it to:
    substring(HD_TICKET.CUSTOM_FIELD_VALUE3, 1, 100) as DESCRIPTION

    That worked and limited the report results. Thanks for the help! - tshupp 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ