Crystal Reports Sorting using a parameter

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Sorting using a parameter

<< Prev Page of 2

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Dec 2009 at 9:04am
	Interesting...but is that possible lockwelle? Group1 is contingent on group 2 which is contingent on group1 so it becomes an loop that cannot support itself, correct?
	IP Logged


lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 02 Dec 2009 at 9:23am
	Now that you mention it... I don't usually group by aggregates, and I would modify the stored proc to give me those numbers if I needed to, but with that said... The sum by customer won't work outside of the customer group will it...BUT you can use a formula to set the order in a group, so what I would try instead is to use the formula in the Sorting of the group, that should work... which might imply that the first method would work. I would try them both, since it wouldn't take too long add a group, delete the group and modify the sort
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 02 Dec 2009 at 9:26am
	though, I just looked at the sort using a formula and I not so sure any more.
	IP Logged

theotherdodge Groupie Joined: 17 Nov 2008 Online Status: Offline Posts: 40	Posted: 02 Dec 2009 at 11:02am
	I have tried that and still can't find that field in the sort...
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Dec 2009 at 3:00pm
	How far did you get in this process... *Originally posted by DBlank* This one is tricky but here is the process. Do each step seperate and test it (run your report) so you see what is happening. Create a parameter ... (1 for sort by Revenue $ or 2 by Revenue%) Now create a formula field. This part is where you will have to figure out what values to put in later. For testing/learning though just use it to flip between two easy report values... Call the formula "Sort"... if @param = 1 then {table.revenueField} else {table.CostField} Place this on your report details (you need to unsupress these for now) Now use the insert summary function (Sigma Sign) and select the @Sort formula field as a SUM with a summary location on Group footer 1 (customer name). Run your report with each param value. See how it changes your numbers and your sum. Suppress youre details again if you do not need them anymore. Now go into the group sort expert Select for this Group sort=Top N based on = SUM of @Sort Where N is = 1000 ( or any number larger than your total number of customers in the DB) hit OK. Now instead of sorting the customers (groups)by alpha customer name it is sorting by the Sum of thje Sort formula for each customer. Now the trick is to figure out which value to use the "Sort" formula. It cannot be the other values I gave you in the other post as these use multiple Summary function in them. Test this out and see what you can come up with.
	IP Logged

theotherdodge Groupie Joined: 17 Nov 2008 Online Status: Offline Posts: 40	Posted: 02 Dec 2009 at 6:03pm
	DBlank: Because the Revenu$ and Revenue% were but summary fields, I am not allowed to select them to sort...They were not on the available fields to sort...
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 02 Dec 2009 at 7:13pm
	I understand. That is why I gave you a new process (above) to sort group by top N. All that is missing is the correct formula replacement in it. If you can get it to work then I think we can figure out the right formula to use for the "Sort" formula I mention in it. However I want to make sure you can get the rest of it to work as well. Please try that and then we can try and repalce the formula.
	IP Logged

theotherdodge Groupie Joined: 17 Nov 2008 Online Status: Offline Posts: 40	Posted: 03 Dec 2009 at 6:20am
	DBlank: That worked great!!! Thanks!
	IP Logged

<< Prev Page of 2

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