San Mateo Staff Meeting, Nov 28, 2007

Atomic Context:

The Key to Flexible Sub Summary Reporting Jeremiah Small

Atomic?!

• •

(more later) But first…

A Simple Example

A Simple Example

No, seriously.

It’s not as big a mess as it looks…

A Simple Example Requirements: 1. Report on various tables (at least 3) 2. Extract totals from 2 date ranges

– Actual range as defined by user – Same range in the previous year

3. Show the % change between ranges and the % of the total for each range

Report: Rep Sales Totals

Report: Products by Class

Report: Products by Class then Rep

Report: Business Types by Territory

Report: Locations by Type then Rep

Report: Locations by Rep

Report: Products by Location

Report: New Locations by Territory

An Example Solution

So what about atomic context?!

•

Oxford American Dictionary

a-tom-ic

: of or forming a single irreducible unit or component in a larger system.

•

Disambiguation

NOT used as in the same sense as an success or failure.

atomic operation

in computer science which refers to a set of operations that can be combined so that they appear to the rest of the system to be a single operation with only two possible outcomes:

So have I been misleading you?

What is the atomic context here?

These all run from Invoice Line context

But how can that be?

1. We don’t see Invoice Line items in reports 2. We need to extract 2 date ranges

– Actual range as defined by user – Same range in the previous year

3. We need to compare values between ranges

1. No rule that you must display body

1. No rule that you must display body

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

2. Conditional atomic values

So conditional atomic values let us do this…

3. Doing math on Summary values

…but what about this?

3. Doing math on Summary values

3. Doing math on Summary values

3. Doing math on Summary values

What’s the catch?

The break field in a GetSummary function…

MUST BE LOCAL

3. Doing math on Summary values

3. Doing math on Summary values

The break field in a GetSummary function must be local?!

…bummer :-( …but wait! Nobody said it can’t be a calc field!

The trick?

Bring all your break fields to the atomic context as calc fields.

3. Doing math on Summary values

Bring all your break fields to the atomic context as calc fields.

Bonus: You can add some logic to your break fields.

3. Doing math on Summary values

Note: You can sort by the same summary field

or a constant

to get Grand Summary values.

3. Doing math on Summary values

GetSummary () with localized break fields lets us do this

$50,826.32

÷

$1,546,456.07 = 3%

3. Doing math on Summary values

GetSummary () with localized break fields lets us do this Let ( [ cur = GetSummary( _il_cur_AVG_price_sm ; _ct_RepName ) ; pre = GetSummary( _il_pre_AVG_price_sm ; _ct_RepName ) ]; Case ( cur ≤ 0 ; "" ; pre ≤ 0 ; "" ; cur > pre ; ( cur / pre - 1) ; pre > cur; ( pre / cur - 1 ) * -1 ; 0 )//endCase )//endLet

3. Doing math on Summary values

GetSummary () with localized break fields lets us do this ($249,320.79 ÷ 391% $50,826.32 -1 ) x -1 = -

…but it’s not just for FileMaker

Sub-summarize records on a Web page First record

…but it’s not just for FileMaker

Sub-summarize records on a Web page First group

PHP Sub Summary

Do one query, not seven

PHP Sub Summary

Do one query, not seven

PHP Sub Summary

Do one query, not seven

PHP Sub Summary

Do one query, not seven

PHP Sub Summary

Remember the break field for Sub Summaries?

PHP Sub Summary

Remember the break field for Sub Summaries?

PHP Sub Summary

With records grouped by a break field, nested loops can print them to the screen just like FileMaker sub summaries

PHP Sub Summary

Sub-summarize records on a Web page 1st group First record Third record Second group And so on…

Concept Review

• •

Atomic Context

– The table that represents an irreducible unit or component in the larger system.

– If you find yourself considering complicated algorithms to extract report data from child records, question your context.

Sub Summary

– A field (column) representing a de-normalized (non-unique) attribute when treated as a group (break field) for all the records (rows) sharing that attribute.

– In FileMaker this is achieved by sorting.

– In PHP this is achieved by appending records into associative arrays named as the break field values.