How to remove comma separator in number parameter?

[samratmuddasani]

Hi Experts,

I'm using CR XIR2. I have a Number data type column. I'm creating parameter based on that. Now when I pointed that column to the parameter I'm getting thousand separator in the number while entering the prompt. How to remove the separator?

Thanks,

Samrat Muddasani

[abhilash_kumar · Accepted solution]

Hi Samrat,

The only workaround is to convert the prompt to string and convert it back to number if you're refering to it in the record selection formula.

-Abhilash

[samratmuddasani · Accepted solution]

Hi Abhilash, Thanks for the response. How can I do that?? can you give an example??  and yes I'm referring to record selection formula

[abhilash_kumar · Accepted solution]

What kind of a parameter is it - static or dynamic?

With a dynamic prompt you'd need to use a command object (Don't join this to the existing tables) that uses a datatype conversion function. Something like:

select cast(number_column as varchar(10)) from table

You can then point the dynamic prompt to this field in the command object and use a record selection formula as"

totext({original_number_field}) = {?Prompt}

P.S: To improve performance, you can also use a SQL Expression Field to convert the number field to text so that you can use it in the selection formula.

-Abhilash

[samratmuddasani · Accepted solution]

The prompt is a static prompt. what can be done now??    

[abhilash_kumar · Accepted solution]

Here's what you need to do:

1) Change the prompt type to Static

2) Change the record selection formula to:

totext({original_number_field}) = {?String_Prompt}

Like I said above, CR does not pass datatype conversions back to the database and hence you won't see this filter as part of the where clause (if you're reporting directly against tables), thereby slowing down the report.

You would be better off creating a SQL Expression Field that uses database specific functions to convert the column to string and use this SQL Expression Field in the record selection formula.

-Abhilash

[samratmuddasani · Accepted solution]

Abhilash,

I'm getting error when I'm trying to create the SQL expression. Database vendor code: 936(missing expression). How to correct this??    

Thanks,

Samrat

[samratmuddasani · Accepted solution]

And when I'm using
totext({original_number_field}) = {?String_Prompt}

then its displaying a blank report.

[abhilash_kumar · Accepted solution]

What database are you reporting against and what is the SQl Expression you've used?

-Abhilash

[samratmuddasani · Accepted solution]

Both the databases are same.

[abhilash_kumar · Accepted solution]

What do you mean by that?

I actually was looking for the Database Server you're reporting against - Oracle, SQL Server, etc?

And, if you could paste the SQL Expression Code you've used here please?

-Abhilash

[samratmuddasani · Accepted solution]

I'm using Oracle 10g database and the SQL Expression is as below.

select cast({"XFDP_WORK_VW"."WRK_RQST_NBR"} as varchar(10)) from XFDP_WORK_VW

Thanks,

Samrat

[DellSC · Accepted solution]

Try changing it to this:

To_Char({"XFDP_WORK_VW"."WRK_RQST_NBR"})

You don't need the "select" part as this will automatically come through as part of the query that Crystal builds.

-Dell

[abhilash_kumar · Accepted solution]

You don't need the 'select' part as Dell suggests.

cast("XFDP_WORK_VW"."WRK_RQST_NBR" as varchar(10))

should work, or Dell's function should work with a small modification too:

To_Char("XFDP_WORK_VW"."WRK_RQST_NBR")

You cannot use the curly braces CR puts around fields in a SQL Expression.

-Abhilash

[samratmuddasani · Accepted solution]

Hi Abhilash,

Thanks for your response. You are always been a star for me.

cast("XFDP_WORK_VW"."WRK_RQST_NBR" as varchar(10)) has worked.

Dell,

Thanks a ton for your response too.

To_Char("XFDP_WORK_VW"."WRK_RQST_NBR") has not given the output which I was looking for.

Thanks a lot to all again,

Samrat Muddasani

[former_member205840]

Hi Samrat,

I think you are creating a static number type parameter so, while accepting input it is showing the number with thousand separator.

You can change the data type of your parameter to string and while comparing you have to convert to number and can compare this will solve the issue.

or

You write a SQL(Add Command)  and convert number filed to string, this will solve the issue.

Sastry

[samratmuddasani]

Sastry,

Yes the parameter is static number. When I change the parameter to static string then its asking for Number while I'm trying to save in record selection. Can you tell me what can be done??    

Thanks,

Samrat Muddasani