1571509 - How to remove Commas from Parameter values (for eg. convert 1,200 to 1200) in Crystal Reports

SAP Knowledge Base Article - Public

1571509 - How to remove Commas from Parameter values (for eg. convert 1,200 to 1200) in Crystal Reports

Symptom

  • How to format the parameter list of values from (1,200 1,201) to (1200 1201), where 1,200 and 1,201 is stored in the database as a string value.

Environment

  • Crystal Report 2008
  • Crystal Report XI Release 2
  • SQL Server

Reproducing the Issue

  1. Create a table (e.g. job) with say Two Field in it (e.g. job_number and job_name).
  2. The Datatype of both the Field should be String, but for job_number add only Number Value's. 
  3. Add a Command Object in Crystal Report much like the one given below 
    1. Select cast(job_number as INT) as job_id,job_name from the job; 
  4. Create a Dynamic Parameter based on job_number. 
  5. You will see that a Comma is being added in the parameter value which is appearing in Parameter Prompt Window.
  6. Need to Eliminate the Comma’s coming in the Parameter Values and as well as need to Sort the Values too.

Resolution

  1. Modify the command object as shown below:
    • SELECT JOB_NUMBER,CAST(JOB_NUMBER AS INT) as JOB_NUMBER_int, FROM JOB.
  2. Modified the SQL Query to show the INT Casted Value of JOB_NUMBER as JOB_NUMBER_int as well as to show the char value of JOB_NUMBER in the Command Object.
  3. Used JOB_NUMBER_int as a Value Field and JOB_NUMBER as Description Field in the Parameter Window.
  4. Performed the Sorting on Value field in Parameter Window with option as "Descending by value" and selected option for "Prompt with description only" as TRUE.
  5. You can see the parameter values without commas and the values are also sorted.

Keywords

Crystal Reports, CR, List of Values, LOV, sort, Original Order, Commas in Parameter Value , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports XI R2