Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Multiple Sort Options Post Reply Post New Topic
Author Message
avaj
Newbie
Newbie


Joined: 26 Sep 2007
Online Status: Offline
Posts: 30
Quote avaj Replybullet Topic: Multiple Sort Options
    Posted: 03 Oct 2007 at 6:49am
I am using CR XI and I am trying to build a report that allows the user to select various sort options.  I have built the parameter to capture the user selection and built a formula to determine which one to use for the sort.  I put the formula on the group header.  This is the formula I have so far which is working:
 

  if {?Sort Field} = '2' then

     {VENDOR_ACCOUNT.ZIP_BASE} else

   if {?Sort Field} = '3' then

      {VENDOR_ACCOUNT.ACCT_NAME} else

     if {?Sort Field} = '4' then

        {%acct_tochar}

These are all string fields being sorted ascending.  The problem is Option '1' needs to sort on a running total field that is numeric (running total of dollar amt) and in descending order.  When I try to add that option by converting it to a string field, I get an error.

Any help or ideas on how I can include this would be greatly appreciated.
 
PS... this is my first time using Crystal Reports.
IP IP Logged
djcheung
Newbie
Newbie


Joined: 04 Oct 2007
Online Status: Offline
Posts: 1
Quote djcheung Replybullet Posted: 04 Oct 2007 at 4:26pm
Hey i had the same problem today and found a workaround for it.  Not sure if it'll work in your case but your formula is correct.  But i'd try to do it like this instead:
 
if {?Sort Field} = '2' then

     {VENDOR_ACCOUNT.ZIP_BASE} else

   if {?Sort Field} = '3' then

      {VENDOR_ACCOUNT.ACCT_NAME}

// Remove these lines
//     if {?Sort Field} = '4' then

//        {%acct_tochar}

 
and in the Record Sort Expert make a new formula field for your {%acct_tochar} and add it to the sort list after the above sort formula field.  What the report should do is try to look for the sort field 4 inside the sort formula above.. but if there's nothing there for it.. it will fall to the next field in the record sort expert and use that to sort it.  By doing this you're able to also make your {%acct_tochar} sort by descending.
 
Now it's a bit of a hack since you can only do this for one numeric field (if there's than one i'm not sure how'd i do it), so not sure if it's the "proper" fix but it worked for me.  Try it out and let me know.. i'm pretty new to crystal as well.
 
- Dave.


Edited by djcheung - 04 Oct 2007 at 4:28pm
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 05 Oct 2007 at 3:09pm

You  have to convert the number to a character.  The thing you have to be careful about, is that you have to left-pad the resulting string with spaces or zeros so that it sorts correctly.  Otherwise, your numbers will be sorted as if they're characters and it will look something like this:

10
100
101
11
 
You have to know what the maximum length of the number is in order to do this correctly.  For example, when I deal with lender numbers that are up to 3 digits long, I do the following:
 
ToText({lender.lender_number}, '000')
 
which gives me
 
010
011
101
 
This sorts correctly.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
avaj
Newbie
Newbie


Joined: 26 Sep 2007
Online Status: Offline
Posts: 30
Quote avaj Replybullet Posted: 09 Oct 2007 at 11:10am
I was able to create a formula as follows:
 
if {?Sort Field} = '1' then
     ToText ({#RTotalASold},'###,###,###,##0.00')
 
This is using a Running Total I have set up on the report & seems to be giving me the correct totals but in a string format.
 
The problem is this formula does not show up on the available fields list for sorting. 
 
Can anybody tell me why?  How can I get this into my sort?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 09 Oct 2007 at 12:00pm
You can't sort on running totals.  If you think about it, it makes sense - the numbers are being generated WHILE the data is being read so they're not available for actually sorting the data.
 
The only way I can think of to do this would be to create a SQL Command that will be used as one of your data sources.  Include the fields needed to link to the rest of the data and a count in the SQL statement and use that value instead of the running total for sorting.  However, Commands are not available for all databases - if you're using native SQL Server or native Oracle it shouldn't be a problem, but for  others it might be.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
avaj
Newbie
Newbie


Joined: 26 Sep 2007
Online Status: Offline
Posts: 30
Quote avaj Replybullet Posted: 09 Oct 2007 at 12:14pm
Users always ask for the most complicated report on your first time using a new tool....ha ha ha
 
Anyway, thanks for the help.  This forum has been a wonderful source of information!!!
IP IP Logged
Post Reply Post New Topic
Printable version 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



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.