multiple sort levels using parameter fields

[schaeff]

Hello, I am using CRXI and have a report that contains information on services. There are a few fields which the users would like control over for sorting such as a service category code, location of service, service date and person name. I have been able to create a primary sort of the records using a formula field with a parameter for the input to the formula. I put the formula field in my report header, then selected it as the first sort option in the Record Sort Expert. The users would like a secondary sort (and perhaps a third level sort) that they can also control. The formula I have for my primary sort is:

If {?SortField} = "L" then
{@service location code}
Else
If {?SortField} = "C" then
{Service.category}
Else
If{?SortField} = "D" then
{@service date }
Else
If {?SortField} = "N" then
{person.lastName}

I tried to create a similar formula, calling it SecondarySort, and associated it with a separate parameter, once again adding the formula to the report header and then choosing the formula as the next sort level in the Record Sort Expert. However, the report results appear to ignore this secondary sort input. Is there a way to accomplish more than one level of sorting using input parameters from the user?

 

[lbass]

Your method should work--as long as you realize that the secondary sort will occur within the set of records returned from the first sort. And of course groups have priority over sorts. What do you see listed on the right if you go into the sort records area?

-LB

 

[schaeff]

I am not sure I understand your question. Are you asking what I see when I open the "Record Sort Expert"? If so, then on the left side of the window is my available fields, on the right side is a list of my sort fields. The top, or first field listed, is my primary sort formula, the second field listed is my secondary sort formula. I have no groups in this report. Other than these two fields there is nothing else in the "Sort Fields" side of the window.

If this doesn't answer your question, then I need a little help understanding what you'd like me to look at. Thanks

 

[lbass]

Yes, that was what I was asking. In what way is the report sorting incorrectly? Please show a sample of what you are seeing, and then show how you would expect the report to look, and identify what parameter options were chosen.

-LB

 

[schaeff]

If I sort by Category as my primary sort, then choose Name as my secondary sort I get a result like this:
Category Date Loc Code Last Name
7 10/1/2009 10 Littleton
7 11/1/2009 5 Alexander
7 11/5/2009 21 Smith
7 09/4/2009 26 Douglas
9 11/8/2009 5 Williams
9 10/15/2009 11 Carey

I would expect it to look like:
7 11/1/2009 5 Alexander
7 09/4/2009 26 Douglas
7 10/1/2009 10 Littleton
7 11/5/2009 21 Smith
9 10/15/2009 11 Carey
9 11/8/2009 5 Williams

 

[lbass]

I agree it should sort like that. It looks like it is actually sorting based on location code (if it is a string). What does your secondary sort formula look like?

-LB

 

[schaeff]

As I was adding my secondary sort formula to this post I spotted an error I had overlooked. I went back and corrected it and now the sort is working fine. I just couldn't see the problem yesterday and started questioning if I was trying to get CR to do something it wasn't designed to do. Thank you for your time, I do appreciate your willingness to assist. It made me take a closer look and then the issue because clear.