Trouble sorting results from formula 3

[rji1124]

Hi... we are using CR 7. I have a formula that uses a department assignment field to combine departments under a manager... like if problm.assign in ["xxx", "yyy"] then "Enterprise Ops" else...etc.

I wanted to order the departments in a specified order (other than alpha ascending), so I wrote another formula taking the previous formula and equating it with a number, thinking I could use this new formula in a sort.

I am displaying this in a bar chart...

The department sequence n the chart stays the same... I inserted numbers in front of the departments next and the chart displayed fine (except I have numbers in front of the departments which isn't going to go over well...

Any ideas about what I need to do???

Thanks

 

[dgillz]

Did you customize the group name field when you made the group so it would look back to the label field for the group name rather than the formula, thus returning 1, 2, 3....etc ? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

version 7 has the ability to specify the order of groups, without a formula. And this carries over to charts. Go to REport - Change Group Expert and click options.
Instead of Ascending select Specified, and then select the groups in the order that they should appear. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken-

I could not find this. The only options I have are to change the field that is being grouped on, and checkboxes for 'keep group together' and 'print group header at the top of each page'. I tried this in both 7 and 8.5. Do I need to do something else? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

Dgilz, do you have a setting for Ascending / Descending?
Use this same selector to select "Specified Order".
Very first lesson in my advanced class. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken-

I got it! I guess youre hinting that I need an advanced class huh? This bring to mind a followup question - is there specified order sorting as well? I looked and could not find it.

The only workaround I caoul dthink of was to "trick" crystal by creating another group, and then suppressing all group headers, footers, etc. and using specified order grouping on that group.

other ideas? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

There is no option for sorting, but your workaround is the same thing. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[rji1124]

Ken, your solution worked perfectly... thanks so much for your help again!

Bob

 

[fwatanabe]

Hi, I have a question about sorting as well... I am using Crystal 8 and SQL Server 7.

I have a formula field that looks like this:
//%Replace
if{equipment.replacecost} > 0 then
((Sum ({@TotalCost}, {equipment.eqnum}))/{equipment.replacecost}) * 100
else
0

It is essentially calculating the total cost of maintenance divided by the replacement cost of the equipment. My report spec wants the report sorted by this field, but it doesn't show up as a selection when I click on the 'Sort' button. I am assuming that this is because the calculation takes place after the 1st pass through all the records?

Does anyone know if it's possible to sort based on this field? How would I go about it?

 

[dgillz]

You are sorting on a subtotal so you need to use the TopN/Sort expert from the report menu. Select All and ascending or descending and you are done. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[fwatanabe]

This formula field doesn't show up in the TopN/Sort Expert either.

 

[kenhamady]

You have to reverse the order of operations to get this one to work.

1)Create a formula like this:

if{equipment.replacecost} > 0 then
{@TotalCost}/{equipment.replacecost} * 100


2)Create a subtotal of this formula for each product.

3)Use the new subtotal in your TopN function as Dgilz has desccribed. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[fwatanabe]

Thanks Ken and dgillz... I tried this and it worked. You guys are wonderful!