Running Total Extract

[rafeman]

Hi there,

I have a table of nominal codes that hold different catagory codes and nett values against them eg;

Net Cat Code1 Cat Code2
345 10 20
678 10 30
34 20 10
467 10 20

There are thousands of these entries and I want to be able to check through the file and where the fourmla meets the criteria (ie cat code 1=10 & cat code 2=20) add those values to a variable.

This is probably simply to most of you guys but I'm a newbie!

I want to add all the criteria for each option into one formula and extract the total to a variable. These variables will then be palced on the report and totalled etc.

Thanks for your help

 

[IanWaterman]

You can also do this with a running total.

Put your Net Field into the window field to summarise.

In your evaluate window check the formula button and write a boolean formula. eg
catcode1 = 10 and catacode2 = 20

In reset check never.

This should now just sum the net figures whenever the above is true.

Ian

 

[rafeman]

Hi Ian,

Thank you for your comments. I have previously done it this way. The problem is the report is very slow as there are many running totals. You cannot add these running totals up using a formula if the result of one is null. I have got around this by putting a fourmla between which converts to 0 if is null.
Somebody has suggested doing it with varibles so it only "reads" the table once and adds the totals to the variables.

Regards

 

[lbass]

It would probably help if you provided more information about your report design. With the information provided so far, I would suggest limiting the records in the report to those that meet your criteria, i.e., in the edit selection formula->record area add:

{table.catcode1} = 10 and
{table.catcode2} = 20

Then you could just insert summaries on the records.

If you used running totals, why would there be more than one? I think you are leaving out key information.

-LB

 

[IanWaterman]

Not totally sure variables will be any faster than running totals. Also variables do not like Nulls either so you will have to test for them in the formula below and tell the variable what to do if it encounters a null.

In you detail line add formula
@evaluate

whileprintingrecords;

global numbervar NetValue;

If Comcat1 = 10 and comcat20 =20 then NetValue:= NetValue {NetField};

You will need to expand this with further If ..then.. else to cover null conditions.

In report footer add this formula
@display

whileprintingrecords;

global numbervar NetValue;

This assumes you do not want to reset, if this is for groups you will place display in group footer and an additional formula, @reset in group header.

@Reset

whileprintingrecords;

global numbervar NetValue:=0;

If you want for other groups and grand total you will need to add additional variables. YOu can expand the evaluate formula with more variables, just end each evaluate statement with a ; However, you will need separate display formula for each variable

Hope this helps

Ian