How to implement a parameter to sort date or alphabetic order 3

[javedi]

Hello

Does anyone know if and how it would be possible to set a parameter option in Crystal reports 10 to allow a user to specify whether the report should sort in date or alphabetical order?

Any idea's welcomed.

javedi

 

[Madawc]

The standard solution is to define a formula field. Use a parameter to choose either a date field or an alpha. Define the field using Reports > Sort Expert.

You may need to use ToText to define the date as text, something like ToText({your.date}, "yyyy/MM/dd")


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[javedi]

Hi

Thanks for the prompt response.

I have two fields, one which is a date field and the other is a street description.

The user wants to be able to sort the report depending on what he is doing. Either searching by date or by street order.

How will the report prompt the user to choose which field he wishes to sort on?

 

[IanWaterman]

Create a string Parameter with two default settings, with prompt

Select Sort Order

Defaults
Date
Street

The create formula
@sort

If {?Param} = 'Date' then {dateField} else {StreetField}

Place this formula in your sort expert.

Ian

 

[javedi]

Thanks Ian,

I don't know how to set the two default settings with prompts. Can you explain further?

javedi

 

[IanWaterman]

In your create parameter screen, you just add your prompting text and select parameter type.

Underneath that is a box to allow you to enter default values of paramter.

Ian

 

[lbass]

The formula should be:

If {?Param} = 'Date' then
totext({table.date},"yyyyMMdd") else
{table.street}

Add this as your record sort field.

-LB

 

[javedi]

Thanks lbass

The parameter is appearing blank. Should it allow the user to choose date or street?

I've created a new parameter, called 'sort', the value type is String, in there i have entered two values manually; Street and Date. Have i done this correctly?

thanks for ongoing help guys.

javedi

 

[lbass]

What do you mean by it being blank? Did you add the parameter directly to the report? Where is it when you say it is blank?

-LB

 

[lbass]

Did you add the Street and Date in the values column of the parameter screen?

-LB

 

[javedi]

Hi lbass

The parameter is blank when refreshing the report, the options date and street do not appear in the drop down box.

I have added <Street> and <Date> into the default values.

I have chosen the street field for the Browse table: and browse field: options. Is this correct?

I have not copied over the values from the street field in the default values.

javedi

 

[javedi]

Thanks Guys,

It works well!

The decription on the default values was missing hence the parameter values did not appear. Many thanks for helping me out with this.

javedi

 

[lbass]

You should be adding the text "Street" and "Date" (no quotes) as your value fields in the parameter setup screen which for this example we will say you called "Sort". You shouldn't be using the browse feature at all. Then you must add your formula:

If {?Sort} = 'Date' then
totext({table.date},"yyyyMMdd") else
{table.street}

...to field to report->sort records. Then when you refresh, you should see "Date" and "Street" in the parameter selection screen as options for your parameter {?Sort}.

-LB

 

[NervousRex]

I'm trying to do this also, except with a numeric field field that has negatives.

When I do:

IF {?SortField} = "Total_Score" Then
ToText({@Total_Score}, "#######.##")
ELSE
{usp_Report_Client_Goals;1.Full_Name}


The sort order comes out:

0.08
-0.12
0.17

I would expect:
-0.12
0.08
0.17


Thanks for any help

 

[javedi]

Hi

I'm not sure but try changing ToText to ToNum. It could be that the report assumes it is alpha.

Hope this is useful.

Javedi

 

[lbass]

Create two separate formulas like this:

//{@SortScore}:
IF {?SortField} = "Total_Score" Then
{@Total_Score}

//{@SortName}:
IF {?SortField} = "Name" Then
{usp_Report_Client_Goals;1.Full_Name}

Add each formula to the sort records area. If the criterion is not met, it will have no impact on the sort.

-LB