Problem sorting(possibly due to a group), help please 1

[shef015]

I am having issues with sorting a report using v8.0. I think my problem may be with the group(grouped by account number) which is sorting in original order. I want it to sort depending on the parameter chosen by the user(possible choices are date, employee number, or none). my report looks like this..

Account Number Date Employee Number
_______________________________________________________
Address1
Address2
...

As of right now it just sorts it in the original order, ignoring my parameters. I have formulas setup to figure out how to sort, here is an example...
if {?SortSelect} = 'Employee' then
{Employee}
this formula is then used by the sort

any help would be appreciated, thanks

 

[FoxG]

Hi,

You need to create a formula which will return the required value depending on the parameter value specified.

For example :

Create Formula F1...
If {?param} = 1 then {str_field}
else if {?param} = 2 then totext({num_field},0)
else if {?param} = 3 then totext({dt_field,'yyyy-mm-dd'}
else "invalid param"

Note date format must be yyyy-mm-dd (or similar) for the sort to be able to work correctly with dates.

Then simply sort/group on this formula

By creating three formulas you can organise the report in any required order by simply grouping on f1, f2 f3, and letting the user customise the look of their reports!


Geoff

PS. This should gain you a few brownie points!

 

[shef015]

thanks for the ideas, but it still doesn't work. how it is setup, the report is already group by a client's account number. then within the header there is their number, name, start date, and employee responsible. within the data section there is their billing address, which can have multiple locations(hence the grouping). The user enters in the dates to search by and also enters from 4 sorting options(date, employee responsible, name, or none). All but date are in varchar2 in the database. If none is selected, then it will sort by account number. here is the formula i have set up..

if {?SortingSelect} = 'Client Name' then
{CLIENTVIEW.CLIENT_NAME}
else if {?SortingSelect} = 'Employee Responsible' then
{CLIENTVIEW.RESPONSIBLE_EMPLOYEE}
else if {?SortingSelect} = 'Start Date' then
toText({CLIENTVIEW.STARTED_DATESTAMP},'yyyy-mm-dd')
else if {?SortingSelect} = 'None' then
{CLIENTVIEW.ACCOUNTNUMBER}

this is then read by the sort to figure out what to sort off of. the problem is that it doesn't sort. i think mainly do the groups sorting in original ordered.

I have done this way of sorting before in another report that required no grouping and it worked perfecting fine, so thats why i think it may be my account number group. any help would be appreciated. Thanks to Geoff for the help he gave me.

 

[FoxG]

Hi,

Have you tried adding the formula into the detail section to check what value is being given back.

This will at least indicate whether you formula is working correctly based on the parameters supplied.

Geoff

 

[shef015]

all the records being collected in the detail section are the correct ones. I have been checking it with an MS Access query. The parameter is selecting the correct option i pick also. Is that what you meant by checking the values.

 

[FoxG]

Hi,

When you insert the formula into the crystal report detail section as a text field do the values change for each parameter option when you re-run the report.

IE. The value displayed in the text box should correspond to the parameter being used for each record.

Geoff

 

[kenhamady]

What are the group and sort fields for this report? Is your group set to original order? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[shef015]

i had them grouped by the clients account number. i sort them in the original order. inside the group header i have their number, name, start date, and employee responsible. within the data section there is their billing address, which can have multiple locations(hence the grouping). the value for the parameter only is changed when the report is refresh using new parameter values.

 

[kenhamady]

Post the complete formula that is supposed to determine the group field, and give it's name. I will guess that you should be grouping on this formula field, in ascending order. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[shef015]

well i figured out the people i am making the report for don't need the different sorting option. I tried the grouping on a formula and that idea sorta worked. Thank you for your help though

 

[wusinik]

Can you sort by formula results? If so, how?

 

[kenhamady]

As long as the formula doesn't use a summary operation or a "WhilePrinting" variable you can add it to the sort just like any other field. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/