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

[sqr-users] FW: Help with Arrays...

Subject: [sqr-users] FW: Help with Arrays...
From: "Deb Harris" <dharris@activeinterest.com>
Date: Fri, 30 May 2003 11:18:34 -0500
Importance: Normal
List-help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-id: This list is for discussion about the SQR database reporting language from Brio Software. <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>



I hope this is not to large for everyone.
I need to produce the following report..     I am having problems with
working with the arrays.    My code is following the sample..

 Any help would be greatly appreciated.

[Deb Harris]
Page 1....
Report Sorted and page breaks by Division
                                               Fiscal Year
                                       Territory
                                       Distributor
                                       Brand
                                       Tertile

There are 3 Tertiles...   Tertile 1 is May to August
                             Tertile 2 is Sept to Dec
                             Tertile 3 is Jan to April

Page 1 needs to link to Page 2 by the month






Begin-Setup
 Declare-Layout Default
  orientation=landscape
  left-margin= .05
  right-margin=.05
  top-margin=.05
  bottom-margin=.05
 End-Declare

 Declare-Variable
    integer #tertile_num
    integer #month_num
    integer #m
    integer #d
    integer #detail_cnt
    integer #month_cnt
 end-declare

 Declare-TOC Default
  Entry = BRB_TOC_Proc
 End-Declare

End-Setup

Begin-Heading 2 For-Tocs=(default)
 Print 'Table of Contents' (1,115,0)
   Print-Direct printer=html '%%TOC-Title Table of Contents'
 Alter-Printer Font=901 Point-Size=7
End-Heading

Begin-Procedure BRB_TOC_Proc
 Alter-Printer Font=901 Point-Size=7    ! [SQR.INI] 901=MS Shell
Dlg,proportional
 Let #indent-size = 4
 Let #indentation = 1 + (#indent-size * (#sqr-toc-level - 1))
 Print $sqr-toc-text (1,#indentation)
 Print #sqr-toc-page (1,70)
 Next-Listing Skiplines=0 Need=1
End-Procedure

Begin-Program
 Do make-arrays
 Do select-data
 Position (1,1)
 Do print-pagehdr
 do print-tertile1-os-data
End-Program

Begin-procedure make-arrays
LET #OS_ARRAY_ROWS = 0

!OTHER SPEND DETAIL ARRAY
  create-array
   name=Tertile1-other-spend-data size={max_recs}
   field=os-divname:char
   field=os-fiscalyear:char
   field=os-terrname:char
   field=os-distname:char
   field=os-brand:char
   field=os-tertile:char
   field=os-fiscalmonth:char
   field=os-monthname:char
   field=os-channel:char
   field=os-consum:char
   field=os-promo_type:char
   field=os-tactic:char:4

!MEDIA DETAIL ARRAY
  create-array
   name=Tertile1-media-data size={max_media_recs}
   field=media-divname:char
   field=media-fiscalyear:char
   field=media-terrname:char
   field=media-distname:char
   field=media-brand:char
   field=media-tertile:char
   field=media-month:char
   field=media-channel:char
   field=media-consum:char
   field=media-type:char
   field=media-description:char:4
end-procedure

Begin-Procedure select-data
   Print-Direct printer=html '%%ResetBorder'
   Alter-Printer Font=901 Point-Size=7
   move 1 to #firstpage
   move 0 to #m
   move 0 to #d

Begin-Select
DIVISION_NAME &DIVISION_NAME () On-Break Print=Never  level=1
Before=DIVISION_NAME_BeforeProc301
FISCAL_YEAR &FISCAL_YEAR () On-Break Print=Never level=2
Before=FISCAL_YEAR_BeforeProc302
TERRITORY_NAME &TERRITORY_NAME () On-Break  Print=Never level=3
Before=TERRITORY_NAME_BeforeProc303
DIST_NAME &DIST_NAME () On-Break  Level=4  save=$olddist level=4
Before=DIST_NAME_BeforeProc304
BRAND_MAJOR_NAME &BRAND_MAJOR_NAME () On-Break Print=Never level=5
Before=BRAND_MAJOR_NAME_BeforeProc305
TERTILE &TERTILE () On-Break  Print=Never level=6
Before=TERTILE_BeforeProc306
FISCAL_MONTH &FISCAL_MONTH

CHANNEL &CHANNEL
CONSUM &CONSUM
PROMO_TYPE &PROMO_TYPE
TACTIC &TACTIC

  if #m = 0 and Tertile1-other-spend-data.os-divname(#m) = ''
    let Tertile1-other-spend-data.os-divname(#m) = &division_name
    let Tertile1-other-spend-data.os-fiscalyear(#m) = &fiscal_year
    let Tertile1-other-spend-data.os-terrname(#m) = &territory_name
    let Tertile1-other-spend-data.os-distname(#m) = &dist_name
    let Tertile1-other-spend-data.os-brand(#m) = &brand_major_name
 end-if
     if Tertile1-other-spend-data.os-divname(#m)!= ' '
     let #m = #m + 1
     let Tertile1-other-spend-data.os-divname(#m) = &division_name
     let Tertile1-other-spend-data.os-fiscalyear(#m) = &fiscal_year
     let Tertile1-other-spend-data.os-terrname(#m) = &territory_name
     let Tertile1-other-spend-data.os-distname(#m) = &dist_name
     let Tertile1-other-spend-data.os-brand(#m) = &brand_major_name
     let Tertile1-other-spend-data.os-tertile(#m) = &tertile
  end-if
!hdr month gets hardcoded
  if &tertile = '01'
   Let Tertile1-other-spend-data.os-fiscalmonth(0) = 'May'
   Let Tertile1-other-spend-data.os-fiscalmonth(1) = 'Jun'
   Let Tertile1-other-spend-data.os-fiscalmonth(2) = 'Jul'
   Let Tertile1-other-spend-data.os-fiscalmonth(3) = 'Aug'
  end-if

  if &tertile = '02'
   Let Tertile1-other-spend-data.os-fiscalmonth(0) = 'Sep'
   Let Tertile1-other-spend-data.os-fiscalmonth(1) = 'Oct'
   Let Tertile1-other-spend-data.os-fiscalmonth(2) = 'Nov'
   Let Tertile1-other-spend-data.os-fiscalmonth(3) = 'Dec'
  end-if

  if &tertile = '03'
   Let Tertile1-other-spend-data.os-fiscalmonth(0) = 'Jan'
   Let Tertile1-other-spend-data.os-fiscalmonth(1) = 'Feb'
   Let Tertile1-other-spend-data.os-fiscalmonth(2) = 'Mar'
   Let Tertile1-other-spend-data.os-fiscalmonth(3) = 'Apr'
  end-if

!detail
  if #m = 0 and Tertile1-other-spend-data.os-monthname(#m) = ''
    let Tertile1-other-spend-data.os-monthname(#m) = &fiscal_month
    let Tertile1-other-spend-data.os-channel(#m) = &channel
    let Tertile1-other-spend-data.os-consum(#m) = &consum
    let Tertile1-other-spend-data.os-promo_type(#m) = &promo_type
  end-if
  if Tertile1-other-spend-data.os-monthname(#m)!= ''
    let #m = #m + 1
    let Tertile1-other-spend-data.os-monthname(#d) = &fiscal_month
    let Tertile1-other-spend-data.os-channel(#d) = &channel
    let Tertile1-other-spend-data.os-consum(#d) = &consum
    let Tertile1-other-spend-data.os-promo_type(#d) = &promo_type
    let Tertile1-other-spend-data.os-tactic(#d) = &tactic
   end-if
 ! end-if
 ! Next-Listing need=1
>From  ADY_TO_BRIO, BRAND_LOOKUP
,      DIVISION_TERRITORY
      Where ADY_TO_BRIO.TERRITORY = DIVISION_TERRITORY.TERRITORY_CODE
      And ADY_TO_BRIO.BRAND = BRAND_LOOKUP.BRAND_MAJOR_CODE
Order By DIVISION_NAME
,      FISCAL_YEAR
,      TERRITORY_NAME
,      DIST_NAME
,      BRAND_MAJOR_NAME
,      TERTILE
,      TO_DATE(FISCAL_MONTH,'Mon')
,      CHANNEL
,      CONSUM
,      PROMO_TYPE
End-Select
   Print-Direct printer=html '%%ResetBorder'
End-Procedure

Begin-Procedure print-pagehdr
  Print-Direct printer=html '%%ResetBorder'
   Alter-Printer Font=901 Point-Size=7
      if #firstpage != 1
        new-page
      else
        move 0 to #firstpage
      end-if
      !month name 1
!      Let #month_cnt = #m
!      Let #m = 0
!      while #m <= #month_cnt
        let $TT1-DIVNAME = Tertile1-other-spend-data.os-divname(#m)
        let $TT1-FISCALYEAR = Tertile1-other-spend-data.os-fiscalyear(#m)
        let $TT1-TERRNAME = Tertile1-other-spend-data.os-terrname(#m)
        let $TT1-DISTNAME = Tertile1-other-spend-data.os-distname(#m)
        let $TT1-BRAND = Tertile1-other-spend-data.os-brand(#m)
        let $TT1-TERTILE = Tertile1-other-spend-data.os-tertile(#m)
        let $TT1-MONTH1 = Tertile1-other-spend-data.os-fiscalmonth(0)
        let $TT1-MONTH2 = Tertile1-other-spend-data.os-fiscalmonth(1)
        let $TT1-MONTH3 = Tertile1-other-spend-data.os-fiscalmonth(2)
        let $TT1-MONTH4 = Tertile1-other-spend-data.os-fiscalmonth(3)
        Print 'Report 120 Day Calendar' (2,2) BOLD
        Print 'Division:'  (,50) BOLD
        Print $TT1-DIVNAME (,57)
        Print 'Tertile: ' (3,2) BOLD
        Print $TT1-TERTILE (,8)
        Print 'Territory'  (3,50)BOLD
        Print $TT1-TERRNAME (3,57)
        Print 'Brand:' (3,89) BOLD
        Print $TT1-BRAND (3,95)
        Print 'View: Summary' (4,2) BOLD
        Print 'Dist Name:'(4,50)BOLD
        Print $TT1-DISTNAME (4,57,50)
        Print 'Fiscal Year:' (4,89) BOLD
        Print $TT1-FISCALYEAR (4,97)
!next line of header
        print $TT1-MONTH1 (6,30) bold
        print $TT1-MONTH2 (,50) bold
        print $TT1-MONTH3 (,70) bold
        print $TT1-MONTH4 (,90) bold

!next line of header
        Print 'Channel'(7,3) bold
        Print 'Consum' (,12) bold
        Print 'Type' (,20) bold
        !month 1
        Print 'Tactic Description' (,30) Bold
        !month 2
        Print 'Tactic Description' (,50) Bold
       !month 3
        Print 'Tactic Description' (,70) Bold
       !month 4
        Print 'Tactic Description' (,90) Bold
     graphic (,2,108) horz-line
     next-listing
End-Procedure

Begin-Procedure print-tertile1-os-data
Print-Direct printer=html '%%ResetBorder'
   let #month_rec_count = #d
   let #d = 0
   While #d <= #month_rec_count
      let $TT1-MONTH =  Tertile1-other-spend-data.os-monthname(#d)
      let $TT1-CHANNEL =  Tertile1-other-spend-data.os-channel(#d)
      let $TT1-CONSUM = Tertile1-other-spend-data.os-consum(#d)
      let $TT1-PROMO_TYPE = Tertile1-other-spend-data.os-promo_type(#d)
      let $TT1-MONTH-OSD-TACTIC = Tertile1-other-spend-data.os-tactic(#d)

      Print $TT1-MONTH (,1,3)
      Print $TT1-CHANNEL (,7,9)
      Print $TT1-CONSUM (,16,4)
      Print $TT1-PROMO_TYPE (,21,20)
      Print $TT1-MONTH-OSD-TACTIC (,30,50)
      next-listing
      let #d = #d + 1
   end-while
    Alter-Printer Font=901 Point-Size=7
!    next-listing
end-procedure

Begin-Footing 2
 Print-Direct printer=html '%%ResetBorder'
 Print 'Page:' (1,90)
 Page-Number (1,95)
 Print $current-date (2,90) edit 'MM/DD/YYYY' BOLD
End-Footing

!updates the TOC
Begin-Procedure DIVISION_NAME_BeforeProc301
 Print-Direct printer=html '%%ResetBorder'
   let $newdiv = &division_name
   if $newdiv != $olddiv
      do print-pagehdr
      move $newdiv to $olddiv
   end-if
  Toc-Entry text=&division_name level=1
End-Procedure

Begin-Procedure FISCAL_YEAR_BeforeProc302
 Print-Direct printer=html '%%ResetBorder'
   let $newfy = &fiscal_year
   if $newfy != $oldfy
      do print-pagehdr
      move $newfy to $oldfy
   end-if
   Toc-Entry text=&fiscal_year level=2
End-Procedure

Begin-Procedure TERRITORY_NAME_BeforeProc303
   Print-Direct printer=html '%%ResetBorder'
  let $newterr = &territory_name
   if $newterr != $oldterr
      do print-pagehdr
      move $newterr to $oldterr
   end-if
 Toc-Entry text=&territory_name level=3
End-Procedure

Begin-Procedure DIST_NAME_BeforeProc304
 Print-Direct printer=html '%%ResetBorder'
   let $newdist = &dist_name
   if $newdist != $olddist
      do print-pagehdr
      move $newdist to $olddist
   end-if
 Toc-Entry text=&dist_name level=4
End-Procedure

Begin-Procedure BRAND_MAJOR_NAME_BeforeProc305
   Print-Direct printer=html '%%ResetBorder'
   let $newbrand = &brand_major_name
   if $newbrand != $oldbrand
      do print-pagehdr
      move $newbrand to $oldbrand
   end-if
 Toc-Entry text=&brand_major_name level=5
End-Procedure

Begin-Procedure TERTILE_BeforeProc306
   Print-Direct printer=html '%%ResetBorder'
   let $newtertile = &tertile
   if $newtertile != $oldtertile
      do print-pagehdr
      move $newtertile to $oldtertile
   end-if
 Toc-Entry text=&TERTILE level=6
 Alter-Printer Font=901 Point-Size=7
End-Procedure
[Deb Harris]


I am getting something like this..   All the records are going under the
last Distributor instead of their own distributor pages.   How do I get the
individual months to appear in the correct month columns?

Report 120 Day Calendar Division: Utopia Division

Tertile: 01 Territory Utopia 1 Brand: JACK DANIELS

May Jun Jul Aug

Channel Consum Type Tactic Description Tactic Description Tactic Description
Tactic Description

May OFF_INDEP GCM -

May OFF_INDEP GCM -

May ON_PREMIS GCM -

Jun OFF_INDEP GCM -

Jun ON_PREMIS GCM -

Jul OFF_INDEP GCM -

Jul ON_PREMIS GCM -

Here was a non admissible message part of 'image/gif' MIME type. It has
been automatically discarded before sending the message to the list.

Here was a non admissible message part of 'image/gif' MIME type. It has
been automatically discarded before sending the message to the list.

Prev by Date: [sqr-users] writing dashboard exceptions from SQR
Next by Date: [sqr-users] Help with Arrays...
Previous by thread: [sqr-users] writing dashboard exceptions from SQR
Next by thread: RE: [sqr-users] FW: Help with Arrays...
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page