[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: SQR-USERS Digest - 1 Jul 2001 to 2 Jul 2001 (#2001-160)
- Subject: Re: SQR-USERS Digest - 1 Jul 2001 to 2 Jul 2001 (#2001-160)
- From: Steven Stein <steven.stein@COVANCE.COM>
- Date: Tue, 3 Jul 2001 13:55:08 -0500
Emil: There's an interesting technique you can use if you want to get
fancy.
Save the spreadsheet in exactly the format you want as a .SLK file.
The code can then be seen in a Text Editor. You can then make the SQR
write a .SLK file. Franck Masson taught us this technique in this
user group. Here's a sample.
By the way, most of that fancy-looking formatting is easy because I
use Textpad and write a Macro to wrap the SQR code around the .SLK
code. For instance, when I save my sample .SLK file from Excel I get
a line like:
P;P"$"#,##0_);;[Red]\("$"#,##0\)
and I use a Textpad macro to change that to: write 1 from
'P;P"$"#,##0_);;[Red]\("$"#,##0\)'
- Steven Stein, CPA Covance Inc. 609-452-4551
BEGIN-PROGRAM
let $file = 'C:\INVVAL.txt'
Open $file as 1 for-writing record = 1000
LET #Y = 2
LET #COUNT = 0
LET #PUTWY = 0
LET #CNT_PTWY = 0
LET #QTY_PTWY = 0
LET #ISSUES = 0
LET #CNT_ISS = 0
LET #QTY_ISS = 0
LET #ADJ = 0
LET #CNT_ADJ = 0
LET #ADJ_DECR = 0
LET #ADJ_INCR = 0
LET #OTHER = 0
LET #CNT_OTHER = 0
MOVE ' ' TO $BUSUNIT
MOVE ' ' TO $ACCNT
MOVE ' ' TO $ACCTDESC
MOVE ' ' TO $INVID
MOVE ' ' TO $UOMSTD
MOVE ' ' TO $UOMUOM
MOVE ' ' TO $ITEMDESC
do write-header
DO MAIN-REPORT
do write-totals
END-PROGRAM
! MAIN REPORT
BEGIN-PROCEDURE MAIN-REPORT
begin-SELECT
A.BUSINESS_UNIT
count(*) &COUNT
A.ACCOUNT
B.DESCR
A.INV_ITEM_ID
sum(decode( A.TRANSACTION_GROUP,'020', A.MONETARY_AMOUNT,0)) &PUTWY
sum(decode( A.TRANSACTION_GROUP,'020',1,0)) &CNT_PTWY
sum(decode( A.TRANSACTION_GROUP,'020', C.QTY_BASE,0)) &QTY_PTWY
sum(decode( A.TRANSACTION_GROUP,'030', A.MONETARY_AMOUNT,0)) &ISSUES
sum(decode( A.TRANSACTION_GROUP,'030',1,0)) &CNT_ISS
sum(decode( A.TRANSACTION_GROUP,'030', C.QTY_BASE,0)) &QTY_ISS
sum(decode( A.TRANSACTION_GROUP,'050', A.MONETARY_AMOUNT,0)) &ADJ
sum(decode( A.TRANSACTION_GROUP,'050',1,0)) &CNT_ADJ
sum(decode( A.TRANSACTION_GROUP ,'050',
DECODE(C.ADJUST_TYPE,'D',C.QTY_BASE*-1,0),0)) &ADJ_DECR
sum(decode( A.TRANSACTION_GROUP ,'050',
DECODE(C.ADJUST_TYPE,'I',C.QTY_BASE,0),0)) &ADJ_INCR
sum(decode( A.TRANSACTION_GROUP,'020',0,'030',0,'050',0,
A.MONETARY_AMOUNT)) &OTHER
sum(decode( A.TRANSACTION_GROUP,'020',0,'030',0,'050',0,1)) &CNT_OTHER
C.UNIT_MEASURE_STD
C.UNIT_OF_MEASURE
D.DESCR
LET #COUNT = &COUNT
LET #PUTWY = &PUTWY
LET #CNT_PTWY = &CNT_PTWY
LET #QTY_PTWY = &QTY_PTWY
LET #ISSUES = &ISSUES
LET #CNT_ISS = &CNT_ISS
LET #QTY_ISS = &QTY_ISS
LET #ADJ = &ADJ
LET #CNT_ADJ = &CNT_ADJ
LET #ADJ_DECR = &ADJ_DECR
LET #ADJ_INCR = &ADJ_INCR
LET #OTHER = &OTHER
LET #CNT_OTHER = &CNT_OTHER
MOVE &A.BUSINESS_UNIT TO $BUSUNIT
MOVE &A.ACCOUNT TO $ACCNT
MOVE &B.DESCR TO $ACCTDESC
MOVE &A.INV_ITEM_ID TO $INVID
MOVE &C.UNIT_MEASURE_STD TO $UOMSTD
MOVE &C.UNIT_OF_MEASURE TO $UOMUOM
MOVE &D.DESCR TO $ITEMDESC DO WRITE-LINES
FROM PS_CM_ACCTG_LINE A, PS_GL_ACCOUNT_TBL B, PS_TRANSACTION_INV C,
PS_MASTER_ITEM_TBL D
WHERE A.BUSINESS_UNIT = 'ABC01'
AND B.ACCOUNT = A.ACCOUNT
AND A.ACCOUNT LIKE '04%'
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID =
B_ED.SETID
AND B.ACCOUNT = B_ED.ACCOUNT
AND B_ED.EFFDT <= SYSDATE)
AND B.SETID = 'XYZ01'
AND D.SETID = 'ABC01'
AND D.INV_ITEM_ID = C.INV_ITEM_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID =
C.INV_ITEM_ID
AND A.DT_TIMESTAMP = C.DT_TIMESTAMP
AND A.SEQ_NBR = C.SEQ_NBR
AND A.TRANSACTION_GROUP = C.TRANSACTION_GROUP
AND A.ACCOUNTING_DT <= TO_DATE('2001-04-30','YYYY-MM-DD')
! AND A.INV_ITEM_ID like '0000000000000078%'
GROUP BY A.BUSINESS_UNIT,
A.ACCOUNT,
B.DESCR,
A.INV_ITEM_ID,
C.UNIT_MEASURE_STD,
C.UNIT_OF_MEASURE,
D.DESCR
END-SELECT
END-PROCEDURE
! WRITE header
BEGIN-PROCEDURE WRITE-HEADER
write 1 from 'ID;PWXL;N;E'
write 1 from 'P;PGeneral'
write 1 from 'P;P0'
write 1 from 'P;P0.00'
write 1 from 'P;P#,##0'
write 1 from 'P;P#,##0.00'
write 1 from 'P;P#,##0_);;\(#,##0\)'
write 1 from 'P;P#,##0_);;[Red]\(#,##0\)'
write 1 from 'P;P#,##0.00_);;\(#,##0.00\)'
write 1 from 'P;P#,##0.00_);;[Red]\(#,##0.00\)'
write 1 from 'P;P"$"#,##0_);;\("$"#,##0\)'
write 1 from 'P;P"$"#,##0_);;[Red]\("$"#,##0\)'
write 1 from 'P;P"$"#,##0.00_);;\("$"#,##0.00\)'
write 1 from 'P;P"$"#,##0.00_);;[Red]\("$"#,##0.00\)'
write 1 from 'P;P0%'
write 1 from 'P;P0.00%'
write 1 from 'P;P0.00E+00'
write 1 from 'P;P##0.0E+0'
write 1 from 'P;P#\ ?/?'
write 1 from 'P;P#\ ??/??'
write 1 from 'P;Pm/d/yy'
write 1 from 'P;Pd\-mmm\-yy'
write 1 from 'P;Pd\-mmm'
write 1 from 'P;Pmmm\-yy'
write 1 from 'P;Ph:mm\ AM/PM'
write 1 from 'P;Ph:mm:ss\ AM/PM'
write 1 from 'P;Ph:mm'
write 1 from 'P;Ph:mm:ss'
write 1 from 'P;Pm/d/yy\ h:mm'
write 1 from 'P;Pmm:ss'
write 1 from 'P;Pmm:ss.0'
write 1 from 'P;P@'
write 1 from 'P;P[h]:mm:ss'
write 1 from 'P;P_("$"* #,##0_);;_("$"* \(#,##0\);;_("$"*
"-"_);;_(@_)'
write 1 from 'P;P_(* #,##0_);;_(* \(#,##0\);;_(* "-"_);;_(@_)'
write 1 from 'P;P_("$"* #,##0.00_);;_("$"* \(#,##0.00\);;_("$"*
"-"??_);;_(@_)'
write 1 from 'P;P_(* #,##0.00_);;_(* \(#,##0.00\);;_(* "-"??_);;_(@_)'
write 1 from 'P;FArial;M240'
write 1 from 'P;FArial;M240'
write 1 from 'P;FArial;M240'
write 1 from 'P;FArial;M240'
write 1 from 'P;EArial;M240'
write 1 from 'P;EArial;M240;SB'
write 1 from 'F;P0;DG0G8;M300'
write 1 from 'B;Y3273;X22;D0 0 3272 21' write 1 from
'O;L;D;V0;K47;G100 0.001' write 1 from 'F;W3 3 0'
write 1 from 'F;W5 5 0'
write 1 from 'F;W7 7 36'
write 1 from 'F;W8 8 0'
write 1 from 'F;W9 9 15'
write 1 from 'F;W10 11 10'
write 1 from 'F;W12 12 16'
write 1 from 'F;W13 13 0'
write 1 from 'F;W15 15 13'
write 1 from 'F;W16 16 0'
write 1 from 'F;W17 17 13'
write 1 from 'F;W18 18 11'
write 1 from 'F;W19 20 0'
write 1 from 'F;W22 22 13'
write 1 from 'F;P35;FF2G;C9'
write 1 from 'F;P1;FI0G;C11'
write 1 from 'F;P35;FF2G;C12'
write 1 from 'F;P35;FF2G;C15'
write 1 from 'F;P7;FF2G;C17'
write 1 from 'F;P35;FF2G;C22'
write 1 from 'F;P0;FG0C;SDBM6;M315;R1'
write 1 from 'NN;NPrint_Titles;ER1'
write 1 from 'F;Y1;X1'
write 1 from 'C;K"BUSUNIT"'
write 1 from 'F;X2'
write 1 from 'C;K"ACCNT"'
write 1 from 'F;X3'
write 1 from 'C;K"ACCTDESC"'
write 1 from 'F;X4'
write 1 from 'C;K"INVID"'
write 1 from 'F;X5'
write 1 from 'C;K"UOMSTD"'
write 1 from 'F;X6'
write 1 from 'C;K"UOM"'
write 1 from 'F;X7'
write 1 from 'C;K"ITEMDESC"'
write 1 from 'F;X8'
write 1 from 'C;K"COUNT"'
write 1 from 'F;P35;FF2C;X9'
write 1 from 'C;K"PUTWY"'
write 1 from 'F;X10'
write 1 from 'C;K"CNT_PTWY"'
write 1 from 'F;P1;FI0C;X11'
write 1 from 'C;K"QTY_PTWY"'
write 1 from 'F;P35;FF2C;X12'
write 1 from 'C;K"ISSUES"'
write 1 from 'F;X13'
write 1 from 'C;K"CNT_ISS"'
write 1 from 'F;X14'
write 1 from 'C;K"QTY_ISS"'
write 1 from 'F;P35;FF2C;X15'
write 1 from 'C;K"ADJ"'
write 1 from 'F;X16'
write 1 from 'C;K"CNT_ADJ"'
write 1 from 'F;P7;FF2C;X17'
write 1 from 'C;K"ADJ_DECR"'
write 1 from 'F;X18'
write 1 from 'C;K"ADJ_INCR"'
write 1 from 'F;X19'
write 1 from 'C;K"OTHER"'
write 1 from 'F;X20'
write 1 from 'C;K"CNT_OTHER"'
write 1 from 'F;X21'
write 1 from 'C;K"AGG_QTY"'
write 1 from 'F;P35;FF2C;X22'
write 1 from 'C;K"AGG_AMT"'
write 1 from 'C;Y2;X1;K" "'
END-PROCEDURE
! WRITE lines
BEGIN-PROCEDURE WRITE-LINES
let #Y = #Y + 1
move #Y to $Y
move #COUNT to $COUNT
move #PUTWY to $PUTWY
move #CNT_PTWY to $CNT_PTWY
move #QTY_PTWY to $QTY_PTWY
move #ISSUES to $ISSUES
move #CNT_ISS to $CNT_ISS
move #QTY_ISS to $QTY_ISS
move #ADJ to $ADJ move #CNT_ADJ to $CNT_ADJ
move #ADJ_DECR to $ADJ_DECR
move #ADJ_INCR to $ADJ_INCR
move #OTHER to $OTHER
move #CNT_OTHER to $CNT_OTHER
let #this_item_qty = #QTY_PTWY - #QTY_ISS + #ADJ_DECR + #ADJ_INCR
let #this_item_amt = #PUTWY + #ISSUES + #ADJ + #OTHER
move #this_item_qty to $this_item_qty
move #this_item_amt to $this_item_amt
let #tot_COUNT = #tot_COUNT + #COUNT
let #tot_PUTWY = #tot_PUTWY + #PUTWY
let #tot_CNT_PTWY = #tot_CNT_PTWY + #CNT_PTWY
let #tot_QTY_PTWY = #tot_QTY_PTWY + #QTY_PTWY
let #tot_ISSUES = #tot_ISSUES + #ISSUES
let #tot_CNT_ISS = #tot_CNT_ISS + #CNT_ISS
let #tot_QTY_ISS = #tot_QTY_ISS + #QTY_ISS
let #tot_ADJ = #tot_ADJ + #ADJ
let #tot_CNT_ADJ = #tot_CNT_ADJ + #CNT_ADJ
let #tot_ADJ_DECR = #tot_ADJ_DECR + #ADJ_DECR
let #tot_ADJ_INCR = #tot_ADJ_INCR + #ADJ_INCR
let #tot_OTHER = #tot_OTHER + #OTHER
let #tot_CNT_OTHER = #tot_CNT_OTHER + #CNT_OTHER
let #tot_item_qty = #tot_item_qty + #this_item_qty
let #tot_item_amt = #tot_item_amt + #this_item_amt
let $BUSUNIT_SLK = 'C;Y' || $Y || ';K"' || $BUSUNIT || '"'
let $ACCNT_SLK = 'C;X2;' || $ACCNT
let $ACCTDESC_SLK = 'C;X3;K"' || $ACCTDESC || '"'
let $INVID_SLK = 'C;X4;K' || $INVID
let $UOMSTD_SLK = 'C;X5;K"' || $UOMSTD || '"'
let $UOMUOM_SLK = 'C;X6;K"' || $UOMUOM || '"'
let $ITEMDESC_SLK = 'C;X7;K"' || $ITEMDESC || '"'
let $COUNT_SLK = 'C;X8;K' || $COUNT
let $PUTWY_SLK = 'C;X9;K' || $PUTWY
let $CNT_PTWY_SLK = 'C;X10;K' || $CNT_PTWY
let $QTY_PTWY_SLK = 'C;X11;K' || $QTY_PTWY
let $ISSUES_SLK = 'C;X12;K' || $ISSUES
let $CNT_ISS_SLK = 'C;X13;K' || $CNT_ISS
let $QTY_ISS_SLK = 'C;X14;K' || $QTY_ISS
let $ADJ_SLK = 'C;X15;K || $ADJ
let $CNT_ADJ_SLK = 'C;X16;K' || $CNT_ADJ
let $ADJ_DECR_SLK = 'C;X17;K' || $ADJ_DECR
let $ADJ_INCR_SLK = 'C;X18;K' || $ADJ_INCR
let $OTHER_SLK = 'C;X19;K' || $OTHER
let $CNT_OTHER_SLK = 'C;X20;K' || $CNT_OTHER
let $this_item_qty_SLK = 'C;X21;K' || $this_item_qty
let $this_item_amt_SLK = 'C;X22;K' || $this_item_amt
write 1 from $BUSUNIT_SLK
write 1 from $ACCNT_SLK
write 1 from $ACCTDESC_SLK
write 1 from $INVID_SLK
write 1 from $UOMSTD_SLK
write 1 from $UOMUOM_SLK
write 1 from $ITEMDESC_SLK
write 1 from $COUNT_SLK
write 1 from $PUTWY_SLK
write 1 from $CNT_PTWY_SLK
write 1 from $QTY_PTWY_SLK
write 1 from $ISSUES_SLK
write 1 from $CNT_ISS_SLK
write 1 from $QTY_ISS_SLK
write 1 from $ADJ_SLK
write 1 from $CNT_ADJ_SLK
write 1 from $ADJ_DECR_SLK
write 1 from $ADJ_INCR_SLK
write 1 from $OTHER_SLK
write 1 from $CNT_OTHER_SLK
write 1 from $this_item_qty_SLK
write 1 from $this_item_amt_SLK
END-PROCEDURE
begin-procedure write-totals
let #Y = #Y + 1
move #Y to $Y
move #tot_COUNT to $tot_COUNT
move #tot_PUTWY to $tot_PUTWY
move #tot_CNT_PTWY to $tot_CNT_PTWY
move #tot_QTY_PTWY to $tot_QTY_PTWY
move #tot_ISSUES to $tot_ISSUES
move #tot_CNT_ISS to $tot_CNT_ISS
move #tot_QTY_ISS to $tot_QTY_ISS
move #tot_ADJ to $tot_ADJ
move #tot_CNT_ADJ to $tot_CNT_ADJ
move #tot_ADJ_DECR to $tot_ADJ_DECR
move #tot_ADJ_INCR to $tot_ADJ_INCR
move #tot_OTHER to $tot_OTHER
move #tot_CNT_OTHER to $tot_CNT_OTHER
move #tot_item_qty to $tot_item_qty
move #tot_item_amt to $tot_item_amt
let $firsttotcell = 'C;Y' || Y$ || ';X1;K" "'
let $secontotcell = 'C;X2;K" "'
let $thirdtotcell = 'C;X3;K" "'
let $fourttotcell = 'C;X4;K" "'
let $fifthtotcell = 'C;X5;K" "'
let $sixthtotcell = 'C;X6;K" "'
let $seventotcell = 'C;X7;K" "'
let $tot_COUNT_SLK = 'C;X8;K' || $tot_COUNT
let $tot_PUTWY_SLK = 'C;X9;K' || $tot_PUTWY
let $tot_CNT_PTWY_SLK = 'C;X10;K' || $tot_CNT_PTWY
let $tot_QTY_PTWY_SLK = 'C;X11;K' || $tot_QTY_PTWY
let $tot_ISSUES_SLK = 'C;X12;K' || $tot_ISSUES
let $tot_CNT_ISS_SLK = 'C;X13;K' || $tot_CNT_ISS
let $tot_QTY_ISS_SLK = 'C;X14;K' || $tot_QTY_ISS
let $tot_ADJ_SLK = 'C;X15;K' || $tot_ADJ
let $tot_CNT_ADJ_SLK = 'C;X16;K' || $tot_CNT_ADJ
let $tot_ADJ_DECR_SLK = 'C;X17;K' || $tot_ADJ_DECR
let $tot_ADJ_INCR_SLK = 'C;X18;K' || $tot_ADJ_INCR
let $tot_OTHER_SLK = 'C;X19;K' || $tot_OTHER
let $tot_CNT_OTHER_SLK = 'C;X20;K'|| $tot_CNT_OTHER
let $tot_item_qty_SLK = 'C;X21;K' || $tot_item_qty
let $tot_item_amt_SLK = 'C;X22;K' || $tot_item_amt
write 1 from $firsttotcell
write 1 from $secontotcell
write 1 from $thirdtotcell
write 1 from $fourttotcell
write 1 from $fifthtotcell
write 1 from $sixthtotcell
write 1 from $seventotcell
write 1 from $tot_COUNT_SLK
write 1 from $tot_PUTWY_SLK
write 1 from $tot_CNT_PTWY_SLK
write 1 from $tot_QTY_PTWY_SLK
write 1 from $tot_ISSUES_SLK
write 1 from $tot_CNT_ISS_SLK
write 1 from $tot_QTY_ISS_SLK
write 1 from $tot_ADJ_SLK
write 1 from $tot_CNT_ADJ_SLK
write 1 from $tot_ADJ_DECR_SLK
write 1 from $tot_ADJ_INCR_SLK
write 1 from $tot_OTHER_SLK
write 1 from $tot_CNT_OTHER_SLK write 1 from
$tot_item_qty_SLK
write 1 from $tot_item_amt_SLK
write 1 from 'E'
end-procedure
--------------------------------------------------------------------
Emil Galicinao <e.galicinao@MOTOROLA.COM>@list.iex.net> on 06/29/2001
> 06:27:01 AM
>
> Please respond to sqr-users@list.iex.net >
> Sent by: "Discussion of SQR, Brio Technology's
database > reporting language" <SQR-USERS@list.iex.net>
>
> To: SQR-USERS@list.iex.net
> cc:
>
> Subject: How can SQR generate an excel file with numeric data being
> displayed as a string?
>
> Hi Everyone,
>
> Does anybody know how to convert a numeric data into a string such
that > when I generate the report in excel, that data will no longer
be treated > as numeric? Right now, when a numeric data is more than
11 digits
> long, excel usually convert the data into an exponential form which
is > something Im trying to avoid.
>
> An example would be:
> The number 123456789012 becomes 1.23457E+11 when viewed from excel.
>
> I was thinking of appending a special character on that numeric data
but > unfortunately, that special charater also appear in the report.
>
> Any suggestions?
>
> Best Regards,
> Emil
-----------------------------------------------------
Confidentiality Notice: This e-mail transmission
may contain confidential or legally privileged
information that is intended only for the individual
or entity named in the e-mail address. If you are not
the intended recipient, you are hereby notified that
any disclosure, copying, distribution, or reliance
upon the contents of this e-mail is strictly prohibited.
If you have received this e-mail transmission in error,
please reply to the sender, so that we can arrange
for proper delivery, and then please delete the message
from your inbox. Thank you.