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

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

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
Asked:
psokolo
  • 7
  • 3
  • 3
  • +2
1 Solution
 
OutinCommented:
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
 
mlmccCommented:
It cannot be a PRINT TIME formula.  Some formulas are inherently PRINT TIME and therefore cannot be used for sorting.

mlmcc
0
 
bdreed35Commented:
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
psokoloAuthor 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
 
bdreed35Commented:
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
 
psokoloAuthor 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
 
psokoloAuthor 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
 
GJParkerCommented:
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
 
psokoloAuthor Commented:
I already have it the way you posted ... however somehow it is not allowing me to sort it by my formula field
0
 
GJParkerCommented:
Are you saying you now have 2 formulas ? if so post the entire formulas for both.

Gary
0
 
psokoloAuthor 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
 
psokoloAuthor Commented:
it still is not appearing as a field to sort by.
0
 
GJParkerCommented:
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
 
psokoloAuthor 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
 
mlmccCommented:
Is SUM inherently PRINT TIME thus done after all sorting is done?

mlmcc
0
 
bdreed35Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now