how to sort a formula field with having running total of a formula fie 1

[jraja]

I have report having following fields and I'm including data for 4 rows as sample

Mnth1LD Mnth1Loc Mnth2LD Mnth2loc Month3LD Month3Loc
320.83 682.39 197.00 696.46 31.89 798.85
395.11 1137.26 430.86 1152.85 16.19 1249.93
458.22 569.35 255.82 578.33 19.69 713.48
370.08 1040.50 221.89 1049.27 16.42 1150.32


3MnthAvgLD 3MntAvgLoc Variance
193.25 725.89 91%
280.82 1180.11 86 %
244.64 620.39 84%
202.80 1080.03 90%

Here what I'm doing first I wrote formula to get the value of Mnth1LD Mnth1Loc Mnth2LD Mnth2loc Month3LD Month3Loc and then I get running total to get the value of 3MnthAvgLD all ld field and divide by 3 and
running total for all 3MntAvgLoc fields and divide by 3
then I addup 3MnthAvgLD and 3MntAvgLoc divided by Mnth1LD+ Mnth1Loc
and get the value into the variable
I need to sort that variance in asc order
like 91%,90%,86%,84%
which is comming from finalString
My question is how can I sort this variable
when I can not this formula in record sort exper,
group sort expert becuase I'm using runnig total RSumofFistMonth (because I can not get the vaule by adding simply by Mnth1LD + Mnth1Loc
following is the code

Numbervar VSum3MonthAvg:={@3MonthAvgLD}+{@3MonthAvgLocal};

NumberVar RsumFirstMonth:={#RSumofFistMonth};
NumberVar VdivValue:=RsumFirstMonth /VSum3MonthAvg;
NumberVar VdivValueTr:=VdivValue*100 ;
stringVar strVdivValueTr:=totext(VdivValueTr);
NumberVar pos:= instr(strVdivValueTr,".",1);
stringVar finalString:=left(strVdivValueTr,pos-1);
finalString+'%'


Can you help me please

 

[jraja]

Yes I'm using your formula
and how can I sort on it

 

[dgillz]

You cannot sort on this formula.

Normally you sort groups by using the TopN/Sort Group expert. Since the field you are wanting to sort on is not a summary field, but rather a mathematical result of a summary, you cannot sort on it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

Is there any way to get the summary field

 

[dgillz]

I don't think so.

The best this to do is to write a view or an SP which stores your summary data in a table. At that point crystal has a very easy report with only one formula field, and you can sort on it.

What is your database type?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

do you have any sample ???

 

[dgillz]

What is your database type?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

sql server 2000

 

[dgillz]

Go to the SQL server forum and ask for help there on creating a view with this summary data in it. When you have it come back here.

I am not much of a SQL guru.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

As your advised I worked out on sql server's view and now I have two view how can I do data comming from few fields from one view and other fields from other view
mean how can I use two store procedure in one report