Count * Field 2

[CrystaLv]

Please, help me to resolve this problem.
I have few string and 1 Number fields on my Report
Groupped by Code.

Code ID1 ID2 OrderNo Cost(Number)

I need to have
Count of ID1 per Code
Count of ID2 per Code
which I just inserted Count and it works.

Now I need Count{ID1}*Cost
then result of this to divide by ID1.

What is the best way to go when you need to work with Counts and then manipulate it?

Thanks

 

[synapsevampire]

Try a formula in the group header of:

whileprintingrecords;
numbervar MyCost:=0;

Then in the details:
whileprintingrecords;
numbervar MyCost;
if not(isnull({ID1})) then
MyCost:=MyCost+{cost}

Now in the group footer you can reference the formula using:
whileprintingrecords;
numbervar MyCost;

And you can divide it by your existing count formula (although I'm suspicious of your count results).

-k

 

[CrystaLv]

Hi and thank for helping but where is a group header this formula should go...'Keep Together'?

 

[CrystaLv]

and also where in Detail should it go?

 

[synapsevampire]

Must be a communication barrier.

" but where is a group header this formula should go...'Keep Together'?"

Your post stated that you grouped by code, so in the group header of the code.

"and also where in Detail should it go? " Um, anywhere in the details, it's just used for accumulating the values.

-k

 

[CrystaLv]

I must be missing good point here. To insert formula on Group I go to sectionExpert and I have those x2 places to insert formula into, right? But every x2 has section it is corresponding to? Am I doing something wrong? Thanks

 

[synapsevampire]

Create formulas and place them on the report canvas.

-k

 

[CrystaLv]

Oooops...my mind is blogging. Thanks I am doing it immediately.

 

[CrystaLv]

One more stupid question, I am sorry. I have created 3 formulas I called Heatherm Detail and Footer and I placed them accordingly into Report.
All these fromulas are showing me Zeros througout whole 13 pages of the Report. But there IS data.

 

[synapsevampire]

OK, let's cover the basics.

Why are you counting these IDs? Are some null? If not, then the count is always the same per group.

Try placing this formula in the group footer:

sum({table.cost},{table.code})

The key here is why you are counting these IDs, I auumed that it was because some of them are null.

Also, post any formulas that you try as we go along since you're new to this.

-k

 

[CrystaLv]

All right, thanks.

I am counting these IDs to find out how many unique ID1s per Code. Then how many ID2s per code. To have ID1 divided by ID2 later on.
So I have:
Groupped by Code.

Code ID1 ID2 OrderNo Cost(Number)
1-1 123 2222 12345 $123.00
1-1 133 2222 12345 $123.00
1-1 134 2222 12345 $123.00

I need to have
Count of ID1 per Code which is DistinctCount{ID1}
Count of ID2 per Code which is DistinctCount{ID2}
Count of OrderNo which is DistinctCount{OrderNo}

which I just inserted Summary DistinctCount.

Now I need :
1.DistinctCount{ID2}/DistinctCount{ID1}
2.Cost*DistinctCount{ID1}
3.(Cost*DistinctCount{ID1})/DistinctCount{ID2}


So basically I have Group by Code and 3 fromulas DistinctCount of those D1 D2 and OrderNo.


I hope I clarified...thanks

 

[lbass]

What you are missing is the group condition. If you have a group on {table.code}, then your formulas should look like this:

DistinctCount({table.ID2},{table.code})/DistinctCount{table.ID1},{table.code})

Cost*DistinctCount({table.ID1},{table.code})

(Cost*DistinctCount({table.ID1},{table.code})/DistinctCount({table.ID2},{table.code})

-LB

 

[CrystaLv]

I am speachless, thanks a lot to lbass and synapsevampire for your help. You are great, thanks a million.