Help on summary and rank Plz 1

[Durango122]

Hello All
cr 9.0
ms sql
i have this report and has 3 rank
my main report has this gr1 call {terri.definition}
and have 2 sub report i used to rank the YTD and
TYD PER REP RANK

to get the last part i created this formula
----------------------------------------
@YTD_SLS_PER_REP
if
{TRANSACTION_HISTORY.POSTING_DATE} in Date(year({?sub2_End_Date}),1,1) to
Date(Year({?sub2_End_Date}),Month({?sub2_End_Date}),day({?sub2_End_Date}))
and
{TRANSACTION_HISTORY.TRADE_CLASS} = "S
then
sum({TRANSACTION_HISTORY.GROSS_AMOUNT})/{#Rep_Count
else
0
--------------------------------
i get right result but can get the group number to rank desc
i get this error
"A summary has been on a non-recurring field
detail: @YTD_SLS_PER_REP

here is how report looks like now

terri def WEEK SLS YTD SLS WEEK RNK YTDRNK YTD SLS PER REP
FSB-01 5000 1000 1 3
fsb-06 100 500 2 5

i have previous thread 149-1338203 and done what was suggested but it's my last rank i have problem
YTD RNK is a sub report
YTD SLS PER REP is a sub report

thanks


Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[lbass]

This looks like the problem:

sum({TRANSACTION_HISTORY.GROSS_AMOUNT})/{#Rep_Count

You can sort on running totals or formulas using running totals. Try replacing the running total with the equivalent of an inserted summary, e.g., count({somefield}).

-LB

 

[Durango122]

Hi -LB
i replace the formula with this
--------------------------------------
@YTD_SLS_PER_REP
if
{TRANSACTION_HISTORY.POSTING_DATE} in Date(year({?sub2_End_Date}),1,1) to
Date(Year({?sub2_End_Date}),Month({?sub2_End_Date}),day({?sub2_End_Date}))
and
{TRANSACTION_HISTORY.TRADE_CLASS} = "S
then
{TRANSACTION_HISTORY.GROSS_AMOUNT}
-----------------------------------
then place it in the detail and create a summary base on that
that gave me sum({TRANSACTION_HISTORY.GROSS_AMOUNT},
terri.definition)
then i created a running total {#Rep_Count}
summarize field: contact_id
type of summary: count
evaluated:
formula
contact.status = 'active'
on change group: terri.definition

then i create a formula to do the calculation
@result
@YTD_SLS_PER_REP/{#Rep_Count}
gave me the info need for the group footer
now i need to insert the group number and there is where i cant get to sort on this new formula @result

all i have to sort is sort on terri.definition
all
on @YTD_SLS_PER_REP decending
i cant see the @result formula to sort on

This all is missing on this report to complete it

Thanks






Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[lbass]

As I said, you cannot sort on a formula that contains a running total.

-LB

 

[Durango122]

Hi -LB
Sorry for misunderstanding you
I not sure how to do it
here is what i need
a YTD SLS PER REP and a RANK
---------------------------------------
1.
to get the base on the YTD Sale.
My formula is this
@YTD_SLS_PER_REP
if
{TRANSACTION_HISTORY.POSTING_DATE} in
Date(year({?sub2_End_Date}),1,1) to
Date(Year({?sub2_End_Date}),Month({?sub2_End_Date}),
day({?sub2_End_Date}))
and
{TRANSACTION_HISTORY.TRADE_CLASS} = 'S'
then
{TRANSACTION_HISTORY.GROSS_AMOUNT}
else
0
----------------------------
2.I need to get the number of rep that are active status per territory definition

here is how you get the right rep
-------------------------------------------
@ Active_ rep
if
{TRANSACTION_HISTORY.CSMT_STATUS} = 'Active'
then
CONCTACT.contact_id
-----------------------------------------
this gives de rep ID that are active


now i need to split these rep i got from this formula by territory.definition


my group is territory.definition

I'm so confused right now not sure what to do.
i got the other info and ranks but this one i'm stump







Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

Ops forgot
i just found out that the rep must be active depending on the date of the report
so the formula for the rep is this
@ Active_ rep
if
{TRANSACTION_HISTORY.POSTING_DATE} in
Date(year({?sub2_End_Date}),1,1) to
Date(Year({?sub2_End_Date}),Month({?sub2_End_Date}),
day({?sub2_End_Date}))
and
{TRANSACTION_HISTORY.CSMT_STATUS} = 'Active'
then
CONCTACT.contact_id

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[lbass]

Since you are doing this in a subreport, add a record selection formula in the subreport of:

{TRANSACTION_HISTORY.CSMT_STATUS} = 'Active'

Then use this formula:

//{@YTD_SLS_PER_REP}:
if
{TRANSACTION_HISTORY.POSTING_DATE} in
Date(year({?sub2_End_Date}),1,1) to
Date(Year({?sub2_End_Date}),Month({?sub2_End_Date}),
day({?sub2_End_Date}))
and
{TRANSACTION_HISTORY.TRADE_CLASS} = 'S' then
{TRANSACTION_HISTORY.GROSS_AMOUNT}
else
0

...and insert an average on this, and this would allow you to do a group sort.

-LB

 

[Durango122]

Hi -LB
Thanks for the replid.
I did you you said added {TRANSACTION_HISTORY.CSMT_STATUS} = 'Active'
to my record selection formula
and did an avrg on it

but this is an avrg of the year sale that the status is active not the # of rep active for this territory def

ex:
in the new changes you recommend
terrirory YTD SLS AVRG Rank

PFS02 PFS-California 1,115,235 111 3


but there 8,904 rep active for this territory

so if i take 1,115,235/8904 = 125 and not 111
you see what i mean


Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

thanks a million -Lb
I can count on you

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40