[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

[sqr-users] Column Report that skip a first group occurence.

Subject: [sqr-users] Column Report that skip a first group occurence.
From: "Carlos Loperena Matias" <cloperena@elnuevodia.com>
Date: Tue, 9 Dec 2003 15:22:57 -0400
List-help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-id: This list is for discussion about the SQR database reporting language from Hyperion. <sqr-users.sqrug.org>
List-post: <mailto:sqr-users@sqrug.org>
List-subscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=subscribe>
List-unsubscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=unsubscribe>
Thread-index: AcO+idpI+GBp8kpNTKu+BGL7wIeI4A==
Thread-topic: Column Report that skip a first group occurence.

Hi group, I  have a report printing in column format, and it print perfect but 
skip the first value of  my second break. In the column of the counter sales I 
should have a last value of  0,0,0. The values for that column are shifted up 
on line.

 I thanks in advance for any help.

Here is my code and my output.

!
Begin-Setup

        Ask database 'What Database? ' 
        Use {database}
!    use CIRCTEST
        Ask begindate 'Begin Date mm/dd/yyyy '
        Ask enddate 'End Date mm/dd/yyyy '
    Ask Abczone 'Enter ABC Main Zone Ex: CT - For City Zone, RTZ - Retail Zone'
 
 declare-variable
 text $lv_company_name
 text $lv_system
 text $lv
 text $lv_hour
 text $lv_date
 text $lv_page_desc_number
 text $lv_report_name
 end-declare

 Declare-Printer HP
  type =  hplaserjet
  Point-Size=7
  Font=5
 End-Declare

 Declare-Layout def_layout 
  paper-size= (14,8.5)
  orientation= landscape
!  Orientation = Landscape
!  Paper-Size = (Legal)
  Top-Margin = 0.50
  Bottom-Margin = 0.50
  Left-Margin = 0.50
  Right-Margin = 0.50
  Char-Width = 7.0

 End-Declare

 Declare-Report def_report
  layout = def_layout
  printer-type = hplaserjet
 End-Declare

BEGIN-SQL
!Driver Table Con Draw Id y Dias De Servicio
create table tempdb..driver_table 
(draw_id int not null,
dos_id smallint not null,
dtos_id datetime not null,
prod_cd char(6) not null)
end-sql

BEGIN-SQL
!Create Table With Draw,Ret
create table tempdb..draw_ret
(abc_zone char(30) default '',
special_bdry_type_cd char(6) not null,
bdry_short_nm char(6) not null,
draw_id int not null,
dos_id smallint not null,
dtos_id datetime not null,
draw_amt int default 0,
ret_amt int default 0)
end-sql

!create Table With Draw,Ret,Net
BEGIN-SQL
create table tempdb..draw_ret_net
(abc_zone char(30) default '',
special_bdry_type_cd char(6) not null,
bdry_short_nm char(6) not null,
draw_id int not null,
dos_id smallint not null,
dtos_id datetime not null,
draw_amt int default 0,
ret_amt int default 0,
net_amt int default 0)
end-sql


! changing values here will change where columns print on report
!define column POSITION variables

#define  col1  1

#define  col2  06
#define  col3  10
#define  col4  14

#define  col5  19
#define  col6  23
#define  col7  27

#define  col8  32
#define  col9  36
#define  col10 40 

#define  col11 45
#define  col12 49 
#define  col13 53 

#define  col14 58  
#define  col15 62 
#define  col16 66 

#define  col17 71  
#define  col18 75 
#define  col19 79 

#define  col20 84 
#define  col21 88 
#define  col22 92 

#define  col23 97 
#define  col24 103 
#define  col25 105 

#define  col26 108 
#define  col27 112 
#define  col28 115 
End-Setup


Begin-Heading 8
 let $lv_company = &dbv_newspaper_name
 let $lv_hour = 'Time: ' || edit(datenow(),'HH:MI PM')
 let $lv_system = 'CIRC2000'
 let $lv_report_name = 'Report: ' || 'abcrecbook.sqr'
 let $lv_report_title = 'ABC Record Book'
 print $lv_report_name (2,1) 
 Print $lv_company  (2,40) Bold !Center
 Print $lv_hour (,100)
 print $lv_system (3,100)
 print $lv_report_title (3,40) bold
 print 'From: ' (4,33) bold
 print $begindate (4,38) edit 'MM/DD/YYYY' bold
 print 'To: ' (4,46) bold
 print $enddate (4,49) edit 'MM/DD/YYYY' bold
 page-number (4,100) 'Page No: '
 print '='(5,1,210) fill

 print 'HOME DELIVERY'  (7,8) BOLD
 print 'DEALERS'        (7,23) BOLD
 print 'HAWKERS'        (7,36) BOLD
 print 'NIE'            (7,52) BOLD
 print 'EMPLOYEES'      (7,61) BOLD
 print 'COUNTER SALES'  (7,72) BOLD
! print 'BULK SALES'     (7,86) BOLD
 print 'MAIL SUBSC'     (7,100) BOLD


 Print 'Date'       (8,1) BOLD UNDERLINE

 Print 'Draw'       (,6)  BOLD UNDERLINE
 Print 'Return'     (,10)   BOLD UNDERLINE
 Print 'Net'        (,14)  BOLD UNDERLINE

 Print 'Draw'       (,19)  BOLD UNDERLINE
 Print 'Return'     (,23)   BOLD UNDERLINE
 Print 'Net'        (,27)  BOLD UNDERLINE

 Print 'Draw'       (,32)  BOLD UNDERLINE
 Print 'Return'     (,36)   BOLD UNDERLINE
 Print 'Net'        (,40)  BOLD UNDERLINE

 Print 'Draw'       (,45)  BOLD UNDERLINE
 Print 'Return'     (,49)   BOLD UNDERLINE
 Print 'Net'        (,53)  BOLD UNDERLINE

 Print 'Draw'       (,58)  BOLD UNDERLINE
 Print 'Return'     (,62)   BOLD UNDERLINE
 Print 'Net'        (,66)  BOLD UNDERLINE

 Print 'Draw'       (,71)  BOLD UNDERLINE
 Print 'Return'     (,75)   BOLD UNDERLINE
 Print 'Net'        (,79)  BOLD UNDERLINE

 Print 'Draw'       (,84)  BOLD UNDERLINE
 Print 'Return'     (,88)   BOLD UNDERLINE
 Print 'Net'        (,92)  BOLD UNDERLINE

 Print 'Draw'       (,97)  BOLD UNDERLINE
 Print 'Return'     (,101)   BOLD UNDERLINE
 Print 'Net'        (,105)  BOLD UNDERLINE

! Print 'Draw'       (,110)  Bold
! Print 'Return'            (,114)   Bold
! Print 'Net'       (,118)  Bold

End-Heading

Begin-Report
    Do GetProductCode
        Do Main
!       Do Print_Total
End-report

Begin-Procedure Main

Begin-select
Convert(datetime,'{begindate}') &begindate
 move &begindate to $begindate
Convert(datetime,'{enddate}') &enddate
 move &enddate to $enddate
end-select

use-report def_report
let $prod_code = &dbv_newspaper
let #i = 0
let $col_pos = 'col' || to_char(#i)  
!display $col_pos

Begin-SQL
!Insertar Draw Id, Dias de Servicio Por Producto y Por Herarquia de ABC
insert into tempdb..driver_table
(draw_id,dos_id,dtos_id,prod_cd)
select distinct 
d.draw_id,
d.dos_id,
d.dtos_id,
d.prod_cd 
from top_level_structure_usage t, draw_est d
where t.structure_id in (7)
and d.del_mth_cd = t.del_mth_cd
and d.dos_id = t.dos_id 
and (d.dtos_id >= '{begindate}' and d.dtos_id <= '{enddate}')
and d.draw_est_type = 'DRAW'
and d.final_indc = '1'
and d.prod_cd = 'END'
End-SQL


Begin-SQL
!Seleccionar fecha de Servicio,Draw Id, Dia de Servicio, Metodo de Delivery   
insert into tempdb..draw_ret
(abc_zone,special_bdry_type_cd,bdry_short_nm,draw_id,dos_id,dtos_id,
draw_amt,ret_amt)
select 
h.bdry_nm,dh.special_bdry_type_cd,h.bdry_short_nm,dt.draw_id,dos_id,dtos_id,
dh.qty,0
from draw_hierarchy dh, hierarchy h, tempdb..driver_table dt
where dh.structure_id = 7 
and dh.bdry_id = h.bdry_id
and dh.draw_id = (dt.draw_id)
and dh.special_bdry_type_cd <> 'RET' 
and h.bdry_short_nm in 
('CZHDHD','CZHDEM','CZHDCT','CZHDML','CZSCDL','CZSCHK','CZSCSC','CZSCBK')
End-SQL

Begin-sql
insert into tempdb..draw_ret
(abc_zone,special_bdry_type_cd,bdry_short_nm,draw_id,dos_id,dtos_id,
draw_amt,ret_amt)
select 
h.bdry_nm,dh.special_bdry_type_cd,h.bdry_short_nm,dt.draw_id,dos_id,dtos_id,
0,dh.qty
from draw_hierarchy dh, hierarchy h, tempdb..driver_table dt
where dh.structure_id = 7 
and dh.bdry_id = h.bdry_id
and dh.draw_id = (dt.draw_id)
and dh.special_bdry_type_cd = 'RET' 
and h.bdry_short_nm in 
('CZHDHD','CZHDEM','CZHDCT','CZHDML','CZSCDL','CZSCHK','CZSCSC','CZSCBK')
End-sql
LET $old_abc_zone = 'CZSCBK'
begin-select
dtos_id (,1) on-break PRINT=CHANGE level=1 SKIPLINES=1 Edit 'mm/dd/yy' 
bdry_short_nm (,) on-break PRINT=NEVER  level=2   after=next_abc_zone 
save=$old_abc_zone
bdry_short_nm &abc_short_nm
sum(draw_amt) &dr 
sum(ret_amt)  &rt 
sum(draw_amt - ret_amt) &nt 
 let #i = #i + 1
from tempdb..draw_ret
group by dtos_id,bdry_short_nm,abc_zone
END-SELECT

End-Procedure

BEGIN-PROCEDURE next_abc_zone
let $col_pos = 'col' || to_char(#i)
IF &abc_short_nm = 'CZHDHD'
 print &dr (,{col2}) Edit '99,999'
 print &rt (,{col3}) Edit '9,999'
 print &nt (,{col4}) Edit '99,999'
ELSE 
  IF &abc_short_nm = 'CZSCDL' 
     print &dr (,{col5}) Edit '99,999'
     print &rt (,{col6}) Edit '9,999'
     print &nt (,{col7}) Edit '99,999'
  ELSE 
     IF &abc_short_nm = 'CZSCHK'
        print &dr (,{col8}) Edit '99999'
        print &rt (,{col9}) Edit '99999'
        print &nt (,{col10}) Edit '99999'
     ELSE 
        IF &abc_short_nm = 'CZSCSC'
           print &dr (,{col11}) Edit '99999'
           print &rt (,{col12}) Edit '99999'
           print &nt (,{col13}) Edit '99999'
        ELSE 
            IF &abc_short_nm = 'CZHDEM'
               print &dr (,{col14}) Edit '99999'
               print &rt (,{col15}) Edit '99999'
               print &nt (,{col16}) Edit '99999'
            ELSE 
                IF &abc_short_nm = 'CZHDCT'
                   print &dr (,{col17}) Edit '99999'
                   print &rt (,{col18}) Edit '9'
                   print &nt (,{col19}) Edit '99999'
                ELSE 
                   IF &abc_short_nm = 'CZSCBK'
                         print &dr (,{col20}) Edit '99999'
                         print &rt (,{col21}) Edit '999'
                         print &nt (,{col22}) Edit '99999'
                   ELSE 
                      IF &abc_short_nm = 'CZHDML'
                         print &dr (,{col23}) Edit '99999'
                         print &rt (,{col24}) Edit '9'
                         print &nt (,{col25}) Edit '99999'                     
                      END-IF
                   END-IF
                END-IF
            END-IF
        END-IF
     END-IF
  END-IF
END-IF
 LET #i = #i + 1
end-procedure


BEGIN-PROCEDURE GetProductCode

let $proc_nm = 'GetProductCode'

Begin-select !ON-ERROR=Error_Process
prod_cd &dbv_newspaper
convert(char(10),convert(datetime,GetDate()),101) &convdate
convert(varchar(30),prod_nm) &dbv_newspaper_name
from prod
end-select

end-procedure GetProductCode

 <<abcrecbook.pdf>> 


















Carlos A. Loperena Matías
El Nuevo Dia - Sistemas de Información 787-641-8000x2984
cloperena@elnuevodia.com

Here was a non admissible message part of 'application/octet-stream'
MIME type. It has been automatically discarded before sending the
message to the list.

Prev by Date: [sqr-users] System Commands - SQR Resource
Next by Date: [sqr-users] Command Line Arguments
Previous by thread: RE: [sqr-users] System Commands - SQR Resource
Next by thread: [sqr-users] Command Line Arguments
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page