summary cant be created problem 1

[fsreport]

Hi All
MS sql
cr 9.0
My co-worker drop me off this report his stuck.
In the report he has this
this formula
@SMA_Division
sum({@Sale_SMA},{vw_all_contact_labels.CONTACT_ID})/{#GroupCount}

my running total is
#GroupCount
is difine like this
field to summarize: vw_all_contact_labels.LABEL_NAME
type of summary: distinct count
Evaluate on each record
on change of group: vw_all_contact_labels.LABEL_NAME

this works fine to give me the total at the contact_id group
when i place @SMA_Division
formula in the group footer of contact_id group

now i need to be able to take formula
@SMA_Division
and have the total at the territory level

i tried
sum(@SMA_Division,vw_territory_external_wholesaler.terr1})

i get error field cant be summarize

i need to be able to get that result
Please help me.



fsreport

 

[lbass]

Your description of the running total is again incorrect, as it would always return a one.

You need a separate running total for the group count at the territory level, but you should probably be using a formula like:

sum({@Sale_SMA},{vw_territory_external_wholesaler.terr1})
/{#territoryGroupCount}

In other words, sum the same thing, only at a different group level. Hard to tell though.

-LB

 

[dgillz]

Please post your formulas for {@Sale_SMA} and {@SMA_Division}

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[fsreport]

hi All
here are the formulas
@SMA_Division
sum({@Sale_SMA},{vw_all_contact_labels.CONTACT_ID})/{#GroupCount}

@Sale_SMA
if
{FUNDS.PRODUCT_TYPE} ="SMA" and
{TRANSACTION_HISTORY.TRADE_CLASS} = "S"
then
round({TRANSACTION_HISTORY.GROSS_AMOUNT})



fsreport

 

[fsreport]

hi
Also why i'm using this running total

my running total is
#GroupCount
is define like this
field to summarize: vw_all_contact_labels.LABEL_NAME
type of summary: distinct count
Evaluate on each record
on change of group: vw_all_contact_labels.LABEL_NAME

there could be X number of label assign to the contact_Id
so by using distinct count it gives me a count of label for each contact_id some have only 1 label some have 2 or 4 or 5

so i need to add the number return by the running total to the territory level

i created this running total at the territory level

#Territory_Count
field to summarize: vw_all_contact_labels.LABEL_NAME

type of summary: count
evaluate: on change of group contact_id
reset on change of group: Territory

it gives me 49 and when i count #GroupCount for that territory i get 99




fsreport

 

[fsreport]

ops
error on # running total

my running total is
#GroupCount
is define like this
field to summarize: vw_all_contact_labels.LABEL_NAME
type of summary: distinct count
Evaluate on each record
on change of group: contact_id

fsreport

 

[fsreport]

More info supplied

example of my report
gr1Territory:W01
gr2contact_id******Num of label*******SMA_Amount
00001**************3****************500
00002**************5****************10000
00044**************2*****************1000

total for W01 10 1150

i need to divide the total sma by the total of label
i tried creating a running total to add the label number
and cant do it

is there a way to get the total of label return by my running total from previous post
my running total is
#GroupCount
is define like this
field to summarize: vw_all_contact_labels.LABEL_NAME
type of summary: distinct count
Evaluate on each record
on change of group: contact_id

that's all i'm missing to complete this report



fsreport

 

[lbass]

You should test first though to see if you need a running total in either case. Your {#groupcount} appears to be the equivalent of:

distinctcount({vw_all_contact_labels.LABEL_NAME},{table.contact_id})

At the territory level, it would be:

distinctcount({vw_all_contact_labels.LABEL_NAME},{table.territory})

In any case, the territory count should have been set up like this:

Territory_Count
field to summarize: vw_all_contact_labels.LABEL_NAME
type of summary: distinctcount
evaluate: for each record
reset on change of group: Territory

This would work IF the label names were specific to each contact_ID, i.e., if the same label_name could not appear in more than one contact_ID. If they can, then you should use a variable. Create three formulas:

//{@reset} to be placed in the territory GH:
whileprintingrecords;
numbervar cntforterr := 0;

//{@accum} to be placed in the contact ID group footer:
whileprintingrecords;
numbervar cntforterr := cntforterr + {#GroupCount};

//{@display} to be placed in the territory group footer:
whileprintingrecords;
numbervar cntforterr;
sum({@SaleSMA}, {table.territory})/cntforterr

-LB

 

[fsreport]

Hi -LB
Not the labels are not specific for each contact-id
some most have diffrent labels name assign
So your saying it's not possible to sum the running total number #GroupCount
Thanks



fsreport

 

[lbass]

No, I am saying that then you need to use the formulas in my last post to sum the running total. I built your calculation into the display formula.

-LB

 

[fsreport]

hi -LB
Thank you so much for your help


fsreport

 

[fsreport]

Hi -Lb
for some reason it resetting the count even when it the same territory
ex:
C01 on the first page the accum number start at 3 and end at 47
second page it start a 0 and ends at 50
third page start at 2 and ends at 48
page 3 start at 1 to 25
page 4 start 0 to 25
page 5 start 1 to 16


then the formula takes the number 16 from the last page and divided the sma
it should add all the number for that territory and not restting after new page



fsreport

 

[lbass]

Sounds like you have a repeating group header. Change the reset formula to:

//{@reset} to be placed in the territory GH:
whileprintingrecords;
numbervar cntforterr;
if not inrepeatedgroupheader then
cntforterr := 0;

-LB