Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Group Sorting based on formula/parameter - how? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet Topic: Group Sorting based on formula/parameter - how?
    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??
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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 {@Sort} formula.
 
Regards,
Ryan.


Edited by rkrowland - 20 Feb 2012 at 5:59am
IP IP Logged
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet 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.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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: {@Sort}
 
Let me know if you already tried that and I'll put something together on my machine to investigate.
 
Regards,
Ryan.
IP IP Logged
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet 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.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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 {@Sort}
 
That worked on the example I created here, give it a try and let me know.
 
Regards,
Ryan.

 
IP IP Logged
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet 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!
IP IP Logged
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet 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!
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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.
IP IP Logged
BadBoyHouse
Newbie
Newbie


Joined: 25 May 2010
Online Status: Offline
Posts: 20
Quote BadBoyHouse Replybullet 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?


Edited by BadBoyHouse - 21 Feb 2012 at 1:25am
IP IP Logged
Page  of 2 Next >>
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.