Conditional Totals Problem

[mdl2]

Hi:
I have a report which is grouped by region, program,
activity and project. Each project has multiple detail lines spanning multiple years. The cash flow budget amount is a fixed amount for a given year. I want to sum the total cash flow amount for the current year for each project over the region, program, and activity. I am also calculating the previous years expenditures so I have to include all of the records for previous years as well.
I can do an average for the project by the number of records that are included for the year. Is there any way to do a conditional running total to get the other group totals.

i.e
Region Central for fy 03/04
Activity Some
Program -- A program
spent cash flow fy
Project 123 200 30000 02/03
Project 123 300 30000 02/03
Project 123 200 40000 03/04
Project 123 200 40000 03/04
Project 123 400 80000 04/05
above data suppressed
Project prev exp current exp cashflow
proj 123 500 400 40000
proj 124 100 3000 30000

I want
Program A 600 3400 70000

 

[SBendBuckeye]

Sorry, I don't have much time. Try doing a search on Local and or Global variables and check some of those threads for a possible way of doing what I think you are asking. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[lbass]

Create the following formulas and place in the detail section:

//{@prevexp}:
if year({table.date}) = year(currentdate)-1 then {table.exp}

//{@currentexp}:
if year({table.date}) = year(currentdate) then {table.exp}

//{@currbudg}:
if year({table.date}) = year(currentdate) then {table.cashflow}

Insert a group on ProjectNo and then right click on and insert summaries (sums) on {@prevexp} and {@currentexp}. Check "insert summary for all groups" "insert grand total". Right click on {@currbudg} and insert a maximum. You only want the maximum at the ProjectNo group level. Drag the ProjectNo group name into the footer next to the summaries and suppress the detail section.

Then create three formulas:

//{@reset} to be placed in the Program group header and suppressed:
whileprintingrecords;
numbervar sumbudg := 0;

//{@accum} to be placed in the group footer for the Project# group and suppressed:
whileprintingrecords;
numbervar sumbudg := sumbudg + maximum({@currentbudg},{table.projectNo});

//{@display} to be placed in the group footer for the Program group:
whileprintingrecords;
numbervar sumbudg;

-LB