AnsweredAssumed Answered

omit sub summary line items if below a threshhold value?

Question asked by waldotippy on Mar 21, 2012
Latest reply on Mar 22, 2012 by waldotippy

Like • Show 0 Likes0
Comment • 6

Title

Post

i'm still getting acquainted with sub summary reports.

i was wondering if it is possible to exclude items from the report if the sub summary is below a certain vaule.
basically i wish to not show items with a sub summary count of less than 2.

i thought i could do this with a search but sub summary fields do not seem to be searchable.

i arrive at the sub summary by executing a script which searches my records for a date range and then send the user to the sub summary layout.

philmodjunk Mar 21, 2012 1:44 PM

Correct Answer

Summary field values do not refer to a specific record (they refer to a group) and their value changes as the found set changes so that keeps them from being a searchable field.

By defining a field that uses GetSummary to return the same subtotal as your sub summary parts, you could set up a script that uses omit record to loop through your found set and omit all records where this calculation field returns 2 or smaller.

See the reply in context

No one else had this question

Outcomes

philmodjunk Mar 21, 2012 1:44 PM

Summary field values do not refer to a specific record (they refer to a group) and their value changes as the found set changes so that keeps them from being a searchable field.

Actions

waldotippy Mar 22, 2012 12:16 PM

got it.

thank you.

my script is this, and i have to run it twice to get rid of any straglers (one at the end):

Go to Record/Request/Page [ Last ]

Loop
#
If [ JOB::s_Job_Count getSUBsum < 3 ]
Omit Record
End If
Go to Record/Request/Page
[ Previous; Exit after last ]
#
End Loop
#
Exit Script [ ]

i was going to the FIRST record but i had to run it five times to get rid of all the "< 3" records.

i'm not too inconvenienced by this, but if anyone knows what i'm doing wrong this time, insight would be appreciated.

Actions

philmodjunk Mar 22, 2012 12:19 PM

How did you define s_Job_Count getSUBsum?

Omit record moves you to the next record automatically. Try this approach:

If [ JOB::s_Job_Count getSUBsum < 3 ]
Omit Record
ELSE
Go to Record/Request/Page [ Previous; Exit after last ]
End If

That should work, but don't see why working from first to last wouldn't also work.

Actions

waldotippy Mar 22, 2012 12:29 PM

s_Job_Count getSUBsum is an unstored calculation field. i'm generally reluctant to use those because of ptoential speed issues, but i did anyway.

unstored from JOB, = Getsummary (s_Job_Count; Title )

Actions

philmodjunk Mar 22, 2012 1:28 PM

What I hoped, starting the field name with s_, the naming convention for a field of type summary had me concerned.

This script should work for you and only need to be run once:

Sort[Restore; no dialog ] // sort by Title field
Go to record/Request/Page [first]
Loop
   If [ s_Job_Count getSUBsum < 3]
       Omit Record
   Else
      Go to Record/Request/Page [next ; exit after last]
   End If
End Loop

Actions

waldotippy Mar 22, 2012 1:35 PM

oooh, right.
i sorta ignored my own naming convention!

in my mind it was still a summary, but it is not a summary field as the s_ should denote based on my naming conventions.

Actions

6 Replies

philmodjunk Mar 21, 2012 1:44 PM
Unmark Correct
Correct Answer
Summary field values do not refer to a specific record (they refer to a group) and their value changes as the found set changes so that keeps them from being a searchable field.

By defining a field that uses GetSummary to return the same subtotal as your sub summary parts, you could set up a script that uses omit record to loop through your found set and omit all records where this calculation field returns 2 or smaller.
Like • Show 0 Likes0
Actions
waldotippy Mar 22, 2012 12:16 PM
Mark Correct
Correct Answer
got it.

thank you.

my script is this, and i have to run it twice to get rid of any straglers (one at the end):

Go to Record/Request/Page [ Last ]

Loop
#
If [ JOB::s_Job_Count getSUBsum < 3 ]
Omit Record
End If
Go to Record/Request/Page
[ Previous; Exit after last ]
#
End Loop
#
Exit Script [ ]

i was going to the FIRST record but i had to run it five times to get rid of all the "< 3" records.

i'm not too inconvenienced by this, but if anyone knows what i'm doing wrong this time, insight would be appreciated.
Like • Show 0 Likes0
Actions
philmodjunk Mar 22, 2012 12:19 PM
Mark Correct
Correct Answer
How did you define s_Job_Count getSUBsum?

Omit record moves you to the next record automatically. Try this approach:

If [ JOB::s_Job_Count getSUBsum < 3 ]
Omit Record
ELSE
Go to Record/Request/Page [ Previous; Exit after last ]
End If

That should work, but don't see why working from first to last wouldn't also work.
Like • Show 0 Likes0
Actions
waldotippy Mar 22, 2012 12:29 PM
Mark Correct
Correct Answer
s_Job_Count getSUBsum is an unstored calculation field. i'm generally reluctant to use those because of ptoential speed issues, but i did anyway.

unstored from JOB, = Getsummary (s_Job_Count; Title )
Like • Show 0 Likes0
Actions
philmodjunk Mar 22, 2012 1:28 PM
Mark Correct
Correct Answer
What I hoped, starting the field name with s_, the naming convention for a field of type summary had me concerned.

This script should work for you and only need to be run once:

Sort[Restore; no dialog ] // sort by Title field
Go to record/Request/Page [first]
Loop
   If [ s_Job_Count getSUBsum < 3]
       Omit Record
   Else
      Go to Record/Request/Page [next ; exit after last]
   End If
End Loop
Like • Show 0 Likes0
Actions
waldotippy Mar 22, 2012 1:35 PM
Mark Correct
Correct Answer
oooh, right.
i sorta ignored my own naming convention!

in my mind it was still a summary, but it is not a summary field as the s_ should denote based on my naming conventions.
Like • Show 0 Likes0
Actions

omit sub summary line items if below a threshhold value?

Title

Post

Outcomes

Related Content

Recommended Content