Shortening large columns in user facing reports

Where a database column and its content is larger than the size you wish to show in a report, it is easy to just substring the column value to fit your report column size. However, this often does not look that good in user facing reports.

To demonstrate this concept below I have used the application users description column in applsys.fnd_user which is a varchar2(240)...

SELECT fu.user_id user_id,
               fu.user_name user_name,
               fu.description description
   FROM applsys.fnd_user fu;



In most cases when you write a report you substring the column to the size of the column in your report. Whilst sub stringing the column value is the easy solution and in your mind acceptable, you should remember your reports are aimed at end users wouldn’t it look more professional if you added three (3) full stops to a sub stringed value to clearly indicate the column has been truncated.

SELECT fu.user_id user_id,
                fu.user_name user_name,
                substr(fu.description, 1, 30)
                decode(sign(length(fu.description) - 30), 1, '...') description
FROM applsys.fnd_user fu;

If your report column size is 30, set the two (2) values in the example SQL to 27, that is 27 characters plus 3 for the full stops.

A very simple piece of code but what a difference. You should consider using this for any user report where a data column that has been truncated.

Remember:
“Always make life easier for your target user”


- Gary