TOTALS IN ENQUIRY
- AMMAR.FAIDI
- Topic Author
- Offline
- Elite Member
- Posts: 266
- Thank you received: 13
how can i get the totals for sum of accounts per customer , e.g assume that i have 10 customers and for each one i have 3 accounts , how can i get the balance total for each of the customers in an enquiry
thnx
Please Log in or Create an account to join the conversation.
- jpb
- Offline
- Moderator
- TAFj-R20 - 'unix'
- Posts: 2854
- Thank you received: 649
one line per customer showing only the total
or
list all accounts with its balance and a total underneath
total in local currency ?
Please Log in or Create an account to join the conversation.
- AMMAR.FAIDI
- Topic Author
- Offline
- Elite Member
- Posts: 266
- Thank you received: 13
Please Log in or Create an account to join the conversation.
- jpb
- Offline
- Moderator
- TAFj-R20 - 'unix'
- Posts: 2854
- Thank you received: 649
ENQUIRY........... ACC.SUM.BY.CUS
-------------------------------------
1 PAGE.SIZE ........ 4,19
2 FILE.NAME......... ACCOUNT
3. 1 FIXED.SELECTION CUSTOMER NE ""
4. 1 FIXED.SORT..... CUSTOMER
14. 1 FIELD.NAME..... CUSTOMER
15. 1. 1 OPERATION... CUSTOMER
17. 1 LENGTH.MASK.... 10R
35. 1 SINGLE.MULTI... S
14. 2 FIELD.NAME..... LCY
15. 2. 1 OPERATION... "USD"
14. 3 FIELD.NAME..... BREAK.CUS
15. 3. 1 OPERATION... B C CUSTOMER
14. 4 FIELD.NAME..... DISP.CUS
15. 4. 1 OPERATION... F CUSTOMER
16. 4 COLUMN......... 3
17. 4 LENGTH.MASK.... 10R
21. 4 DISPLAY.BREAK.. BREAK.CUS
14. 5 FIELD.NAME..... CUS.NAME
15. 5. 1 OPERATION... F CUSTOMER
16. 5 COLUMN......... 12
17. 5 LENGTH.MASK.... 35L
18. 5. 1 CONVERSION.. L CUSTOMER,SHORT.NAME
20. 5 TYPE........... L
21. 5 DISPLAY.BREAK.. BREAK.CUS
14. 6 FIELD.NAME..... CURRENCY
15. 6. 1 OPERATION... CURRENCY
17. 6 LENGTH.MASK.... 3L
35. 6 SINGLE.MULTI... S
14. 7 FIELD.NAME..... ONL.ACT.BAL.CALC
15. 7. 1 OPERATION... ONLINE.ACTUAL.BAL
17. 7 LENGTH.MASK.... 19R
35. 7 SINGLE.MULTI... S
14. 8 FIELD.NAME..... ONL.ACT.BAL
15. 8. 1 OPERATION... F ONL.ACT.BAL.CALC
17. 8 LENGTH.MASK.... 19R
20. 8 TYPE........... CCY CURRENCY
35. 8 SINGLE.MULTI... S
14. 9 FIELD.NAME..... CCY.BAL
15. 9. 1 OPERATION... CALC CURRENCY : ONL.ACT.BAL.CALC
14.10 FIELD.NAME..... ONL.BAL.LCY
15.10. 1 OPERATION... F CCY.BAL
17.10 LENGTH.MASK.... 19R
18.10. 1 CONVERSION.. @ E.EQUIV.LCCY
20.10 TYPE........... CCY LCY
14.11 FIELD.NAME..... SUM.BAL.LCY
15.11. 1 OPERATION... TOTAL ONL.BAL.LCY
16.11 COLUMN......... 41
17.11 LENGTH.MASK.... 19R
20.11 TYPE........... CCY LCY
21.11 DISPLAY.BREAK.. BREAK.CUS
SUBROUTINE E.EQUIV.LCCY
****************************************************************
$INSERT I_EQUATE
$INSERT I_COMMON
$INSERT I_ENQUIRY.COMMON
****************************************************************
* ccys & amts
CCY1=O.DATA[1,3]
AMT1=O.DATA[4,100]
CCY2 = LCCY
AMT2 = ""
RATE = ""
* convert
IF CCY1=CCY2 THEN
AMT2=AMT1
END ELSE
CALL EXCHRATE("1",CCY1,AMT1,CCY2,AMT2,"",RATE,"","",RET.CODE)
IF ETEXT THEN AMT2 = ""
END
* result
O.DATA = AMT2
RETURN
****************************************************************
END
Please Log in or Create an account to join the conversation.
- AMMAR.FAIDI
- Topic Author
- Offline
- Elite Member
- Posts: 266
- Thank you received: 13
it worked fine but i have one issue that is the totals are accumulative how can i zeroise it after displaying each total
B.R
Please Log in or Create an account to join the conversation.
- jpb
- Offline
- Moderator
- TAFj-R20 - 'unix'
- Posts: 2854
- Thank you received: 649
in my env. with R08 and desktop the example is working OK, the TOTAL is cleared when the customer changes (as defined in 21.11).
I haven't check with R11 and browser...
Please Log in or Create an account to join the conversation.
- AMMAR.FAIDI
- Topic Author
- Offline
- Elite Member
- Posts: 266
- Thank you received: 13
thnx
Please Log in or Create an account to join the conversation.
- CryptoGuy
- Offline
- Senior Member
- Posts: 46
- Thank you received: 3
I was looking for such enquiry, and I came through this old post :), I'm trying to do something similar to show total balances of categories using CAL table, so I want one line per category and show total balance. I created an enquiry and it shows total balance for each category but it displays as many lines as the number of consol keys with this category.
What am I doing wrong? How to show only one line per category?
Thanks.
Please Log in or Create an account to join the conversation.
- silvergem
- Offline
- Elite Member
- TAFC|R12/R13, TAFJ|R20
- Posts: 246
- Thank you received: 19
Please Log in or Create an account to join the conversation.
- CryptoGuy
- Offline
- Senior Member
- Posts: 46
- Thank you received: 3
Please Log in or Create an account to join the conversation.
- CryptoGuy
- Offline
- Senior Member
- Posts: 46
- Thank you received: 3
Page Size 4,19
File Name CONSOLIDATE.ASST.LIAB
Fixed Selection.1 BALANCE NE ""
Fixed Sort.1 CATEGORY
Field Name.1 CAT
Operation.1.1 CATEGORY
Column.1 1
Length Mask.1 35L
Single Multi.1 S
Field Name.2 BREAK.CAT
Operation.2.1 B C CAT
Column.2 3
Single Multi.2 S
Field Name.3 DISP.CAT
Operation.3.1 F CAT
Column.3 4
Display Break.3 BREAK.CAT
Single Multi.3 S
Field Name.4 BAL
Operation.4.1 BALANCE
Column.4 5
Length Mask.4 19R
Single Multi.4 S
Field Name.5 TOTAL.BAL
Operation.5.1 TOTAL BAL
Column.5 6
Length Mask.5 19R
Display Break.5 BREAK.CAT
Single Multi.5 S
Page Fields 2 1 4
Multi Fields 0
Break Fields 1 2
Process Breaks 2 3 5
Total Fields 1 5
Product AC Accounts
Curr No 21
and here is how the output looks :
CAT BREAK.CAT DISP.CAT BAL TOTAL.BAL
10001 '-100'
10001 '-1305'
10001 '-1200'
10001 '-2605'
1001 '-186.4'
1001 '-2800'
1001 '-30197.991'
1001 4194.5 -180
1001 '-34538295.157'
1001 '-4970'
1001 40306.207
1001 '-352'
1001 '-365'
1001 300
1001 108.888
1001 '-137'
1001 1250
1001 '-34535338.453'
Any ideas ?
Please Log in or Create an account to join the conversation.
- silvergem
- Offline
- Elite Member
- TAFC|R12/R13, TAFJ|R20
- Posts: 246
- Thank you received: 19
Please Log in or Create an account to join the conversation.
- CryptoGuy
- Offline
- Senior Member
- Posts: 46
- Thank you received: 3
Please Log in or Create an account to join the conversation.
- silvergem
- Offline
- Elite Member
- TAFC|R12/R13, TAFJ|R20
- Posts: 246
- Thank you received: 19
COLUMN
Specifies the screen co-ordinates to display the enquiry field.
Defines the exact or relative screen position to display the field value. The following formats are accepted:
Column Action
'' Null - field will not be displayed.
CC Field will be displayed at column CC on the current line.
CC,LL Field will be displayed in column CC on line LL. (exact positioning, normally used for variable heading data).
cc,+nn Field will be displayed in column cc on the current line + nn lines (also supports -nn). Note that when a multi/sub value field is displayed. Each item will be displayed +nn lines from the last.
cc,+nnM Field will be displayed in column cc on the current line + nn lines (also supports -nn). When a multi/sub value field is displayed ONLY the first item will be displayed +nn lines, each subsequent item will be displayed on the next line.
P Used only in conjunction with break instructions - performs page 'throw' when break condition met.
Validation Rules
5 type A (alphanumeric) characters.
Please Log in or Create an account to join the conversation.
- DUBLIN
- Offline
- Elite Member
- Posts: 202
- Thank you received: 29
You need to remove column number in enquiry fields CAT, BREAK.CAT @ BAL i.e.
Field Name.1 CAT
Operation.1.1 CATEGORY
Column.1 1<---- make it blank
Length Mask.1 35L
Single Multi.1 S
Field Name.2 BREAK.CAT
Operation.2.1 B C CAT
Column.2 3 <---- make it blank .
Single Multi.2 S
and in here :
Field Name.4 BAL
Operation.4.1 BALANCE
Column.4 5 <---- make it blank
Length Mask.4 19R
Single Multi.4 S
It should give you a Total per Category.
But... you could have lines with the same CATEGORY but different currencies . Then your enquiry would show wrong numbers as you will sum "apples and oranges".
Field BALANCE holds value in original ccy.
May be you should use field that holds balance in LOCAL currency instead...
Please Log in or Create an account to join the conversation.