• Status: Solved
• Priority: Medium
• Security: Public
• Views: 624

# Sort by formula field

Hi
I need to sort a report in Crystal Reports 8.5 by formula field that I created.
It seems to me to be impossible to sort a report by custom fields.

Any ideas ??
Thanks,
Pawel
0
psokolo
• 7
• 3
• 3
• +2
1 Solution

Commented:
Sorting by a formula field is possible.

What's the formula?

If it contains WhilePrintingRecords, delete that (if not needed for other reasons), and try again...

--
Outin
0

Commented:
It cannot be a PRINT TIME formula.  Some formulas are inherently PRINT TIME and therefore cannot be used for sorting.

mlmcc
0

Commented:
Don't make us guess about what your formula is, please post it for us so you can get some actual solutions to try.
0

Author Commented:
unfortunately i have "WhilePrintingRecords" and i think i need it ... since i am calculating a difference between two group totals... for year 2004 and 2005 ( in this particular case)
Any hope for me ??
0

Commented:
If you are using standard summary functions, you will not need the "WhilePrintingRecords" in your formula, but since you didn't post the formula, I can't tell you that for sure.

Please post the formula and show some sample layout if you can.
I thinking you have some higher level grouping, and then a grouping on year?  This all critical information on determining if you can use a group sort or not.
0

Author Commented:
ok here is my code ... i calculate the changes in totals :
whileprintingrecords;
currencyvar firstyr;
currencyvar secondyr;
currencyvar thirdyr;

numbervar changetot12;
numbervar changetot23;

//first year
firstyr:=Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode});
//second year
secondyr:=Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode});
//third year
thirdyr:=Sum ({spVolumePerParentAcct;1.TYearTotal}, {spVolumePerParentAcct;1.AccountCode});
If firstyr>0 and secondyr<>0 then Changetot12:= ((secondyr-firstyr)/firstyr)*100;
if secondyr>0 and thirdyr<>0 then Changetot23:= ((thirdyr-secondyr)/secondyr)*100;

and then i just show changetot12 and changetot23;
0

Author Commented:
ok i made some adjustments to my report but still i cannot sort it by my formula field
here is a code for my formula field :

(Sum ({spVolumePerParentAcct;1.TYearTotal}, {spVolumePerParentAcct;1.AccountCode})-
Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}))*100
/Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode})

any ideas ?

Thanks,
Pawel
0

Commented:
To work with what you've already got you can just remove the WhilePrintingRecords; statement as this isn't needed for this formula

Ipersonally woudl get rid of the variables completely and create 2 formulas

//@ChangeTot12
If Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode}) > 0 and
Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}) <> 0 Then
(Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}) - Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode}))/Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode})
Else
0

//@ChangeTot23
If Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}) > 0 and
Sum ({spVolumePerParentAcct;1.TYearTotal}, {spVolumePerParentAcct;1.AccountCode}) <> 0 Then
(Sum ({spVolumePerParentAcct;1.TYearTotal}, {spVolumePerParentAcct;1.AccountCode}) - Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}))/Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode})
Else
0

This will allow you to sort by either of these resulting formulas

HTH

Gary
0

Author Commented:
I already have it the way you posted ... however somehow it is not allowing me to sort it by my formula field
0

Commented:
Are you saying you now have 2 formulas ? if so post the entire formulas for both.

Gary
0

Author Commented:
//@ChangeTot12
if Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode})<>0 then
(Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode})
-Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode}))*100
/Sum ({spVolumePerParentAcct;1.FYearTotal}, {spVolumePerParentAcct;1.AccountCode})

//@ChangeTot23
If Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}) <> 0 Then
100*(Sum ({spVolumePerParentAcct;1.TYearTotal}, {spVolumePerParentAcct;1.AccountCode})
- Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode}))
/Sum ({spVolumePerParentAcct;1.SYearTotal}, {spVolumePerParentAcct;1.AccountCode})
Else
0
0

Author Commented:
it still is not appearing as a field to sort by.
0

Commented:
How are you trying to sort ?

Should be Report menu -> Group sort expert  -> For this group sort = all and formula should be available to select .

Gary
0

Author Commented:
yes i am trying to sort this way ... i dont really know what i am doing wrong ... I never actually was able to sort by formula field
0

Commented:
Is SUM inherently PRINT TIME thus done after all sorting is done?

mlmcc
0

Commented:
SUM is not a PRINT TIME formula, but it is calculated after the record sorting is done.

I think you are having issues with these formulas not showing up because of the calcaulations you are doing and that they are not based off just one summary.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.