how to set evaluation in manual running total

[Former Member]

Morning all,

I am trying to create a manual running total however I need to setup an evaluation by using a filter field.

For example

Running Total:

Summary: foc.doc_no

Summary Type: Distinct Count

Evaluation: Formula - foc.linetype="F"

Reset: Group2(foc.reasoncode)

Now the question is how to use the above in the formula.

Whileprintingrecords;
numbervar x:=0;
numbervar y:=0;
numbervar z:=0;
x:=DistinctCount(foc.doc_no),foc.reasoncode;
y:=(foc.linetype="f")

If y=true then 1;
z:=x+sum(y)
z;

I know the above code could be wrong (most probably is) however it gives you an idea what I am trying to achieve.

If I use temp SQL Command. the report takes forever to calculate so I don't think I would be using that.

Any ideas how to do this sort of running total (manual).

Many thanks

Regards

Jehanzeb

[Former Member · Accepted solution]

Hi.Jehanzab,

Mannual running total should be like three steps :

--Initilize your variable

--evaluate / increase / decrease

--Display it.

In your formula, you will have to divide this into three parts and pace in sections where you wants this to be initilized / evaluated /displayed

@init

Whileprintingrecords;

numbervar x:=0;

numbervar y:=0;

numbervar z:=0;

@eval

Whileprintingrecords;

numbervar x;

numbervar y;

numbervar z;

x:=DistinctCount(foc.doc_no),foc.reasoncode;

y:=(foc.linetype="f")

If y=true then 1;

z:=x+sum(y)

@Display

Whileprintingrecords;

numbervar z

z;

Thanks,

Sastry

[Former Member · Accepted solution]

Sastry thanks for your quick response however I have tried your method and the problem comes when I use the following line

Whileprintingrecords;

numbervar x;

numbervar y;

numbervar z;

x:=DistinctCount(foc.doc_no),foc.reasoncode;

y:=(foc.linetype="f")

If y=true then 1;

z:=x+sum(y)

The error says "A number is required here". Well obviously a number however how to create or count orders when linetype =f?

That is the main issue.

Many thanks

Regards

jehanzeb

[Former Member · Accepted solution]

Hi ..

Here you are assigning a string to number variable so, it is giving an error.

Do the following :

Whileprintingrecords;

numbervar x;

Stringvar y;

numbervar z;

Numbervar cnt:

x:=DistinctCount(foc.doc_no),foc.reasoncode;

y:=(foc.linetype="f")

If y=true then

cnt:=cnt+1;

z:=x+cnt

Thanks,

Sastry

[Former Member · Accepted solution]

Sastry no matter what I do it keep saying "String is required" here is the code again

Whileprintingrecords;
numbervar x;
Stringvar y;
numbervar z;
Numbervar cnt;
x:=DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});
y:={foccredsum.line_type}="F";

If y=true then
cnt:=cnt+1;
z:=x+cnt

Even if I put brackets around it it does not make any difference.

Any other ideas?

Regards and Many thanks

J

[Former Member · Accepted solution]

Hi Jehanzab,

Can you please try the following :

Whileprintingrecords;

numbervar x;

Stringvar y;

numbervar z;

Numbervar cnt;

x:=DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

if {foccredsum.line_type}="F" Then

cnt:=cnt+1;

z:=x+cnt

Thanks,

Sastry

[Former Member · Accepted solution]

Thanks Sastry,

This has eliminated the error message however the total output which comes out is incorrect.

Total Jobs were 87 however if I use the formula you have provided it shows 194.

I am not sure if it is filtering the records correctly or not.

Many thanks

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Oct 7, 2008 4:56 PM

I'd like to ask you another question if it is ok, when we are using Groups in our report and there are lets say 2 groups with the same fields.

When I try to put the group next to my field, for example

x:=DistinctCount({foccredsum.doc_no},GroupName ({foccredsum.reason_code}));

Can you see it says GroupName, I have no idea what needs to be entered there. If I leave it like that it says enter group name but I don't know the group name.

Any ideas on this please?

[Former Member · Accepted solution]

Please try this :

Whileprintingrecords;

numbervar x;

Stringvar y;

numbervar z;

Numbervar cnt;

if {foccredsum.line_type}="F" Then

x:=DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

cnt:=cnt+1;

z:=x+cnt

[Former Member · Accepted solution]

It is just because there is a word GroupName..

Try this:

x:=DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

Thanks,

Sastry

[Former Member · Accepted solution]

Sastry that is what I have also tried and that time the number went upto 329 instead of 87. So I changed it back.

I don't know where it is getting the records from when I have a formula which this formula should be counting at.

It has to look at the running total of the Group and then count those numbers and show it on the report footer. However this is what it is not doing.

Regards

Jehanzeb

[Former Member · Accepted solution]

Yes that is I figured out but the problem still presist about the total number of jobs. I simply don't understand why it is not working properly.

Shall I upload the screenshot and show you?

[Former Member · Accepted solution]

Hi Jehanzab,

Have you reset the cnt variable in initilization ?

Like

numbervar cnt:=0;

Thanks,

Sastry

[Former Member · Accepted solution]

Morning Sastry,

If I reset the counter it only pick the last number which is 9 in the detail section. If I create a reset and insert

Whileprintingrecords;
Global Numbervar cnt:=0;

and place it in the group header (suppress it), it shows the last number which is 27.

Somehow it is not counting the actual number of Jobs, what it is doing is, it prints the last number in the detail section as the grand total.

So if I do not initialise cnt with 0, it says 194 (this 194 number is the last number in the list).

Here is what list looks like

Number of jobs

10

10

11

11

11

10

10

9

8

15

9

9

-

---

9

This is when I use reset cnt:=0, however if i remove the reset, it shows like this

Number of jobs

177

177

178

179

180

194

194

194

-

---

194

Bearing in mind the formula is entered into the detailed section of the report, even if I put it in the group section it produces the same result.

Here is the formula with reset

Whileprintingrecords;
numbervar x;
Stringvar y;
numbervar z;
Numbervar cnt;
Numbervar cnt:=0;
x:=DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});
if {foccredsum.line_type}="F" Then    
cnt:=cnt+1;
    z:=x+cnt

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Oct 8, 2008 8:52 AM

I am wondering if there is anyway of counting only the sub totals? at the end of the day, total number of sub totals is equal to grand total?