Print Page | Close Window

Group Sorting based on formula/parameter - how?

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=15671
Printed Date: 05 May 2024 at 9:09am


Topic: Group Sorting based on formula/parameter - how?
Posted By: BadBoyHouse
Subject: Group Sorting based on formula/parameter - how?
Date Posted: 20 Feb 2012 at 4:40am

I have a report which shows a list of clients, along with outstanding debt.

There are columns for client name, current debt, 31-60 days debt, 61-90 days and so on.

The report uses one Group so as to allow total/sum formulas.

I need to add some Parameters so that when the report is generated it offers the user a choice of sorting options. Namely, sort by 31-60 days debt, 61-90 days and so on.

I can manually specify the group sorting via the Group Sort Expert, and this works fine.

I can't however figure out how specify group sorting via a Parameter or a Formula.

Can anyone offer any suggestions??


Replies:
Posted By: rkrowland
Date Posted: 20 Feb 2012 at 5:57am
Create a parameter called Sorting.
 
Assign static values to the parameter for the sorting options you wish to give the user. eg;
31-60 days debt
61-90 days debt
 
Then create a formula as follows;
 
@Sort:
if {?Sorting} = "31-60 days debt" then sum({table.3160field},{table.groupfield}) else




if {?Sorting} = "61-90 days debt" then sum({table.61-90field},{table.groupfield}) else


if 


etc....
 
Now apply your group sorting on the mailto:%7b@Sort - {@Sort } formula.
 
Regards,
Ryan.

Posted By: BadBoyHouse
Date Posted: 21 Feb 2012 at 12:42am
I gave that a shot however within the Group Sorting Expert, the @Sort formula does not appear in the list. Even with the @Sort formula added to the report layout.

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 12:51am
It won't show as one of the tabs.
 
Select which ever group it is you want to be sorted (ie the same group as {table.groupfield} in the formula above).
 
For this group sort: All
Based on: mailto:%7b@Sort - {@Sort }
 
Let me know if you already tried that and I'll put something together on my machine to investigate.
 
Regards,
Ryan.

Posted By: BadBoyHouse
Date Posted: 21 Feb 2012 at 12:54am
It's actually the Based on: list I was referring to.  The @Sort formula doesn't appear in that list.

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 1:04am
Ok, I think it's due to us performing the sum in the formula.
 
Instead, change your formula as follows;
 


if {?Sorting} = "31-60 days debt" then {table.3160field} else




if {?Sorting} = "61-90 days debt" then {table.61-90field} else


if 


etc....
 
(referencing the individual record value rather than the summed amount.
 
Then select which ever group it is you want to be sorted in the group sort expert.
 
For this group sort: All
Based on: Sum of mailto:%7b@Sort - {@Sort }
 
That worked on the example I created here, give it a try and let me know.
 
Regards,
Ryan.

 

Posted By: BadBoyHouse
Date Posted: 21 Feb 2012 at 1:10am

the @sort formula still doesn't show up in the list. there must be some other element of the report design that's causing it. 

no stress though I will probably have to move on and go for the multiple reports option for now.
 
thanks for taking a look!

Posted By: BadBoyHouse
Date Posted: 21 Feb 2012 at 1:13am
Scratch that.  I've sorted it.
 
I had to add the @sort formula to the details section, then add a summary of it to the Group Header.
 
Seems to be working now.
 
Thanks again!

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 1:19am
Oh yeah, I actually did that on my machine without thinking to tell you to do it! haha
 
Glad it's working, you can suppress the detail field and the summary without worry of it breaking the sort parameters if you don't want them to be displayed.
 
Regards,
Ryan.

Posted By: BadBoyHouse
Date Posted: 21 Feb 2012 at 1:23am
cheers
 
I thought I knew pretty much everything about Crystal. I guess you learn something new every day!
 
Do you know if you can have dynamic sorting - i.e. double click on a column heading to do the sorting?

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 1:26am
I'm not sure I follow, let's say for example your user chose 31-60 days as their sorting option - your sort column should match entirely to the 31-60 days column. Other columns won't match as we're not performing any kind of sorting on them.
 
Could you post an example of what it's doing at the minute and what you'd like it to do instead?

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 1:28am
I do remember some sort of dynamic sorting option from a while a go - but I don't remember how to do it haha. I'll have a snoop around and see if I can find it.

Posted By: rkrowland
Date Posted: 21 Feb 2012 at 1:37am
I found the following, I can't really help you any further than this as I've never used the option and for some reason not known to me my Crystal reports doesn't even have the sort control option under Insert.
 
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/60f4cde6-622e-2e10-559e-e09a04be13ff&overridelayout=true - http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/60f4cde6-622e-2e10-559e-e09a04be13ff&overridelayout=true
 
Hopefully that helps a little.
 
Regards,
Ryan.


Print Page | Close Window