[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
RE: [sqr-users] Print issue

Subject: RE: [sqr-users] Print issue
From: "Dwight Schrute" <schrute.dwight@hotmail.com>
Date: Thu, 24 Aug 2006 18:40:28 -0500
Bcc:
Delivery-date: Thu, 24 Aug 2006 19:43:19 -0400
In-reply-to: <F21043E4D7DE6E419F956791AEEDEA5D02141104@corpex03.navigant.com>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
This proposed solution will not work.  The problem is with the data 
relationship of many-to-many.

You have four items in one table and three items in another and neither 
relates to the other except by roleuser/oprid.  This will give you twelve 
rows in a result set.  You must divide it into separate pieces in order to 
conquer this obstacle.

You must try something like this...

!----------------------------------- get-roleuser
begin-procedure get-roleuser
begin-select distinct
ROLEUSER &roleuser (+2,1)
  let $roleuser = '''' || &roleuser || ''''
  let #starting-point = #current-line
  do get-rolename
  do get-emplid
  do get-deptid
  position (#max-lines)
FROM TABLE1
end-select
end-procedure
!----------------------------------- get-rolename
begin-procedure get-rolename
begin-select
ROLENAME (0,90,30)
  position (+1)
FROM TABLE1
WHERE ROLEUSER = [$roleuser]
end-select
  let #max-lines = #current-line
end-procedure
!------------------------------------- get-emplid
begin-procedure get-emplid
  position (#starting-point)
begin-select
EMPLID (0,144,11)
  position (+1)
FROM TABLE2
WHERE OPRID = [$roleuser]
end-select
  if #current-line > #max-lines
    let #max-lines = #current-line
  end-if
end-procedure
!------------------------------------- get-deptid
begin-procedure get-deptid
  position (#starting-point)
begin-select
DEPTID (0,121,10)
  position (+1)
FROM TABLE2
WHERE OPRID = [$roleuser]
end-select
  if #current-line > #max-lines
    let #max-lines = #current-line
  end-if
end-procedure

This way you can print down and then move back to the starting line.  You 
could also use columns.

If this does not do the trick, you can try some nifty arrays.

Dwight


>From: "Munger, Lance" <Lance.Munger@TQ3Navigant.com>
>Reply-To: "This list is for discussion about the SQR database 
>reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
>To: "This list is for discussion about the SQR database reportinglanguage 
>fromHyperion Solutions." <sqr-users@sqrug.org>
>Subject: RE: [sqr-users] Print issue
>Date: Thu, 24 Aug 2006 13:18:04 -0600
>
>I think the issue is that you make the call to write_data and then you
>process more data and call write_data1.  Try this version.
>
>program:
>!***********************************************************************
>begin-procEdure get-oprid-data
>!***********************************************************************
>       move '' to $rowsecclass
>       move '' to $classdefndesc
>
>begin-select distinct
>OPR.OPRID                      &opr.oprid
>OPR.OPRDEFNDESC        &opr.oprdefndesc
>OPR.ROWSECCLASS
>CLS.CLASSDEFNDESC
>
>       move &opr.oprid to $oprid
>       move &opr.rowsecclass to $rowsecclass
>       move &cls.classdefndesc to $classdefndesc
>       let $Company = &COMPANY
>
>       do get-role-count
>       do check-custom-security
>
>       if #count > 1
>               do evaluate-HR-User
>               do get-role-data
>!              do evaluate-custom-security
>       end-if
>
>FROM PSOPRDEFN OPR, PSCLASSDEFN CLS, PSROLEUSER RU,
>PS_JOB JOB        !FSCPR001 Begin change
>WHERE OPR.ROWSECCLASS = CLS.CLASSID
>AND  OPR.OPRID = RU.ROLEUSER
>AND OPR.OPRID = JOB.EMPLID
>AND JOB.EFFDT  = (SELECT MAX(EFFDT)
>                               FROM PS_JOB
>                               WHERE EMPLID   = JOB.EMPLID
>                               AND EMPL_RCD = JOB.EMPL_RCD
>                               AND EFFDT   <= SYSDATE)
>AND JOB.EFFSEQ = (SELECT MAX(EFFSEQ)
>                               FROM PS_JOB
>                               WHERE EMPLID   = JOB.EMPLID
>                               AND EMPL_RCD = JOB.EMPL_RCD
>                               AND EFFDT    = JOB.EFFDT)
>[$COMPANY-SQL1]
>[$ROLENAME-SQL1]
>                               !FSCPR001 End change ORDER BY
>OPR.ROWSECCLASS, OPR.OPRID end-select end-procedure
>
>!***********************************************************************
>begin-procedure get-role-data
>!***********************************************************************
>
>       move '' to $rolename
>       move 0 to #Idx
>
>begin-select
>RU.ROLEUSER
>RU.ROLENAME
>
>       let $roleuser = &ru.roleuser
>       let $rolename = &ru.rolename
>
>       do evaluate-custom-security
>
>FROM PSROLEUSER RU
>WHERE RU.ROLEUSER = $oprid
>AND RU.ROLENAME NOT IN ('HR User','Business Owner') AND RU.ROLENAME NOT
>LIKE '%Employee%'
>ORDER BY RU.ROLEUSER
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure get-role-count
>!***********************************************************************
>begin-select
>COUNT(COUNT.ROLENAME)  &count
>
>       move &count to #count
>
>FROM PSROLEUSER COUNT
>WHERE COUNT.ROLEUSER = $oprid
>AND COUNT.ROLENAME NOT IN ('HR User','Business Owner') end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure evaluate-HR-User
>!***********************************************************************
>       move '' to $HRUser
>       move '' to $BusOwner = ' '
>
>begin-select
>HR.ROLENAME
>
>       Evaluate &hr.ROLENAME
>               When = 'HR User'
>                       move 'H' to $HRUser
>                       move 'H' to $HR_output
>               When = 'Business Owner'
>                       move 'B' to $BusOwner
>                       move 'B' to $BO_Output
>       End-evaluate
>
>FROM PSROLEUSER HR
>WHERE HR.ROLEUSER = $oprid
>AND HR.ROLENAME IN ('HR User','Business Owner')
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure evaluate-custom-security
>!***********************************************************************
>
>       move '' to $deptid
>       move '' to $grade
>       move '' to $empcd
>       move '' to $emplid
>       move '' to $empType
>       move '' to $location
>
>begin-select
>SEC.OPRID
>SEC.DEPTID                     &sec.Deptid
>SEC.GRADE                              &sec.Grade
>SEC.FSC_EMPLOYER_CODE  &sec.Empcd
>SEC.EMPLID                     &sec.Emplid
>SEC.EMPL_TYPE                  &sec.EmpType
>SEC.LOCATION                   &sec.Location
>
>       Let $deptid = &sec.Deptid
>       Let  $grade = &sec.Grade
>       Let  $empcd = &sec.Empcd
>       Let  $emplid = &sec.Emplid
>       Let  $emptype = &sec.EmpType
>       Let  $location = &sec.Location
>
>       do write-data1
>
>FROM PS_CUSTOM_SEC_TBL SEC
>WHERE SEC.OPRID = $oprid
>ORDER BY SEC.DEPTID, SEC.GRADE, SEC.FSC_EMPLOYER_CODE, SEC.EMPLID,
>SEC.EMPL_TYPE, SEC.LOCATION
>end-select
>end-procedure
>
>begin-procedure write-data1
>
>       print $oprid        (+1,1,11)           on-break
>       print=change skiplines=1
>       print &opr.oprdefndesc  (0,12,30)       on-break
>       print=change
>       print $HR_output        (0,43,2)
>       print $$BO_output       (0,46,2)
>       print $rowsecclass      (0,49,8)        on-break
>       print=change
>       print $classdefndesc    (0,58,31)       on-break
>       print=change
>       print $rolename         (0,90,30)
>       print $deptid           (0,121,10)      !&sec.Deptid
>       print $grade            (0,132,5)       !&sec.Grade
>       print $empcd            (0,138,5)       !&sec.Empcd
>       print $emplid           (0,144,11)      !&sec.Emplid
>       print $emptype          (0,156,8)       !&sec.EmpType
>       print $location         (0,165,10)
>
>       print $deptid           (0,121,10)      !&sec.Deptid
>       print $grade            (0,132,5)       !&sec.Grade
>       print $empcd            (0,138,5)       !&sec.Empcd
>       print $emplid           (0,144,11)      !&sec.Emplid
>       print $emptype          (0,156,8)       !&sec.EmpType
>       print $location         (0,165,10)
>
>end-procedure
>
>
>Lance D. Munger
>
>
>
>
>Report Development
>Carlson Wagonlit Travel * formerly TQ3Navigant
>Direct Line: +1 (303) 925-3120
>Email: lmunger@carlsonwagonlit.com
>
>-----Original Message-----
>From: sqr-users-bounces+lance.munger=tq3navigant.com@sqrug.org
>[mailto:sqr-users-bounces+lance.munger=tq3navigant.com@sqrug.org] On
>Behalf Of prashanth reddy
>Sent: Thursday, August 24, 2006 8:55 AM
>To: sqr-users@sqrug.org
>Subject: [sqr-users] Print issue
>
>Hello,
>
>    I'm trying to print multiple values from different
>tables for each userid. Program gets the data
>accordingly,
>  but when trying to print, its not in proper format.
>Current format:
>OprID   Name   HR Rolename  Dept  Grade Empcd Emplid
>-----   ----   -----------  ----  ----- ----- ------
>FS007 John D   H  Payroll
>                H  Data Entry
>                H  Reports
>                             107D              G728292
>
>Expected format:
>OprID   Name   HR  Rolename  Dept  Grade Empcd Emplid
>
>-----   -----  --  --------- ----  ----- ------ ------
>
>FS007 John D   H   Payroll   107D              F892389
>                H   Data Entry                  F839303
>                H   Reports                     G728292
>
>Data in two tables:
>
>table1:
>roleuser    rolename
>-------     --------
>FS007       Payroll
>FS007       Data Entry
>FS007       Reports
>table2:
>oprid   emplid  deptid
>-----   ------  ------
>FS007   F892389
>FS007   F839303
>FS007   G728292
>FS007            107D
>
>program:!***************************************************************
>********
>begin-procEdure get-oprid-data
>!***********************************************************************
>Let $rowsecclass = ' '
>Let $classdefndesc = ' '
>
>begin-select distinct
>OPR.OPRID      &opr.oprid
>OPR.OPRDEFNDESC   &opr.oprdefndesc
>OPR.ROWSECCLASS
>CLS.CLASSDEFNDESC
>     move &opr.oprid to $oprid
>     move &opr.rowsecclass to $rowsecclass
>     move &cls.classdefndesc to $classdefndesc
>     let $Company = &COMPANY
>
>     do get-role-count
>     do check-custom-security
>
>     if #count > 1
>        do evaluate-HR-User
>         do get-role-data
>         do evaluate-custom-security
>     end-if
>
>FROM PSOPRDEFN OPR, PSCLASSDEFN CLS, PSROLEUSER RU,
>PS_JOB JOB        !FSCPR001 Begin change
>WHERE OPR.ROWSECCLASS = CLS.CLASSID
>AND  OPR.OPRID = RU.ROLEUSER
>AND OPR.OPRID = JOB.EMPLID
>AND JOB.EFFDT  = (SELECT MAX(EFFDT)
>                       FROM PS_JOB
>                      WHERE EMPLID   = JOB.EMPLID
>                        AND EMPL_RCD = JOB.EMPL_RCD
>                        AND EFFDT   <= SYSDATE)
>AND JOB.EFFSEQ = (SELECT MAX(EFFSEQ)
>                       FROM PS_JOB
>                      WHERE EMPLID   = JOB.EMPLID
>                        AND EMPL_RCD = JOB.EMPL_RCD
>                        AND EFFDT    = JOB.EFFDT)
>[$COMPANY-SQL1]
>[$ROLENAME-SQL1]
>                        !FSCPR001 End change
>ORDER BY OPR.ROWSECCLASS, OPR.OPRID
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure get-role-data
>!***********************************************************************
>let $rolename = ''
>let #Idx = 0
>begin-select
>RU.ROLEUSER
>RU.ROLENAME
>     let $roleuser = &ru.roleuser
>     let $rolename = &ru.rolename
>
>     do write-data
>FROM PSROLEUSER RU
>WHERE RU.ROLEUSER = $oprid
>AND RU.ROLENAME NOT IN ('HR User','Business Owner')
>AND RU.ROLENAME NOT LIKE '%Employee%'
>ORDER BY RU.ROLEUSER
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure get-role-count
>!***********************************************************************
>begin-select
>COUNT(COUNT.ROLENAME)  &count
>
>    move &count to #count
>
>FROM PSROLEUSER COUNT
>WHERE COUNT.ROLEUSER = $oprid
>AND COUNT.ROLENAME NOT IN ('HR User','Business Owner')
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure evaluate-HR-User
>!***********************************************************************
>let $HRUser   = ' '
>let $BusOwner = ' '
>
>begin-select
>HR.ROLENAME
>
>     Evaluate &hr.ROLENAME
>        When = 'HR User'
>           move 'H' to $HRUser
>           move 'H' to $HR_output
>        When = 'Business Owner'
>           move 'B' to $BusOwner
>           move 'B' to $BO_Output
>     End-evaluate
>
>FROM PSROLEUSER HR
>WHERE HR.ROLEUSER = $oprid
>AND HR.ROLENAME IN ('HR User','Business Owner')
>end-select
>end-procedure
>
>!***********************************************************************
>begin-procedure evaluate-custom-security
>
>!***********************************************************************
>
>Let $deptid = ' '
>Let $grade = ' '
>Let $empcd = ' '
>Let $emplid = ' '
>Let $empType = ' '
>Let $location = ' '
>
>begin-select
>SEC.OPRID
>SEC.DEPTID              &sec.Deptid
>
>SEC.GRADE               &sec.Grade
>SEC.FSC_EMPLOYER_CODE   &sec.Empcd
>SEC.EMPLID              &sec.Emplid
>SEC.EMPL_TYPE           &sec.EmpType
>SEC.LOCATION            &sec.Location
>
>
>     Let $deptid = &sec.Deptid
>     Let  $grade = &sec.Grade
>     Let  $empcd = &sec.Empcd
>     Let  $emplid = &sec.Emplid
>     Let  $emptype = &sec.EmpType
>     Let  $location = &sec.Location
>
>    do write-data1
>
>FROM PS_CUSTOM_SEC_TBL SEC
>WHERE SEC.OPRID = $oprid
>ORDER BY SEC.DEPTID, SEC.GRADE, SEC.FSC_EMPLOYER_CODE,
>SEC.EMPLID, SEC.EMPL_TYPE, SEC.LOCATION
>end-select
>end-procedure
>
>begin-procedure write-data1
>     print $deptid           (0,121,10)    !&sec.Deptid
>     print $grade            (0,132,5)     !&sec.Grade
>     print $empcd            (0,138,5)     !&sec.Empcd
>     print $emplid           (0,144,11)    !&sec.Emplid
>     print $emptype          (0,156,8)
>!&sec.EmpType
>     print $location         (0,165,10)
>end-procedure
>
>
>
>!***********************************************************************
>begin-procedure write-data
>!***********************************************************************
>
>     print $oprid        (+1,1,11)   on-break
>print=change skiplines=1
>     print &opr.oprdefndesc  (0,12,30)   on-break
>print=change
>     print $HR_output        (0,43,2)
>     print $$BO_output       (0,46,2)
>     print $rowsecclass      (0,49,8)     on-break
>print=change
>     print $classdefndesc    (0,58,31)    on-break
>print=change
>     print $rolename         (0,90,30)
>     print $deptid           (0,121,10)    !&sec.Deptid
>     print $grade            (0,132,5)     !&sec.Grade
>     print $empcd            (0,138,5)     !&sec.Empcd
>     print $emplid           (0,144,11)    !&sec.Emplid
>     print $emptype          (0,156,8)
>!&sec.EmpType
>     print $location         (0,165,10)
>end-procedure
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>_______________________________________________
>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

_________________________________________________________________
Search from any web page with powerful protection. Get the FREE Windows Live 
Toolbar Today!   http://get.live.com/toolbar/overview


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
References:
- RE: [sqr-users] Print issue
  - From: "Munger, Lance" <Lance.Munger@TQ3Navigant.com>
Prev by Date: RE: [sqr-users] Print issue
Next by Date: [sqr-users] PeopleSoft Custom Paycheck SQR
Previous by thread: RE: [sqr-users] Print issue
Next by thread: [sqr-users] PeopleSoft Custom Paycheck SQR
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page