× 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

  • shelistot@gmail.com
  • Topic Author
  • Offline
  • New Member
  • New Member
More
1 month 3 weeks ago #24476 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
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test
More
1 month 3 weeks ago - 1 month 3 weeks ago #24477 by VK
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: 1 month 3 weeks ago by VK.

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

  • shelistot@gmail.com
  • Topic Author
  • Offline
  • New Member
  • New Member
More
1 month 3 weeks ago #24478 by shelistot@gmail.com
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
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test
More
1 month 3 weeks ago #24479 by VK
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
  • New Member
More
1 month 3 weeks ago - 1 month 3 weeks ago #24480 by shelistot@gmail.com
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: 1 month 3 weeks ago by shelistot@gmail.com. Reason: Typo on VK

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

  • VK
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test
More
1 month 3 weeks ago #24482 by VK
Any examples?
 

Cheers
VK

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

  • shelistot@gmail.com
  • Topic Author
  • Offline
  • New Member
  • New Member
More
1 month 3 weeks ago #24483 by shelistot@gmail.com
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
  • VK's Avatar
  • Offline
  • Platinum Member
  • Platinum Member
  • Globus:G9-G13|TAFC:R05-R23|TAFJ:R19,R23,R24:test
More
1 month 3 weeks ago - 1 month 3 weeks ago #24485 by VK
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: 1 month 3 weeks ago by VK.

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

  • shelistot@gmail.com
  • Topic Author
  • Offline
  • New Member
  • New Member
More
1 month 3 weeks ago #24486 by shelistot@gmail.com
Thank you VK for the sound advice!

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

Time to create page: 0.087 seconds