Calcs on Subsummary Parts?

[Wickerman]

Okay, now I'm forgetting things I'm sure I learned years ago . . .

 

I have a simple Report layout based on a single table that hold records of gaming sessions (backgammon!).  Each record holds info on various results of the session (Games won, Games lost, Points one, points lost, etc.) and various calculations (% games won, average net points won or lost per game, etc.).

 

So I have a Report layout based on the table that sorts by Opponent and Date, with a trailing Subsummary that summarizes my aggregate results against each opponent, and then a Trailing Grand Summary that aggregates all the sessions.  

 

So, the Summary Fields I've placed on the two Summary parts are working as you'd expect, that's fine.

 

. . . but I'm having trouble getting aggregate figure that require more math than the simple summary field options.   I've tried using Calc fields that employ summary field data, but this isn't working of course (it always displays the results for all records, regardless what part it appears on). But I can't recall how to do this otherwise.  One simple example should serve to sort me out:

 

I've got:

NUMBER FIELDS:

Games_won

Games_Lost

 

CALC FIELDS:

Games_Ct. = Games_won   +  Games_Lost

Win_Pct = Games_Won  /  Games_Ct

 

SUMMARY FIELD:

Games_Ct_sum = Total of Games_Ct

Games_Won_sum = Total of Games_won

 

PROBLEM FIELD:

Games_WinPct_sum = Games_Won_sum  /  Games_Won_sum

 

 

On the Reporting layout, I have the following Columns:

 

Opponent     |     Date     |     Games_Ct     |     Win_Pct

 

So . . . The body fields all work fine, and the Summary Fields all work fine on both the subsummary and Grand summary parts . . . but the 'Problem Field' just shows the result appropriate for the Grand Summary in all the Subsummary parts. 

 

I'm not seeing how to use the options in the Summary specification box to achieve this simple 'Average Win %' figure.

 

What have I forgotten?

 

Thanks!

 

 

[JHaughey]

If I understand the issue correctly, I think you need to specify your calculation field in terms of the sort field your subsummary is based on. So you would still have a calc field that does this:

 

Games_WinPct_sum = Games_Won_sum  /  Games_Won_sum

 

But in addition, if you want a subsummary when sorted by, lets say a date field called "date", you would define an additional calculation as:

 

Games_WinPctByDate_sum =  GetSummary (Games_WinPct_sum ; date )

 

Then use that field in your subsummary.

[Wickerman]

Well, the subsummary breakfield is 'Opponent' -- I want to get my results broken down against each opponent, and then get the grand summary against all opponents at the very end.

 

I only sort by Date so that the individual session records are shopwn in order for each opponent.   

 

But maybe a GetSummary calc function is still the right idea . . . ?

[JHaughey]

I would go with something like:

 

Summary fields (as you have them):

Games_Ct_sum = Total of Games_Ct

Games_Won_sum = Total of Games_won

 

Then a calc field:

 

Games_WinPctByOpponent_sum = GetSummary(Games_Won_sum ; opponent)  / GetSummary(Games_Ct_sum ; opponent)

 

Use "Games_WinPctByOpponent_sum" in your subsummary part.

[Wickerman]

Ah -- I see -- that works fine, thanks.

 

Just to learn what I'm learning, is the idea that so long as the GetSummary() is the outermost function in the calc, it will behave like a Summary Field as far as subsummary parts go?

 

Next question, though -- what about the Trailing Grand summary?  I was expecting this new field to behave appropriately when I put it in that part, but it doesn't (It's showing the value for the last Opponent in the report).   My other simple Calc field for Games Won / Total Games doesn't work there either . . . 

 

Thanks for your help!

[JHaughey]

GetSummary() tells the summary field which records to summarize.  So "GetSummary(Games_Won_sum ; opponent)," summarizes records when sorted by the break field: opponent. You wouldn't use it in a grand summary, where you want to summarize all of the records.

 

 

Are you saying that you tried "Games_WinPct_sum" (as defined as Games_Won_sum  /  Games_Ct_sum) in the grand summary part and that did not work? What result did it show?

[Wickerman]

Ahhhhh . . . now I see -- got it.

 

Thanks so much for your generous attention!