| Author |
Message |
TkNeo
Newbie
Joined: 12 Jun 2008
Online Status: Offline
Posts: 11
|
   Topic: Can i have a sort control for summary values ?
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
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
|
|
IP Logged |
|
TkNeo
Newbie
Joined: 12 Jun 2008
Online Status: Offline
Posts: 11
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
TkNeo
Newbie
Joined: 12 Jun 2008
Online Status: Offline
Posts: 11
|
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.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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:
{table.stateCode}
else
{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
|
|
IP Logged |
|
|
|
