Summary help 1

[JCook23]

CR 10.0
SqlServer

I have two tables:

POMAST
POITEM

I have one Group by Customer. What I want is to see the sum for orders that are past due from 'today', the sum of orders due remaining of the month, and the sum for each of the next three months. The field that I sum is poitem.ordamt. Here is an example of what I would like to see:
Past Due Due
Customer from 7/27 7/28-7/31 Due in Aug Sept Oct
ABC Co. DETAILS

Totals $5000 $100 $10000 $10000 $5000


I just do not know where to start. Obviously the formulas would all be about the same. Any suggestions on doing this without subreports? Everything needs to be reliant on the date the report is run.

Thanks,

Jeff

 

[RealityCheque]

Just off the top of my head, but have you tried creating another group by date, grouping in Specified Order and creating formulas for each selection?

The formulas would be something like...

{table.datefield} = AllDatesToToday etc

"I do not have to forgive my enemies. I have had them all shot."
- Ramon Maria Narvaez

 

[Madawc]

What about running totals? Use formulas to select just those records that fit the particular total.

Madawc Williams (East Anglia)

 

[lbass]

Group on Customer ID. Then create a series of formulas:

//{@today}:
if {table.duedate} < currentdate then {table.amt}

//{@restofmonth}
if {table.duedate} in currentdate to dateadd("m", 1, date(year(currentdate),month(currentdate),01))-1 then {table.amt}

//{@nextmonth}:
if {table.duedate} in dateadd("m", 1, date(year(currentdate),month(currentdate),01)) to dateadd("m",2,date(year(currentdate),month(currentdate),01))-1 then {table.amt}

//{@twomonthsfromnow}:
if {table.duedate} in dateadd("m", 2, date(year(currentdate),month(currentdate),01)) to dateadd("m",3,date(year(currentdate),month(currentdate),01))-1 then {table.amt}

//{@threemonthsfromnow}:
if {table.duedate} in dateadd("m", 3, date(year(currentdate),month(currentdate),01)) to dateadd("m",4,date(year(currentdate),month(currentdate),01))-1 then {table.amt}

Then you would insert summaries (sums) on each of these formulas and then suppress the details. You can drag the company name into the group footer to display the results.

-LB

 

[JCook23]

LB,

That was EXACTLY what I was looking for....THANKS A MILLION!

Thanks to everyone else for their help. All post helped me look at things differently. I appreciate all of your time and efforts!

Jeff C.