| Author |
Message |
peter
Groupie
Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
|
   Topic: ???Sort Multiple Field in CR 10 ???
Posted: 18 Apr 2007 at 3:56pm |
I'm using CR 10 Designer to do a simple thing but CR seems so relectant !!! or I'm just so dump.
When users run the report, a prompt/parameter will let user to select one of 2 sort options:
Option 1) Sort by Date then Customer ID
Option 2) Sort by Customer ID then Date
I tried to create 2 Sort Options as Formularsas follows:
Formular1: Sort1
if {?SortOption} = 'Option 1' then TableName.Date
else TableName.CustomerID // CR gave me a error right here. I want a date field
Formular1: Sort2
if {?SortOption} = 'Option 2' then TableName.CustomerID
else TableName.Date
then using the Report Sort Expert to select in formular into the sorting box.
I'm very thankful for any help.
In fact I need to group the report
by:
Date then CustomerId
or by:
CustomerID then Date
|
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 18 Apr 2007 at 4:04pm |
|
You have the right idea, but If Then statements have to return the same datatype. You are trying to return either a date or a string. No can do. Use the CDate() function to convert the date to a string so that they are compatible.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
peter
Groupie
Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 18 Apr 2007 at 4:37pm |
Thank you, Brian.
You've saved me a lot of time.
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 18 Apr 2007 at 4:50pm |
|
Cool.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 18 Apr 2007 at 5:07pm |
CDate returns a date value and not a string - it converts things into dates. ToText converts a date to a string.
You have to be careful when sorting by dates that have been converted to strings. If your dates span more than one month or year, you'll get an odd order. For example, the following dates converted to strings will come out in the following order:
01/01/2005
01/01/2006
01/10/2005
02/01/2005
02/10/2006
03/03/2004
etc.
So, the syntax you'll need looks like this:
ToText({Table.Date}, 'yyyyMMdd')
(NOTE: The format string is CaSe seNsitive!)
With this formula, you'll get the following sort from the above example:
20040303 (03/03/2004)
20050101 (01/01/2005)
20050110 (01/10/2005)
20050201 (02/01/2005) 20060101 (01/01/2006)
20060210 (02/10/2006)
-Dell
|
|
|
|
IP Logged |
|
peter
Groupie
Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 19 Apr 2007 at 10:02am |
Thank you so much hilfy!!!
That is what I'm trying to resolve. Now I run into another issue:
I need to Group and Sort them in two options:
Option 1 - Date Sold then CustomerID
Option 2 - CustomerID then Date Sold
The reports has two grouping columns:
Date Sold Customer ID
------------ ---------------
It seems you can help me on this one!!! Hope I can get the solution from you soon!!!!
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 19 Apr 2007 at 10:12am |
Grouping includes sorting. So, you're going to set up two groups - on on your {@Sort1} formula and the second on your {@Sort2} formula.
-Dell
|
|
|
|
IP Logged |
|
peter
Groupie
Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 19 Apr 2007 at 10:20am |
I did that but the {@Sort1} and {@Sort2} are always the same either Date Sold if Option1 selected, or CustomerID if Option 2 selected. I'm trying to change {@Sort2} := CusomerId if Option1 selected and {@Sort21} := DateSold if option2 selected but CR did not let me do the assign statement. You must be expert on CR. Please help, hilfy.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 19 Apr 2007 at 10:27am |
I suspect your formulas are exactly the same - they should sort of be the reverse of each other.
if {?SortOption} = 'Option 1' then ToText({TableName.Date}, 'yyyyMMdd')
else {TableName.CustomerID}
if {?SortOption} = 'Option 1' then {TableName.CustomerID}
else ToText({TableName.Date}, 'yyyyMMdd')
NOTE that I'm loking at 'Option 1' in both formulas. {@Sort1} will give you the first field to sort on and {@Sort2} will give you the second field to sort on.
-Dell
|
|
|
|
IP Logged |
|
peter
Groupie
Joined: 08 Apr 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 19 Apr 2007 at 11:23am |
You're really expert!!! I'm a little confused about checking the same option1 on both formular. It works. But now the fields on the report columns now swapped that I dont want and I need to format the dates to MONTH DD, YYYY that is getting complicated because I dont now a head of time of which field will be the date. Is there a format 'MONTH DD, YYYY ' for TOTEXT function ?
|
|
IP Logged |
|
|
|
