Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Sorting by multiple parameters Post Reply Post New Topic
Author Message
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Topic: Sorting by multiple parameters
    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.


Edited by colman1212 - 19 Oct 2010 at 11:31pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 20 Oct 2010 at 3:26am
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet 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.


Edited by colman1212 - 20 Oct 2010 at 3:58am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 20 Oct 2010 at 4:23am
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet 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.
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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}
 
 
 
IP IP Logged
colman1212
Newbie
Newbie


Joined: 08 Jul 2010
Location: Ireland
Online Status: Offline
Posts: 36
Quote colman1212 Replybullet Posted: 20 Oct 2010 at 6:06am
Great thanks a million for all the help, That has done the trick.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.