Calculate the running total of group field

[infinitizon]

Hi all,
I know this may look really simple but I dont seem to be finding my way around it.
Ok, I have a report that calculates the running group total in the group header based on certain conditions, what I want to do now is to get the running total of the displayed result in the report footer. I've tried all the techniques I know without success.
Help!

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[MCuthill]

infinitizon,

At a high-level, increment a second variable in your Running Total, then create a display formula in the Report Footer.

You will need 5 Formulas:
1) Report Header Reset -- set the Rpt_Total variable to zero
2) Group Header Reset -- where you currently reset the Grp_Total variable to zero.
3) Accumumlation Formula -- Increment both variables.
4) Group Footer Display -- display the Grp_Total value
5) Report Total Display -- display the Rpt_Total value

Please advise if you need specific assistance with your scenario. Please provide your formula's should you need anything further.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[infinitizon]

@Mike:
I'm sorry but I dont seem to understand what you are trying to say
If you could explain the flow and what each does, I'd appreciate.

Thanks

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[lbass]

I think you should the content of the formulas you are currently using to calculate the running group total, as a first step.

-LB

 

[infinitizon]

ok I have a procedure like so:

  Create procedure [dbo].[procAgedAccRecievable]
 @theDate datetime, @interval INT
 as
 begin

	DECLARE @interval2 INT, @interval3 INT, @interval4 INT, @interval5 INT, @interval6 INT, @incr1 INT, @incr2 INT,@incr3 INT,@incr4 INT,@incr5 INT

	SET @interval2=@interval*2
	SET @interval3=@interval*3
	SET @interval4=@interval*4
	SET @interval5=@interval*6
	SET @interval6=@interval*12
	
	SET @incr1 = @interval+1
	SET @incr2 = @interval2+1
	SET @incr3 = @interval3+1
	SET @incr4 = @interval4+1
	SET @incr5 = @interval5+1
	
select a.[Customer No_],c.Name, c.[Customer Posting Group], b.[Entry No_], b.[Posting Date], SUM(a.Amount)[Balance]
,(case when b.[Posting Date] between DATEADD(day, -@interval, @theDate) and @theDate then sum(a.Amount) end)[1]
,(case when b.[Posting Date] between DATEADD(day, -@interval2, @theDate) and DATEADD(day, -@incr1, @theDate) then sum(a.Amount) end)[2]
,(case when b.[Posting Date] between DATEADD(day, -@interval3, @theDate) and DATEADD(day, -@incr2, @theDate) then sum(a.Amount) end)[3]
,(case when b.[Posting Date] between DATEADD(day, -@interval4, @theDate) and DATEADD(day, -@incr3, @theDate) then sum(a.Amount) end)[4]
,(case when b.[Posting Date] between DATEADD(day, -@interval5, @theDate) and DATEADD(day, -@incr4, @theDate) then sum(a.Amount) end)[5]
,(case when b.[Posting Date] between DATEADD(day, -@interval6, @theDate) and  DATEADD(day, -@incr5, @theDate) then sum(a.Amount) end)[6]
,(case when b.[Posting Date] < DATEADD(day, -@interval6, @theDate) then sum(a.Amount) end)[7]
from [Detailed Cust_ Ledg_ Entry]a
join [Cust_ Ledger Entry]b
on a.[Customer No_]=b.[Customer No_]
join [Customer]c
on c.No_ = a.[Customer No_]
where b.[Entry No_] = a.[Cust_ Ledger Entry No_] and a.[Posting Date] <= @theDate 
group by b.[Entry No_],a.[Customer No_],c.[Customer Posting Group],b.[Posting Date],c.Name

This will age the customer ledger based on how much purchase the customer made over certain period.

Inside CR10, I created a group field by a.[Customer No_].
In order to get the sum over each periods, I created a summary for each a.[Customer No_] so as to get the group totals and display only those whose balance sum is positive. For instance

This is a sample data when you run the procedure

CK07 *** *** *** 2009-12-02 00:00:00.000 20000.00 NULL NULL NULL NULL NULL NULL 20000.00
CK07 *** *** *** 2009-11-27 00:00:00.000 -65000.00 NULL NULL NULL NULL NULL NULL -65000.00
CK07 *** *** *** 2009-11-16 00:00:00.000 45000.00 NULL NULL NULL NULL NULL NULL 45000.00
CK07 *** *** *** 2009-02-27 00:00:00.000 -22811.40 NULL NULL NULL NULL NULL NULL -22811.40
CK08 *** *** *** 2009-07-27 00:00:00.000 -50000.00 NULL NULL NULL NULL NULL NULL -50000.00
CK08 *** *** *** 2009-07-06 00:00:00.000 50000.00 NULL NULL NULL NULL NULL NULL 50000.00
CK09 *** *** *** 2010-06-16 00:00:00.000 -21500.00 NULL NULL NULL NULL NULL NULL -21500.00
CK09 *** *** *** 2011-07-14 00:00:00.000 30000.00 NULL NULL 30000.00 NULL NULL NULL NULL
CK09 *** *** *** 2011-07-22 00:00:00.000 -34000.00 NULL NULL -34000.00 NULL NULL NULL NULL
CK09 *** *** *** 2010-06-10 00:00:00.000 25000.00 NULL NULL NULL NULL NULL NULL 25000.00
CK09 *** *** *** 2010-06-17 00:00:00.000 -3500.00 NULL NULL NULL NULL NULL NULL -3500.00
CK10 *** *** *** 2010-12-30 00:00:00.000 7500.00 NULL NULL NULL NULL NULL 7500.00 NULL
CK10 *** *** *** 2010-12-21 00:00:00.000 -13200.00 NULL NULL NULL NULL NULL -13200.00 NULL
CK10 *** *** *** 2010-11-15 00:00:00.000 13200.00 NULL NULL NULL NULL NULL 13200.00 NULL
CK10 *** *** *** 2010-12-17 00:00:00.000 8000.00 NULL NULL NULL NULL NULL 8000.00 NULL
CK10 *** *** *** 2010-10-08 00:00:00.000 -10200.00 NULL NULL NULL NULL NULL -10200.00 NULL
CK10 *** *** *** 2010-12-15 00:00:00.000 7500.00 NULL NULL NULL NULL NULL 7500.00 NULL
CK10 *** *** *** 2010-09-23 00:00:00.000 10200.00 NULL NULL NULL NULL NULL NULL 10200.00
CK10 *** *** *** 2010-12-31 00:00:00.000 -15500.00 NULL NULL NULL NULL NULL -15500.00 NULL

When CR runs this it will display only CK10 because the sum of the balance is positive but not the others because they are negative.

Now the thing is I want to do is get the sum for the displayed summaries (i.e those whose group total are positive).

I hope someone is understanding my situation. I'd give more details on demand.

Thanks

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[lbass]

Go to report->selection formula->GROUP and enter:

sum({table.amt},{table.customer_no}) > 0

Then insert a running total in the report footer that is set up to do a sum of {table.amt}, evaluate for each record, reset never. Only the displayed results will contribute to the calculation.

-LB

 

[infinitizon]

Oh thanks, you know I actually did this earlier and it did not work. I however did it again now and it worked.

I guess my computer was just tired.

Thanks lbass, you've always been right on time.

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.