Hi everyone, I need to sum the num 1

[Jacque]

Hi everyone,
I need to sum the num of locations for each successful mission per name. Seems easy enough but I'm missing something. I need data from two other tables which are joined with left outer joins. Instead of getting the sum for num of locations for each distinct missionID, I get the sum for num of locations for each instance of missionID.

Here is what I see from my sql query:

Date(1st grp) name(2nd) missionID locs success
09/12/02 Roy 091202001 2 1
09/12/02 Roy 091202001 2 1
09/12/02 Roy 091202002 1 1
09/12/02 Roy 091202003 4 1
09/12/02 Ruth 091202004 1 0
09/12/02 Ruth 091202005 3 1

This is what I'm getting in my report:
09/12/02 Roy 9
09/12/02 Ruth 3

This is what I'm supposed to get:
09/12/02 Roy 7
09/12/02 Ruth 3

I used a running total, summing on the num of locations, evaluating on mission success = 1 and reseting on change of group. I know I need to add an additional qualifier of distinct missionID but I'm blanking on how to do it. I'd prefer to my summing with variables due to past issues with running totals in our apps but I seem to be suffering from brain fade today and fighting a losing battle.

Thanks for all your help,
Jacque

 

[SmpTraining]

Try adding a third group on mission ID and displaying all the info at group 3 level (suppressing the details section altogether). You can then get the running total to evaluate at the group 3 level only (you will still need the check for success = 1).

This will only work however, if every repeating line for a given mission has the same 'locs' number. So, if the following is possible (lines marked *), we will need to get another solution

Date(1st grp) name(2nd) missionID locs success
09/12/02 Roy 091202001 2 1
09/12/02 Roy 091202001 2 1
*09/12/02 Roy 091202001 3 1
*09/12/02 Roy 091202001 3 1
09/12/02 Roy 091202002 1 1
09/12/02 Roy 091202003 4 1
09/12/02 Ruth 091202004 1 0
09/12/02 Ruth 091202005 3 1
Steve Phillips, Crystal Consultant

 

[Naith]

Combine

Not OnPreviousRecord

with

If {MissionID} <> Previous({MissionID})

in your RT. (Or Not OnLastRecord with the Next function).

Or use variables:

WhilePrintingRecords;
NumberVar Missions;

Not OnLastRecord;
If {MissionID} <> Next({MissionID})
Then 
	If {Success} = 1
	Then Missions := Missions + {locs}
	Else Missions
Else Missions;

with a reset of Missions in the relevant group header. I don't know what your previous issues with variables have been in the past, but this should be fine.

Nice emoticon,

Naith

 

[Jacque]

Steve, Naith,
Many Thanks!

Steve, luckily the num of locations will not change but my developers would prefer that I did not add another grouping.

Naith , the RT worked like a charm. Our issue with RTs is due to the way we allow users flexibility in grouping, sorting and filtering in our ap. But this is one of the rare reports that doesn't allow the user to reset the groups.

The people are what make this forum the best!
Thanks again,
Jacque