Selecting Records to Grouping Formula

[augsburg]

Hi all,

I am using CR2008 and DB is in SQL Server 2008.

Perhaps this is just impossible, but I am trying to find solution to dilemma, where I would need to select certain records and group and calculate values based to group.

Records
{leg_ID},{date},{person},{time}

leading to

1,1.4.2012,Andy,1.5
1,1.4.2012,Bill,1.5
1,1.4.2012,Leon,1.5
1,1.4.2012,John,1.5

2,1.4.2012,Andy,3.0
2,1.4.2012,Mike,3.0

3,1.4.2012,Leon,5.0
3,1.4.2012,Rick,5.0

1,2.4.2012,Mike,1.5
1,2.4.2012,Olav,1.5
1,2.4.2012,Anke,1.5



Now I would need to find totals to:
1. When Andrew is without Mike as 'person' (rest 'person'-records on that Leg_ID can be whatever but not Mike)
2. When Mike is without Andy as 'person' (rest 'person'-records on that Leg_ID can be whatever but not Andrew)
3. When Mike and Andy are 'person' on this leg_ID (rest 'person'-records can be whatever)
4. When no Mike and neither Andy are 'person', and on the leg_ID 'person'-records are whatever

Outcome should upon example be
1. Group "Andy" total time = 1.5
2. Group "Mike" total time = 1.5
3. Group "Andy&Mike" total time = 3.0
4. Group "Other" total time = 5.0

I have tried to arrays, selecting cases, and with that make formula (@pax} and then group based on that. I can get two groups, where cases 1. and 2. are somewhat well. But this simply does not work, and it puts 'person'-records to many groups. And needless to say, time calculations go wrong, when I total groups. It gives {time} as many times as it finds 'person'-records.

Any help or is this mission impossible? Or should I make something totally different to catch the outcome right?

I am newbie in CR, sorry folks.

Rgds

augsburg

 

[MCuthill]

augsburg,

The specificity of your 4 cases looks kind of like something that might be on an assignment or in a textbook, which is not the intent for this forum. That being said... I will assume it's not and provide some very high level ideas for you to test out.

I am thinking you ultimately need a series RunningTotal's which calculate based on vaious conditions. Due to the layout of the data you have provided, you may need addition Summary fields to help in the conditional part, but I think it should be doable. I am assuming that your criteria is based on a "leg" and "date" combination of whatever you are measuring? Assuming this, I would suggest a formula for each criteria that looks something like:
{@COUNTER_Mike}

IF {Name} = "Mike" THEN 1 ELSE 0

This would then be summed to the group level needed.

Another set will be needed where the "1" is replaced with your {Table.Time} field, and Summed to the same group. For example purposes, lets say the field is {@COUNTER_MikesTime}

Then you will need 3x formula fields for each scenario, a "Reset" at the top to set variables to zero, an "Accumulation" formula to conditionally add times as needed and a "display" formula for the end result.

Very high level, but "Mike Only" might look something like:
{@RESET_MikeTotalTime}

WhilePrintingRecords;
Shared NumberVar MikeTotalTime:=0;

{@ACCUM_MikeTotalTime}

WhilePrintingRecords;
Shared NumberVar MikeTotalTime;
IF SUM({@COUNTER_Mike}, {Some Group}) > 0 AND SUM({@COUNTER_Alex}, {Some Group}) = 0 THEN MikeTotalTime:=MikeTotalTime + SUM({@COUNTER_MikesTime}, {Some Group});

For results you just need something like:
{@DISPLAY_MikeTotalTime}

WhilePrintingRecords;
Shared NumberVar MikeTotalTime;

I am unable to mock up a complete test for you, so this may need some fine tuning but I feel the theory is sound and "should" work. That all being said, it is based on a couple assumptions for the data and report structure.

So to answer your primary question, I don't think it is "mission impossible", but it certainly isn't as straight forward as it could be (again, based on how presented).

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."

 

[augsburg]

Hi MCuthill,

after few hours of making formulas, this approach to use variables worked out.

Thanks MCuthill!

Kind regards

augsburg

 

[MCuthill]

Excellent!
I was wondering last night how you made out with this one, but didn't make it to a computer to see.

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."