Print Page | Close Window

Sorting by multiple parameters

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11467
Printed Date: 04 May 2024 at 8:44am


Topic: Sorting by multiple parameters
Posted By: colman1212
Subject: Sorting by multiple parameters
Date Posted: 19 Oct 2010 at 11:30pm

My problem is that I cannot sort bu multiple fields.

 
So for example I have a parameter called SortField
The user can add multiple fields here to sort on.
For example they add NAME, ADDRESS, DETAILS to sort on.
 
I then have a fomula called SortFormula set up as below:
 
if {?SortField}='NAME' then {CUSTMER_HDW.NAME}
else if {?SortField}='ADDRESS' then {SPECFAC_HDW.ADDRESS}
else if {?SortField}='DETAILS' then {SPECFAC_HDW.DETAILS}
 
Then I sort by Sortformula.
My problem is that it will only ever sort on one of the fields regardless if I add 1 ,2 or 3 fields.


Replies:
Posted By: DBlank
Date Posted: 20 Oct 2010 at 3:25am
Originally posted by colman1212

My problem is that it will only ever sort on one of the fields regardless if I add 1 ,2 or 3 fields.
Not sure what you mean by this.
Are you intending that the user select a primary, secondary and tertiary sort order?
If so you would need 3 paramaters to define each sort level.

Posted By: colman1212
Date Posted: 20 Oct 2010 at 3:58am
Yes well crystal reports allows you to add multiple values when you select a parameter. I was hoping that they could add Name, Address, Details and that the report would then
Sort by Name, Address, Details Asc.
 
I'm guessing this is not the case?
 
So if you are creating a report and you want the user to be able to decide how the report is sorted, How do you do this? Assume they want to sort on more than one field.

Posted By: DBlank
Date Posted: 20 Oct 2010 at 4:22am

I see. YOu were letting them add multiple values to one param.

Well your formual does not handle the multiple values and the sort expert would be expecting multiple sort criteria.
Maybe you could do it with this but I do not know if crystal would properly enforce the order in which the values were added to the param in the array. Some intial testing seems that it does hold them in the oprder they are added but I am not certain of this.
Perhaps you can handle it with 3 formulas that use the values in the array to create the sorting values that can be added to the sort expert but I am no good with array formulas so not much help with that part.

Posted By: colman1212
Date Posted: 20 Oct 2010 at 4:31am
Ok I think I understand.
So at the moment, my formula is returning one value and thats why the report is sorting on one field.
So then I need my formula to return multiple values, and arrays is the only way to do this?
 
I thought allowing a user to sort a report on multiple fields would be something quite commonly done... hmmmmm

Posted By: DBlank
Date Posted: 20 Oct 2010 at 5:04am

kind of. Assuming your param is allowing for multiple values then your formula is sequentially checking in the order of the formula not the array to see if that value exists. If it does it returns that value and then stops....

if {?SortField}='NAME' then {CUSTMER_HDW.NAME}
else if {?SortField}='ADDRESS' then {SPECFAC_HDW.ADDRESS}
else if {?SortField}='DETAILS' then {SPECFAC_HDW.DETAILS}
 
...
so if NAME was selected at all in the param array it will sort on name.
YOu can allow a user to sort on mulltiple fields in another way.
Just create 3 params ('primary sort', 'secondary sort' and 'tertiary sort')that do NOT allow for multiple values all with the selecy option of the 3 types.
create 3 formual fields to 'convert' the params just like you did for 1.
in your sort expert add all 3 formula fields in descending order.
Primary isus with this way is that unless youa re running 2008 your users will have to input a value in all 3 params as optional params are not allowed.

Posted By: colman1212
Date Posted: 20 Oct 2010 at 5:20am

Thanks for all your help on this. Much appreciated.

I am running version 10.0 of crystal reports,
not sure what year that is from.
 
Yeah my next question was going to be is it possible to have default values set for the parameters so that you can just click ok when you open the report as opposed to filling in all the parameters?
But from what you are saying above about optional params, its not.
 
 
 

Posted By: DBlank
Date Posted: 20 Oct 2010 at 5:39am
depends on how you are deploying the report. My deployment system ignores defaults but it is a custom interface.
I think v10 allows for you to define the default values for a param.
AN option would be to add a value of 'None' to secondary and tertiary params. In the Options portion of each of those params use 'None' as teh default value.
in your formulas use
if {?SortField}='None' then ''
else if {?SortField}='NAME' then {CUSTMER_HDW.NAME}
else if {?SortField}='ADDRESS' then {SPECFAC_HDW.ADDRESS}
else if {?SortField}='DETAILS' then {SPECFAC_HDW.DETAILS}
 
 
 

Posted By: colman1212
Date Posted: 20 Oct 2010 at 6:06am
Great thanks a million for all the help, That has done the trick.


Print Page | Close Window