Re: [sqr-users] variable resetting to 0 problem

[Russ Wulfson <rwulfson@bost.com>]

I think it's related to the fact the procedures that take parameters have 
variables that are local only to that procedure.

Thanks,

Russ Wulfson




Bob Stone <bstone@fastenal.com> 
Sent by: sqr-users-bounces+rwulfson=bost.com@sqrug.org
11/05/2004 12:37 PM
Please respond to
"This list is for discussion about the SQR database reporting   language 
from Hyperion Solutions." <sqr-users@sqrug.org>


To
"'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
cc

Subject
[sqr-users] variable resetting to 0 problem






Hey there.  Okay, so this should be a pretty easy problem, but it has me
currently stumped.  I'm trying to use a couple variables across my SQR and
by the time I go to use them, they've been reset to 0.  Here's a summary 
of
my program (i've taken out the parts which I know work fine, and aren't 
part
of the problem). My idea is to set up these variables that are going to be
used through the whole SQR.  But as they're being called through 
procedures,
they're not being set correctly.  REPORT calls CC_REPORT calls
BUSINESS_UNIT_ROW sets #D_BU_COUNT.  but when I try to use it in a 
different
procedure (END_OF_DISTRICT) it isn't set. Can someone tell me what i'm 
doing
wrong with my variables?!?! 

do i need to declare them somewhere else? 
is the problem with how many procedures its going through?
i'm stumped.  any help would be appreciated.


The finished product will look somethign like this
                        normal     single
BU1                              10              5 
BU2                              20              7
BU3                              17              2
BU4                              13              10

dist tot                                 80              24
Avg                              20              6               ** except 
#d_bu_count isn't being correctly
increased by one!!



-------------------------
BOB.SQR        ||
-------------------------

!*******************************************************************
BEGIN-SETUP
!*******************************************************************
END-SETUP

!*******************************************************************
BEGIN-REPORT
!*******************************************************************
                                 !-------District variables
                 let #d_normal = 0
                 let #d_single = 0
                 let #d_bu_count = 0
                                 !-------Region variables
                 let #r_normal = 0
                 let #r_single = 0
                 let #r_bu_count = 0
                                 !------Excel Variables
                 Let #RowNum = 1
                 Do EXCEL-HEADING

                 do CC_REPORT

END-REPORT

!*******************************************************************
BEGIN-PROCEDURE CC_REPORT
!*******************************************************************
BEGIN-SELECT 
fas_branch5                              &business_unit 
fas_district_descr               ()              ON-BREAK PRINT=NEVER 
AFTER=END_OF_DISTRICT
SKIPLINES = 1
fas_regional_descr               ()              ON-BREAK PRINT=NEVER 
AFTER=END_OF_REGION
SKIPLINES = 1

                 let $business_unit = &business_unit
                 Do BUSINESS_UNIT_ROW($business_unit)  

FROM sysadm.ps_table WHERE busin_unit_type = 'BRNC'  AND fas_branch5 in (
**Blah blah blah.  sql works fine ) ORDER BY fas_region_code, 
fas_district,
fas_bu_4char
END-SELECT
END-PROCEDURE CC_REPORT

!*******************************************************************
BEGIN-PROCEDURE BUSINESS_UNIT_ROW($bu)
!*******************************************************************
                 !******************** Normal CC
BEGIN-SELECT on-error=sql-error
count(distinct a.counting_event_id)              &n.count
                 let #cyclcnt_count = &n.count
FROM ps_count_hdr_inv a, ps_fas_count2_inv c
WHERE a.business_unit = c.business_unit and a.counting_event_id =
c.counting_event_id
and c.business_unit = $bu and c.fas_cyclcnt_type = 'N'
END-SELECT

                 let #d_normal = #d_normal + #cyclcnt_count
                 let #r_normal = #r_normal + #cyclcnt_count

                 !******************** Single CC
BEGIN-SELECT on-error=sql-error
count(distinct a.counting_event_id)              &s.count
                 let #cyclcnt_count = &s.count
FROM ps_count_hdr_inv a, ps_fas_count2_inv c
WHERE a.business_unit = c.business_unit and a.counting_event_id =
c.counting_event_id
and c.business_unit = $bu and c.fas_cyclcnt_type = 'S'
END-SELECT

                 let #d_single = #d_single + #cyclcnt_count
                 let #r_single = #r_single + #cyclcnt_count

                 !********************
                 let #d_bu_count = #d_bu_count + 1
                 let #r_bu_count = #r_bu_count + 1
                 let #RowNum = #RowNum + 1
END-PROCEDURE BUSINESS_UNIT_ROW

!*******************************************************************
BEGIN-PROCEDURE END_OF_DISTRICT 
!*******************************************************************
!***TOTALS

Do Excel-Write-Number(1, #RowNum, 4, #d_normal)
Do Excel-Write-Number(1, #RowNum, 5, #d_single)

!***AVERAGES
let #d_normal = #d_normal / #d_bu_count !****ERROR - cannot divide
by ZERO***!!
let #d_single = #d_single / #d_bu_count

let #RowNum = #RowNum + 1 !skip a row

Do Excel-Write-Number(1, #RowNum, 4, #d_normal)
Do Excel-Write-Number(1, #RowNum, 5, #d_single)

!***RESET VARIABLES to 0 FOR NEXT DISTRICT
let #d_normal = 0
let #d_single = 0

let #RowNum = #RowNum + 1 !skip a row

END-PROCEDURE END_OF_DISTRICT 

  #include **stuff - includes are working fine**

_______________________________________________
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