Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to a do a sum of sum on Header level 1

Status
Not open for further replies.

vega83

Programmer
Jun 12, 2008
141
US
Friends I have data this way

Gh1
WHOLE total-----2
GH2
ZZ total--- 1
details
abc 1
abc 0
GH2
SS total---0
details
abc 0
abc 0
GH2
CO total---1
details
abc 1
abc 1

But in GH 1 when i do sum it summing up every detail and giving me value 3. Is there a formula I can use to display it as 2?
Thanks in advance,
Vega.
 
Well, what is the summary you are using at the GH2 level--it can't be a sum, since in your third instance, the summary = 1. Is it a distinctcount? A maximum?

-LB
 
I have a formula if date1 < date2 then 1 else 0 in details.
In header i used same formula too
 
LB every 1st group will have multiple groups 2 with different date . Basically i am trying to sum (total value of GH2). Thanks so much for your help on this
 
If you place that in the header, it will just return the result for the first record in the group. What is your intention?

-LB
 
that is right for each ticket it has only one data but each ticket has mutiple subtickets. I want to add up only GH2 vlaues in GH1.
 
I can do max(date1,gh2) > max(date2,gh2)then 1 else 0. But i cannot sum this again. If there is a turnaround for this it would be very helpful
 
I think you could concatenate your group #1 field ({table.ticket}?) with the date field in a formula like this:

{table.ticket}+" "+totext({table.date},"yyyy-MM-dd")

Then insert a distinctcount on this formula at the Group #1 level and drag the result into the group header.

-LB
 
LB, on which date field i need to do it? I have two data fields? calculation is if datefield 1 < datefield 2 then 1 else 0. I dont use current date. Sorry i did not mention earlier.
 
Sorry, I lost track of that. What is the purpose of this comparison? Are the two dates the same for each record within the Group #2?

-LB
 
one is date is ticket opned and other is ticket closed . If open date < close data then i calculate it as one.

GH2 has multiple GH3

data is liekt his


GROUP A---GH1----total 2

ticket 1 GH2 total 1
child tick1 1 ----detailas
child tick2 0


ticket 2 GH2 total---0
child tick1 0 ----detailas
child tick2 0

ticket 3 GH2 total 1
child tick1 1 ----detailas
child tick2 1

 
Okay, try this:

First create a formula {@null} by opening and saving a new formula without entering anything. Then create a formula:

if {table.opendate} < {table.closedate} then {table.ticketnumber} else
tonumber({@null}) //remove the tonumber if {table.ticketnumber} is a string

Place this in the detail section and insert a distinctcount on it at the Group #1 level. Drag the result into the group header.

-LB
 
LB details will have d1 and d2 of GH2

ig GH2 has date1(08-08-2009) date2(10-08-2009)
Details
Childticket 1
childtciket 2 will have same dates date1(08-08-2009) date2(10-08-2009)


So basically what ever i have summarized in GH2 should be again summaried on GH1
 
LB got it I use your formula and said
if {table.opendate} < {table.closedate} then GH2 field else
{null}. It works!...Thanks a ton!
 
LB any way to do average on avergae? I want to to do Average of GH2 on GH1
 
I have other field called no of days

All the child tickets will have same no of days value like below

GROUP A---GH1----total (40+20+30)/3(distinct GH2 field) = 30

ticket 1 GH2 total 40
child tick1 40 ----detailas
child tick2 40


ticket 2 GH2 total---20
child tick1 20 ----detailas
child tick2 20

ticket 3 GH2 total 30
child tick1 30 ----detailas
child tick2 30
 
Friends can this be done?, any help would be great
 
If you are planning a variety of calculations in the group #1 header, you are better off saving the report as a subreport and then inserting it in the group #1 header, linked on the group #1 header field. Then you can use running totals to the calculations.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top