Print Page | Close Window

Can i have a sort control for summary values ?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7138
Printed Date: 02 May 2024 at 4:07pm


Topic: Can i have a sort control for summary values ?
Posted By: TkNeo
Subject: Can i have a sort control for summary values ?
Date Posted: 23 Jul 2009 at 12:33pm
I have a report that shows orders. It's grouped by "State" and has a summary of the quantity. I want the report to be either sorted alphabetically by "State" or by the "Summary Quantity" and the users should be able to decide this at run time. The sort control only sorts the data within the group and not the groups itself. If i open the group sort expert and set it to sort by Quantity summary then i can never sort it alphabetically...

Does anyone know how to solve this ?


Thanks in advance.

TK


Replies:
Posted By: lockwelle
Date Posted: 24 Jul 2009 at 6:52am
if you summaries are simple aggregrates like: sum({table.field}, {table.state}) then I have a solution.
 
First create a formula like:
if {?SortParameter} = "State" then
 {table.state}
else
 sum({table.field}, {table.state})
 
 
now in the group, change the group to use the formula you just wrote.
 
all done.
 
HTH

Posted By: lockwelle
Date Posted: 24 Jul 2009 at 6:54am
thinking about it, Crystal will probably complain that 'a string is required here' so you would want on the second line to change it to:
totext(sum..., 0,"")
 
you might want to add the state to the string, but I would try it without to start.

Posted By: DBlank
Date Posted: 24 Jul 2009 at 7:30am
lockwelle, will that work? Aren't you actually turning the report into a giant loop if you choose to sort by the Sum option? It is going to try and get a number from a grouping that does not exist...And it is changing the grouping from State to Number rather than leaving it as State and changing the sort order.?

Posted By: lockwelle
Date Posted: 27 Jul 2009 at 6:21am
In all honesty, I don't know...It is what I would try first.
 
I guess you could try changing the sort...I haven't much luck with sorts...probably just me.
 
This is how I dynamically group, since my groups are usually not related.
 
It is not trying to get a number from a grouping that doesn't exists...I see your point, it is not predetermined...
 
Hmmm....  Again it is what I would have tried first.  If it didn't work/took too long to complete, I would look for another alternative...though in all fairness...I would have just added a column onto the result set that gave the total so that Crystal didn't need to calculate it...the laziness/ease of stored procedures.
 
Thanks for pointing that out DBlank.

Posted By: TkNeo
Date Posted: 30 Jul 2009 at 7:30am
Guys,

Thanks for your replies. As you would have guessed it does not work and the reason is also what we feared. Cant sort by something that does not exist at that point.

I dont believe these are bizarre requirements to ask for..lot of people should be doing similar stuff..

HELP ! PLEASE !

Thanks

Posted By: DBlank
Date Posted: 30 Jul 2009 at 7:40am
Easiest solution (coming to mind today at least) for this one is to create 2 sub reports with your different sorting and then conditioanlly suppress them based on the select parameter.
This may screw up drill downs and prevents the use of other sub reports but it is a viable solution.

Posted By: TkNeo
Date Posted: 30 Jul 2009 at 8:03am
unfortunately i gave you guys a condensed version of what i have in my reports.. there are 5-10 columns that are possible contenders for sorting..So whatever solution i go w/ has to be scalable.



Posted By: lockwelle
Date Posted: 30 Jul 2009 at 9:52am

Most robust solution then is to get the data via stored proc.  In the stored proc, you can add columns that represent the different columns and their values to sort on.

For the state, this is just the name, but for the amounts, you can sum the amounts for a state and place that in a column.  So on and so forth for the rest of the columns(not all may need to be done).
 
Now the fun part.  Have a parameter that the user can set to sort the report. write a forumula like:
if mailto:%7b@sortParam - {@sortParam }="state" then
 {table.stateCode}
else
 if mailto:%7b@sortParam - {@sortParam }="sum" then
  {table.stateSum}
...
 
then create a group (or change an existing one) to group on the formula...
all done.
 
dynamic grouping is the useful thing I have learned, cuts down the number of reports to be created when the data is all the same, just the 'look'/summing is different.
 
HTH


Print Page | Close Window