How to clean data for a param

[Former Member]

I have some data that is being used in the report also as a param. from the server it is not such clean data

for example the name of the user

'Joe Smith 0000000000'

all those zeros. How best to remove the zeros? THis is used in the report as a group but there I can just limit the length but not in the param it shows the zeros.

[Former Member · Accepted solution]

If you have access and control over the database then the best option is to 'update' the column by removing the trailing zeroes. for e.g:

Update table_name Set User_Name= RTRIM(Replace(User_Name,'0',' '))  

this will ensure that all trailing zeroes are deleted

If this is not possible, then one of the option is to use a main report- sub report feature. The formula in the main report will remove the zeroes from the field and the subreport's parameter would be linked to the main report's formula.

Formula for the main report would be similar RTrim(..)

-Prathamesh

[abhilash_kumar]

Hi Paul,

Two options:

1) Use Business View Manager to created the LOVs

2) Add a SQL Query via the Command Object in the same report. The SQL query would need to use functions that get rid of the zeroes. This command object SHOULD NOT be linked to other tables in the report. The parameter can be re-created to use the field from this command object.

-Abhilash