String field sorting 1

[jraheja]

I have a string field which has numeric data. The problem is I display this as a parameter and it displays as 1,11,2,21 etc. since it is a string. I want to display as 1,2,3,4,11,21... If I change parameter to numeric, then in my formulas it expects a numeric.. Don't ask me why this field was defined as string in the first place! This is Crystal reports and enterprise v10

 

[lupins46]

Create a formula field:
@mySort:
val({mytable.mystringfieldname})

and then sort the report on that field.

 

[jraheja]

I did not understand the above. What is it doing? Can I define this and use as a parameter?

 

[lupins46]

Your question title is about Sorting.
To get round the problem of incorrect sort sequence you create a formula field which is numeric and sort on that instead.

The formula field I have shown just makes sure the numbers are treated as numbers and not as text, which was your problem. There are no parameter fields involved.

 

[jraheja]

Sorry. Maybe I need to give more details. I want to sort the parameter value display list which is a string field in the database... Not on the report. In the report I compare against this parameter accepted. Appreciate your prompt reply.

 

[lupins46]

OK, I understand.
You can't force the displayed sequence to follow number sorting rules if it is a text field.

You can type the values into the parameter default values or you can load the list from a table and then adjust the sequence using Up and Down arrows. I don't imagine you find either of these attractive.

 

[jraheja]

Thanks! I know these options are not attractive so I posted this question. I I thought others too would have faced it.
I was doing the above right now. I created a Crystal report,applied the val formula you suggested exported to Excel. Now I will import this list and check it out...Hope it doesn't resort them after that since I will have to define it as a string parameter..

 

[jraheja]

It resorts them! Is there any other solution????

 

[Turkbear]

Hi,
What database?

Yo should be able to use an

Order by To_Number(textfield) ( exact function varies)

when retrieving the parameter values..

 

[Turkbear]

Hi,
Sorry, I failed to note that you were obtaining these using the Default Values part of Crystal Reports..That does not give you a flexible enough sort option....( it also is static, so I never use it unless I know the values will not change for a long while - in which case I order them the way I want, manually).

Perhaps, if you can, you can create a view in the database for lookup purposes that would pre-sort the values you need and use that view to load the default values.

 

[jraheja]

How would I do that?? It is a SQL Server database field. At parameter definition, I don't see that

 

[jraheja]

Even if you define manually or import values, it goes and resorts them based on the data type. The values are not going to change. They are the numbers 1-99. But because it is a string field, it goes and resorts them - I tried numerical ascending sort too!

 

[Turkbear]

Hi,
If you have a DBA ( or are one) create a database view ( I think SqlServer has those, we are an Oracle shop) called, say,
ParamName_Values ( use your param name to make it easier to know which view you want) with something like:
create view ParamName_Values as select to_number(textfield) pvalue from source_table order by pvalue;

In CR, use the Default Values Edit section to select that view and that field..Should be sortable ( if not already ).

Perhaps one of the SqlServer folks in this group can give more specific info.

 

[jraheja]

I can have that done by the dba. That won't be a problem. But when I open the report using a URL, the list is resorted! Will the view list not get resorted too?

 

[Turkbear]

Hi,
If you save them
(Using the 'Browse Table' ( which can be the view) and
'Browse Field' to populate the left hand side , then use the control to move them into the right panel)..

They should stay that way when the report is opened, since, as I said, they are 'static' saved values..

 

[jraheja]

What is the difference if I type them into the Right Hand side v/s using a view. Is it treated different? I am asking since my dba will take a day or two to do it!

 

[jraheja]

Hi Turkbear,

Your 'static' hint helped. By default, it was set to taking from the table. Even when I had cleared the field name, it was not accepting whatever I entered. Once I set the 'Browse Table' and 'Browse field' to blank and then manually entered the fields and kept 'No sort' as the option, it did it. I was trying numerical ascending/values.

So in solution, keep no sort and no table, no field and enter yor list or import it.

Thanks!