Solved

# Sort by formula field

Posted on 2005-04-21
571 Views
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
Question by:psokolo

LVL 9

Expert Comment

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

LVL 100

Expert Comment

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

mlmcc
0

LVL 28

Expert Comment

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 Comment

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

LVL 28

Expert Comment

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 Comment

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 Comment

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

LVL 19

Expert Comment

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 Comment

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

LVL 19

Expert Comment

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

Gary
0

Author Comment

//@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 Comment

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

LVL 19

Expert Comment

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 Comment

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

LVL 100

Expert Comment

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

mlmcc
0

LVL 28

Accepted Solution

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

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…