sorting group formula fields

[Former Member]

hi,

i am using crystal report for my business purpose. now i created a report and my team need to make a value as (ABS) to make it all +ve value. for that i made a formula but when i use this formula for my report i cannot sort using group sort expert because it is a formula i created for that group.

kindly advice me how can i do it .your early action is appreciated.

[Former Member]

hi Former Member: i been used abs then only i got the positive values. its working fine in my report.you can review the attached picture.

Abhilash Kumar : your answer is near to solve my issue. i used the same record sort expert.but my issue is i could not see the formula field in the report expert window as i placed the formula in the group filed becuse i need the total value only to make abs() otherwise it will not be correct answer if i place the formula in detailed area. kindly check the picture i attached for your reference. once the value reach to record expert i can make asc.

[abhilash_kumar]

What is the code you've used in the @absv formula?

-Abhilash

[Former Member]

sorry for delay reply.

the formula which i used for @absv is;

abs(Sum ({@Variance SR - Current Month}, {CR_COUNT.CNT_ITEM})-Sum ({@COGS SR-Current Month}, {CR_COUNT.CNT_ITEM}))

it means abs(Actual Cost-Theoretical cost) in as per report heading

[abhilash_kumar]

Hi Refeesh,

You cannot use this formula in any of the Sorts.

Here's what you need to do:

1) Create a formula with this code:

{@Variance SR - Current Month} - {@COGS SR-Current Month}

2) Go to the Insert Summary option > Use this formula field as the 'Field to summarize'.

Choose 'Sum' as the sum function.

Under 'Summary Location', choose Group #2.

3) Go to the Group Sort Expert > Select Group #2 > Under 'For this group sort', choose 'All'.

Beside 'Based on', choose the Sum of formula field from step 2 > Choose a sort direction.

-Abhilash

[Former Member]

kindly review the below snapshot and advice how can i do it correctly

[former_member205840]

Hi Rafeesh,

Do one thing : first convert all values into positive, i.e. if any negative values then multiply it with -1 then apply ABS()  (note it is not even necessary that you can apply ABS() if you have all positive values)

Try this :

If {number} < 0 then {number}*-1 else {number}

then sort it.

Thanks,

Sastry

[abhilash_kumar]

Hi Refeesh,

The Group Sort Expert sorts groups on 'summary fields'.

Since this is not a 'summary' but a normal field, you'd need to use the 'Record Sort Expert'.

Open the Record Sort Expert and add this formula field to the sort list.

-Abhilash