[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

Crystal Sort order

Hi hope this is easy.  Would like to build a formula for Grouping/sorting purposes.  I would like to sort a currency field descending then a character field.  I was able to use the ToText Function to convert the Currency to a string then concatenate it to my text field. Problem is the currency doesn't seem to want to format.  Here is the Syntax: totext({TimeEntry.TEBillRate},0,'####')+{Employee.EmpTitle}.  It is displaying as $85 + the sting, I would like to have it come out with a couple leading blanks with no dollar sign like this "  85"+Text.  thanks  

0
GregFromSeattle
Asked:
GregFromSeattle
3 Solutions
 
mlmccCommented:
I think you can only use 0
leading 0s

Try this

 totext(CDbl(({TimeEntry.TEBillRate},0,'####'))+{Employee.EmpTitle}

mlmcc
0
 
agandauCommented:
Perhaps you could cast your currency as a cDbl first.
Also, one way to pad the beginning of the expression is by using the Space() function.

This, for example, should get you close to where you want to be.

space(10 - len(totext(CDbl ({TimeEntry.TEBillRate}),0,'####'))) + totext(CDbl ({TimeEntry.TEBillRate}),0,'####')+{Employee.EmpTitle}  

It will calculate the length of your billrate cast as a string, so if it's 85, the length of "85" is two, and it will pad with 8 leading spaces.

Hope this helps.
0
 
James0628Commented:
mlmcc's formula should work, but you need to remove/move a couple of parentheses.  The following should work:

 totext (CDbl ({TimeEntry.TEBillRate}),0,'####')+{Employee.EmpTitle}


 FWIW, the main thing in both mlmcc's and agandau's suggestions is to convert the field to a regular numeric data type (using CDbl) first.  Apparently your field is a currency data type and when converting one of those to a string, CR automatically adds a "$".  If you convert your field to a regular number first, CR won't do that.

 James
0
 
GregFromSeattleAuthor Commented:
All comments helped but agandau helped the most with the leading blanks to get it to sort properly thank you all!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 For the record, the "####" format should also give you leading spaces.  If you want more spaces, just add more #'s.

 James
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now