[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] on break help
- Subject: RE: [sqr-users] on break help
- From: "Bencke, Gina" <GinaBencke@forestcity.net>
- Date: Tue, 20 Jul 2004 11:27:49 -0400
- Delivery-date: Tue, 20 Jul 2004 12:21:45 -0500
- List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
- Thread-index: AcRt0DKtmj127BJJS62nuHI/0q3TfAAlDOqg
- Thread-topic: [sqr-users] on break help
There are some things about on-break which you do not seem to
understand.
First, recognizing that on-break is part of the print command, the
statements will be executed in the order that they are positioned
physically in your select loop. Programmers often think since they have
identified the LEVELS appropriately it should not matter what order the
place the statements in the loop. This is not true.
begin-select
...
TO_CHAR(SF_ITM_POST_DT, 'MM/DD/YY') &SF_ITM_POST_DT () on-break
print=never level=1 after=Datetotal
PSE_OPR_ID () on-break print=never level=2 after=IDtotal
BURSAR_ITM_TYP_NM () on-break print=never level=3
after=subtotal
BURSAR_ITM_TYP_SHRT_DESC
sum(BURSAR_ITM_LN_AMT) &BURSAR_ITM_LN_AMT
...
end-select
Next, make sure your ORDER BY clause lists the level 1 break column
first, level 2 second, etc. Note level 1 is the highest level break.
In your case I would recommend using positional ordering since your
highest level break is a function.
ORDER BY 1, PSE_OPR_ID, BURSAR_ITM_TYP_NM
I do not think you need to save the old values of your variables,
instead use print=change/top-page to control the printing of the field
values. If for some reason you do in fact need the old value of
variable, use the save variables to hold the old value of the select
field during the execution of your after procedures. SQR will maintain
the old value of a select column in a save variable that can then be
used in the after procedure. Once the after procedure executes, SQR
sets the value of the save variable to the new column value so the
$oldpid and &PSE_OPR_ID will have the same value after the AFTER
procedure executes. Note the second level 3 break, I am assuming that
BURSAR_ITM_TYP_NM and BURSAR_ITM_TYP_SHRT_DESC break simultaneously.
begin-select
...
TO_CHAR(SF_ITM_POST_DT, 'MM/DD/YY') &SF_ITM_POST_DT (+1,...) on-break
print=never level=1 after=Datetotal
save=$olddt
PSE_OPR_ID (0,...) on-break print=change/top-page level=2
after=Idtotal save=$oldpid
BURSAR_ITM_TYP_NM (0,...) on-break print=change/top-page
level=3 after=subtotal save=$oldnm
BURSAR_ITM_TYP_SHRT_DESC (0,...) on-break print=change/top-page level=3
save=$olddes
sum(BURSAR_ITM_LN_AMT) &BURSAR_ITM_LN_AMT
...
end-select
Your if statement is doing the work that should be left for your break
statements and SAVE variables. I would recommend using three subtotal
variables and three Counter variables. In your select paragraph include
lines similar to those given below. Then in your after procedure, print
the value of the appropriate variables and then move a 0 to reset them
for the next grouping.
Begin-select
...
add &BURSAR_ITM_LN_AMT to #Datesubtotal
add &BURSAR_ITM_LN_AMT to #idsubtotal
add &BURSAR_ITM_LN_AMT to #NMsubotal
add 1 to #DateCounter
add 1 to #IDCounter
add 1 to #NMCounter
...
End-select
!-----------------------------------------------------
BEGIN-PROCEDURE SUBTOTAL
!-----------------------------------------------------
print 'Total for Name: ' (+2,...)
print #Nmsubotal (0,...)
move 0 to #Nmsubotal
move 0 to #NmCounter
END-PROCEDURE SUBTOTAL
After procedures call each other in descending order. For Example lets
say your code breaks at the highest level (&SF_ITM_POST_DT), the after
procedures will be called in the following order
subTotal, IDTotal, DateTotal. Then SQR will clear the lower level
breaks so that these procedures are not called again when there print
statements are executed.
Now your select paragraph ought to look like this:
begin-select
TO_CHAR(SF_ITM_POST_DT, 'MM/DD/YY') &SF_ITM_POST_DT (+1,...) on-break
print=change/top-page level=1 after=Datetotal save=$olddt
PSE_OPR_ID (0,...) on-break print=change/top-page level=2
after=Idtotal save=$oldpid
BURSAR_ITM_TYP_NM (0,...) on-break print=change/top-page
level=3 after=subtotal save=$oldnm
BURSAR_ITM_TYP_SHRT_DESC (0,...) on-break print=change/top-page level=3
save=$olddes
sum(BURSAR_ITM_LN_AMT) &BURSAR_ITM_LN_AMT
add &BURSAR_ITM_LN_AMT to #Datesubtotal
add &BURSAR_ITM_LN_AMT to #idsubtotal
add &BURSAR_ITM_LN_AMT to #NMsubotal
add 1 to #DateCounter
add 1 to #IDCounter
add 1 to #NMCounter
end-select
While processing the SELECT paragraph, when a break occurs on any
column, events occur in the following order:
1. AFTER procedures are processed in descending order from highest
LEVEL to the lowest LEVEL of the current ON-BREAK column.
2. SAVE variables are set with the new value.
3. BEFORE procedures are processed in ascending from lowest LEVEL
to the highest LEVEL of the current ON-BREAK column.
4. Any breaks with the same of higher LEVEL numbers are cleared so
they will not break on the next value
5. The current line position is advanced if SKIPLINES is used
6. The value is PRINTed, unless PRINT=NEVER
To illustrate this, let's look at what happens when &SF_ITM_POST_DT
breaks.
1. After procedures are processed (in descending order)
subtotal (Level 3)
Idtotal (Level 2)
Datetotal (Level 1)
During the processing of the after procedures the save variable, $olddt,
has value of the previous column variable.
2. Save variables are given new values
The value of &SF_ITM_POST_DT is moved to the save variable $olddt.
3. Before procedures are processed (in ascending order)
None are defined
4. Clear higher level breaks
The breaks on PSE_OPR_ID, BURSAR_ITM_TYP_NM, and
BURSAR_ITM_TYP_SHRT_DESC are cleared.
This ensures that the before and after procedures, Idtotal and subtotal
will not be processed again when the print statement associated with
PSE_OPR_ID and BURSAR_ITM_TYP_NM is executed.
5. n/a
Skiplines was not used.
6. Print - or not
Since print=change/top-page is used, the value of &SF_ITM_POST_DT is
printed.
Hope this Helps!
Regards,
Gina Bencke
FCE Office (216) 416-3492
Home Office (440) 519-0060
email: gina.work@bencke.com
-----Original Message-----
From: sqr-users-bounces+ginabencke=forestcity.net@sqrug.org
[mailto:sqr-users-bounces+ginabencke=forestcity.net@sqrug.org] On Behalf
Of Pan, Zhen
Sent: Monday, July 19, 2004 4:37 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] on break help
Hi all
I am having problem with on break.
I was trying to print out some entries and group by operID
and Date. Here is the result I have(partial)
06/02/04 SCOTE 130160000000 Nr Grad 1
$ 7,435.20
06/02/04 SCOTE 191200000000 Activ Fee 1
$ 7,497.80
total for operator: 2
7,497.80
total for date: 2
7,497.80
for everything else it is correct except the total for
operator and total for the date. If within the same date and sme operID
there are more than one entries, the first one will be ignored. (Except
the very first record, the first entry will be counted)
The following is the code and I aslo prined each time when
'subtotal' is called. I found it is not called by when the item changes.
Is that a bug or anything I did was wrong? Thanks!
begin-procedure SelectData
LET #SUBTOTAL=0
LET #COUNTER=0
LET $OLDpID=''
let $oldnm=''
let $olddes=''
let $olddt=''
let #first=1
BEGIN-SELECT
BURSAR_ITM_TYP_NM () on-break print=never level=3
after=subtotal
BURSAR_ITM_TYP_SHRT_DESC
TO_CHAR(SF_ITM_POST_DT, 'MM/DD/YY') &SF_ITM_POST_DT () on-break
print=never level=1 after=Datetotal
sum(BURSAR_ITM_LN_AMT) &BURSAR_ITM_LN_AMT
PSE_OPR_ID () on-break print=never level=2 after=IDtotal
LET #BURSAR_ITM_LN_AMT=&BURSAR_ITM_LN_AMT
if ($oldnm<>&BURSAR_ITM_TYP_NM or $oldpid<>&PSE_OPR_ID or
$olddt<>&SF_ITM_POST_DT )
let $oldnm =&BURSAR_ITM_TYP_NM
let $oldpid =&PSE_OPR_ID
let $olddes =&BURSAR_ITM_TYP_SHRT_DESC
let $olddt =&SF_ITM_POST_DT
let #subtotal=&BURSAR_ITM_LN_AMT+#subtotal
if #first=1
let #counter=#counter+1 !paly tricks with the counters of
the very first record
end-if
Do ReportLine
if #first=1
let #counter=#counter - 1
end-if !paly tricks with the counters of the very first
record
let #first=0
else
if ($oldnm=&BURSAR_ITM_TYP_NM and $oldpid=&PSE_OPR_ID and
$olddt<>&SF_ITM_POST_DT)
let #counter=#counter+1
let #subtotal=&BURSAR_ITM_LN_AMT+#subtotal
end-if
end-if
FROM DSS_RDS.SF_ALL_ITM_GT
[$where] and rownum<1790
group by BURSAR_BSNS_UNIT_CD,BURSAR_ITM_TYP_NM,BURSAR_ITM_TYP_SHRT_DESC,
SF_ITM_POST_DT,PSE_OPR_ID
order BY SF_ITM_POST_DT, PSE_OPR_ID, BURSAR_ITM_TYP_NM
!
!
!
!
! Do Data
!
!
!
!WHERE 1=0
![$and]
!
END-SELECT
end-procedure
-----------------------------------------------------
BEGIN-PROCEDURE SUBTOTAL
!-----------------------------------------------------
let #idcounter=#counter+#idcounter
let #idsubtotal=#subtotal+#idsubtotal
show 'subtotal' #subtotal
show 'idsubtotal' #idsubtotal
LET #SUBTOTAL=0
LET #COUNTER=1
END-PROCEDURE SUBTOTAL
!-----------------------------------------------------
BEGIN-PROCEDURE IDTOTAL
!-----------------------------------------------------
show 'idsubtotal' #idsubtotal
print 'total for operator: ' (+1, 25)
print #idcounter (,60) edit 999
print #idsubtotal (,75) edit 9,999,999,999.00
let #datecounter=#datecounter+#idcounter
let #datetotal=#datetotal+#idsubtotal
let #idcounter=0
let #idsubtotal=0
END-PROCEDURE IDTOTAL
!-----------------------------------------------------
BEGIN-PROCEDURE DATETOTAL
!-----------------------------------------------------
show 'datesubtotal' #datesubtotal!let
#dateconunter=#datecounter+#idcounter
print 'total for date: ' (+1, 25)
print #datecounter (,60) edit 999
print #datetotal (,75) edit 9,999,999,999.00
let #datecounter=0
let #datetotal=0
END-PROCEDURE DATETOTAL
Zhen Pan
University Information Technology Services
HRMS/SIS Team
work (812)856-4566
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users