× Discuss on Template programming, jBASE programming, Enquiries, No-File enquiry, Enquiry routines, Version, Version routines, Menus, Abbriviations, Creating local reference fields, Fast path enquiries, Creating charts and graphs, Generating Reports, Deal slips, Straight through processing, Multi Company and Multi Book setup, Tabbed screens, Composite Screens, T24 API, etc...

REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

Start
Prev
1
Next
End

shelistot@gmail.com
Topic Author
Offline
New Member

6 months 1 week ago #24476 by shelistot@gmail.com

REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits was created by shelistot@gmail.com

Friends, how can we create a routine that generates a report that shows the following

ACCOUNT ID, TOTAL DEBIT TURNOVER FOR A GIVEN MONTH, TOTAL CREDIT TURNOVER FOR A GIVEN MONTH, ACCOUNT CATEGORY, YEAR-MONTH

This would likely be in ACCT.ACTIVITY and FBNK.ACCOUNT (for getting the account CATEGORY and the @ID)

Sample Output Required;
1000215793,32656.00, 5656.00, 1001, 2024-05
1000215794,32656.00, 5656.00, 1001, 2024-05
1000215795,32656.00, 5656.00, 1001, 2024-05
1000215796,32656.00, 5656.00, 1001, 2024-05

Please Log in or Create an account to join the conversation.

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

6 months 1 week ago - 6 months 1 week ago #24477 by VK

Replied by VK on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

Hi
TAFC or TAFJ?

quite easy under TAFC... one short jQL select (with one I-desc and 1 J-desc in SS).

Cheers
VK

Last edit: 6 months 1 week ago by VK.

Please Log in or Create an account to join the conversation.

shelistot@gmail.com
Topic Author
Offline
New Member

6 months 1 week ago #24478 by shelistot@gmail.com

Replied by shelistot@gmail.com on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

This is TAFC @VK. How can we achieve it, quite stuck. Please share a sample RTN

Please Log in or Create an account to join the conversation.

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

6 months 1 week ago #24479 by VK

Replied by VK on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

SS:

  15. 1 USR.FIELD.NAME. ACCTID
  16. 1 USR.TYPE....... I
  17. 1. 1 USR.FIELD.NO FIELD(@ID, '-', 1)
  20. 1 USR.DISPLAY.FMT 16L
  24. 1 USR.SINGLE.MULT S
  15. 2 USR.FIELD.NAME. ACCTCATG
  16. 2 USR.TYPE....... J
  17. 2. 1 USR.FIELD.NO ACCTID>ACCOUNT>CATEGORY
  20. 2 USR.DISPLAY.FMT 5L
  24. 2 USR.SINGLE.MULT S

jQL:

SELECT FBNK.ACCT.ACTIVITY WITH @ID LIKE ...-202405 SAVING EVAL "ACCTID:',':ABS(SUM(TURNOVER.DEBIT)):',':SUM(TURNOVER.CREDIT):',':ACCTCATG:',':FMT(FIELD(@ID, '-', 2), '####-##')"

SAVE-LIST report.txt

Cheers
VK

Please Log in or Create an account to join the conversation.

shelistot@gmail.com
Topic Author
Offline
New Member

6 months 1 week ago - 6 months 1 week ago #24480 by shelistot@gmail.com

Replied by shelistot@gmail.com on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

This is working well @VK, I have tried both options, using the JQL route, how can we calculate AVG(BK.BALANCE), this is not working as well as AVERAGE(BK.BALANCE).

Last edit: 6 months 1 week ago by shelistot@gmail.com. Reason: Typo on VK

Please Log in or Create an account to join the conversation.

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

6 months 1 week ago #24482 by VK

Replied by VK on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

Any examples?

Cheers
VK

Please Log in or Create an account to join the conversation.

shelistot@gmail.com
Topic Author
Offline
New Member

6 months 1 week ago #24483 by shelistot@gmail.com

Replied by shelistot@gmail.com on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

example 1

LIST FBNK.ACCT.ACTIVITY WITH @ID LIKE ...-202405 SAVING EVAL "@ID:',':ABS(SUM(TURNOVER.DEBIT)):',':SUM(TURNOVER.CREDIT):'
,':SUM(BK.BALANCE)/COUNT(BK.BALANCE):',':FMT(FIELD(@ID, '-', 2), '####-##')"

example 2;

LIST FBNK.ACCT.ACTIVITY WITH @ID LIKE ...-202405 SAVING EVAL "@ID:',':ABS(SUM(TURNOVER.DEBIT)):',':SUM(TURNOVER.CREDIT):'
,':AVERAGE(BK.BALANCE):',':FMT(FIELD(@ID, '-', 2), '####-##')"

Please Log in or Create an account to join the conversation.

VK
Offline
Platinum Member
Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test

6 months 1 week ago - 6 months 1 week ago #24485 by VK

Replied by VK on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

Example 1: SAVING goes only with SELECT, not LIST. Also, instead of COUNT use DCOUNT:

SELECT FBNK.ACCT.ACTIVITY WITH @ID LIKE ...-202405 SAVING EVAL "@ID:',':ABS(SUM(TURNOVER.DEBIT)):',':SUM(TURNOVER.CREDIT):',':SUM(BK.BALANCE)/DCOUNT(BK.BALANCE,@VM):',':FMT(FIELD(@ID, '-', 2), '####-##')"

Example 2: There's no function AVERAGE(), that's a keyword:

LIST FBNK.ACCT.ACTIVITY WITH @ID LIKE ...-202405 AVERAGE BK.BALANCE

.. but it outputs average for all records...

It looks like you're guessing the syntax... Better proceed with some technical training (or hire someone for technical tasks) ... The task becomes more and more complicated and at some stage it might happen that jQL is unable to help you anymore... In such cases better choice is to use NOFILE enquiry.

Cheers
VK

Last edit: 6 months 1 week ago by VK.

Please Log in or Create an account to join the conversation.

shelistot@gmail.com
Topic Author
Offline
New Member

6 months 1 week ago #24486 by shelistot@gmail.com

Replied by shelistot@gmail.com on topic REPORT IN ACCT.ACTIVITY FOR GETTING TOTAL Debits & TOTAL Credits

Thank you VK for the sound advice!

Please Log in or Create an account to join the conversation.

Start
Prev
1
Next
End

Time to create page: 0.104 seconds