Technical Questions: ???Sort Multiple Field in CR 10 ???

Print Page | Close Window

???Sort Multiple Field in CR 10 ???

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=548
Printed Date: 05 May 2024 at 9:35am

Topic: ???Sort Multiple Field in CR 10 ???

Posted By: peter
Subject: ???Sort Multiple Field in CR 10 ???
Date 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

Replies:

Posted By: BrianBischof
Date 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>

Posted By: peter
Date Posted: 18 Apr 2007 at 4:37pm

Thank you, Brian.

You've saved me a lot of time.

Posted By: BrianBischof
Date 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>

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date 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!!!!

Posted By: hilfy
Date Posted: 19 Apr 2007 at 10:12am

Grouping includes sorting. So, you're going to set up two groups - on on your mailto:%7b@Sort1 - {@Sort1 } formula and the second on your mailto:%7b@Sort2 - {@Sort2 } formula.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date Posted: 19 Apr 2007 at 10:20am

I did that but the mailto:%7b@Sort1 - {@Sort1 } and mailto:%7b@Sort2 - {@Sort2 } are always the same either Date Sold if Option1 selected, or CustomerID if Option 2 selected. I'm trying to change mailto:%7b@Sort2 - {@Sort2 } := CusomerId if Option1 selected and mailto:%7b@Sort21 - {@Sort21 } := DateSold if option2 selected but CR did not let me do the assign statement. You must be expert on CR. Please help, hilfy.

Posted By: hilfy
Date 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.

mailto:%7b@Sort1 - {@Sort1 }

if {?SortOption} = 'Option 1' then ToText({TableName.Date}, 'yyyyMMdd')

else {TableName.CustomerID}

mailto:%7b@Sort2 - {@Sort2 }

if {?SortOption} = 'Option 1' then {TableName.CustomerID}

else ToText({TableName.Date}, 'yyyyMMdd')

NOTE that I'm loking at 'Option 1' in both formulas. mailto:%7b@Sort1 - {@Sort1 } will give you the first field to sort on and mailto:%7b@Sort2 - {@Sort2 } will give you the second field to sort on.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date 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 ?

Posted By: hilfy
Date Posted: 19 Apr 2007 at 11:27am

ToText(Datefield, 'MMM dd, yyyy')

You'll have a second, similar set of formulas to display the data on the report vs. sorting the data. The format for sorting the date need to be 'yyyyMMdd' and the format for displaying it needs to be 'MMM dd, yyyy'.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date Posted: 19 Apr 2007 at 11:41am

Thank you a lot for your time and help.

I will try the keep the display of the columns not changed on the report.

Posted By: peter
Date Posted: 19 Apr 2007 at 3:38pm

Hi! Thank you a lot for your help. It works great now.

Well, the issue is on the Server (CE 10).

My report has a drop-down list box prompt/parameter that allows user to select multiple values (facility Id) by pressing the Add button to add one value at a time and the cursor positions correctly to add next next value on the list.

It works correctly if I run the report on my machine (Window XP Pro). But it does not work the same on the server after I published it onto the server.

The drop-down list box list all the entries, but after pressing the Add button to add an entry the cursor does not move to the next entry on the list; it sits at the same entry so if user presses the Add botton again I would add in the same entry.

With your expertise, do you know what happens ? Is there any setting on the server (CE 10) needed to be done to resolve it ?

Posted By: hilfy
Date Posted: 20 Apr 2007 at 7:24am

I'm not very familiar with CE 10, so I'm afraid I'm not going to be much help here. It may just be the way CE 10 works and it becomes a user training issue.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date Posted: 20 Apr 2007 at 8:41am

Thank you, Hilfy.

Are you expert on with .NET. I'm looking for some samples:

1) A sample Web .NET (VB or C#) to run Crystal Reports plus all the Schedule/History features that the CE provides.

2) a sample Web .NET (VB or C#) to maintain some DB2 tables.

I've looked around on the internet but have not found any good samples.

Posted By: hilfy
Date Posted: 20 Apr 2007 at 10:08am

I do C#, but not yet ASP.NET. I'm VERY familiar with the SDK for BO XI, which is similar but not exactly the same as what's available in CE (does CE even have a .NET SDK??)

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: peter
Date Posted: 20 Apr 2007 at 11:07am

Yes. We are moving to XI soon.

Any way that is for the future. Now I just want to do one more new thing and it seems CR Designer 10 does not support it.

I want to have a Formular that spin a 2nd lookup table about 20 record selecting the value of a field, appending them all into one string to display on the report. And this should spin only once.

Can we do some thing like this? (on the table, not the parameter ?company})

dim i as number

dim concate as string

i = 1
concate = ""

do while i <= ubound({?company})
concate = concate + {?company}(i) + ", "

i = i + 1

loop

formula = concate

Print Page | Close Window