Changing group sorting with a parameter field...
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=9917
Printed Date: 06 May 2024 at 3:08am
Topic: Changing group sorting with a parameter field...
Posted By: RaymondC
Subject: Changing group sorting with a parameter field...
Date Posted: 05 May 2010 at 10:59am
|
Hi…new Crystal user here. I have an extremely simple report that sums up sales for customers. The individual invoice amounts are in the detail section (which is suppressed) and the summary info. below is in the group header: Customer Name Sales Company AAA $10,000 Company BBB $15,000 Company CCC $12,000 I created a “pick list” parameter field to prompt the user to select between “sorting alphabetically by customer name” or by “sales amount.” My problem is that I cannot find where to create a “group sorting formula” (the formula workshop does not appear to have a category for sorting formulas). Thanks. |
Replies:
Posted By: DBlank
Date Posted: 05 May 2010 at 11:17am
|
It is Top N sorting using the Group Sort expert.
It is only available for use if the Sales figure is creating the Insert Summary function.
I have never tried to conditionally change TOP N sort into some other sort...not sure about that...
|
Posted By: RaymondC
Date Posted: 05 May 2010 at 11:59am
|
In the Group Sort Expert, I chose "ALL" (not "Top N") and then I selected the summary field that is summing the sales. This results in a report sorted by the Sales amounts.
If, instead, I choose "None" then the report is sorted by the Customer Name since this is the default sorting that resulted from the creation of the Customer Name group.
What I need is a way to choose either the current sorting of "ALL by summary field" or NO SORTING AT ALL (the default).
I have no idea if this is even possible so I may be asking for too much.
 |
Posted By: DBlank
Date Posted: 06 May 2010 at 3:52am
|
The problem is that the sort is dynamic based on sales results and whom is sales person at the point the report is run. Other than doing calcualtions in a stored proc outside crystal and usingt hat as the source the only way to sort on group summaries is using the group sort expert.
You set the sort as TOP N set N= a number greater than your total sales persons count.
The problem is how to use a conditional formula for the 'based on'.
If you can come up with a logic for that you have a solution. I am not seeing anything that works yet.
Another option would be to create 2 sub-reports in the main report. 1 sorted alpha the other sorting using top N on sales totals.
Use your param to show/suppress on e or the other. Sub reports can messup drill downs if you are using any.
|
Posted By: RaymondC
Date Posted: 06 May 2010 at 2:54pm
|
I see what you mean. I will try to use a subreport and see if that works.
Thanks for the assistance.
Ray
|
Posted By: yggdrasil
Date Posted: 07 May 2010 at 3:06am
|
I may have misunderstood you, given the other answers, but I think you are saying you want a parameter that says 'sort on customer' or 'sort on amount' and then the order changes.
If so I would create a formula
if {?sort} = 'Customer' then {Customer}
else {Amount} and then set a group on that formula, outside all your other groups
Then suppress all the sections you don't want
|
Posted By: RaymondC
Date Posted: 08 May 2010 at 7:40am
|
Where I'm at with this...
I decided to create an SQL Server View to sum the sales for customers. This has simplified my report considerably as I now need no groups or summation fields at all within Crystal Reports.
I am still attempting to create a parameter prompt to allow the user to choose between sorting by "customer name" or "total sales."
I created a formula that looks like this:
If {?SortBy} = "Customer" Then {customer.FullName}
else {vw_CustomerSales.TotalSales} My next step would be to use this formula in the sort expert but before I can do that I need to resolve an error in the ridiculously simple formula above! Crystal Reports does not seem to like me using a field from a View in the formula: "There is an error in this formula. A string is required here."
Any thoughts?
P.S. Assuming I can get the paramter promting issued resolved, I have a second issue. If the user selects "Customer" then I want the sort to be ascending but if the user selects "Total Sales" then I want the sort to be descending. Is this possible?
|
Posted By: DBlank
Date Posted: 08 May 2010 at 5:21pm
|
You are getting the error because you are trying to return 2 different field types. Logically based on your criteria this would not happen but crystal still enforces the rule of not mixing a string with a number into the same result field.
If {?SortBy} = "Customer" Then {customer.FullName} else totext({vw_CustomerSales.TotalSales}) will resolve the error but may cause you another one as sorting alpha on a numeric string can get a bit odd unless you pad the left with zeros to make a consistent string length. It also does not resolve your ascending/descending issue.
Here is another approach.
Create two formulas as
SortName
If {?SortBy} = "Customer" Then {customer.FullName}
else 'A' SortSales
If {?SortBy} = "Sales" then {vw_CustomerSales.TotalSales} else 0
Now do a primary sort on @sortname ascending and a secondary sort on @SortSales as descending.
I am not at a machine to test this out but logically I think it will work.
|