Account Transaction Details - Order By

V5 Question
1

Is there a way I can order account transaction details by date in an ascending or descending order? Its a bit of a mess to read it at the moment

image

2

Hello @Posflow,

You can use below report code group and adjust it according to your needs;

[Customer Account Payment Details:2,2,4,2,2]
>Cash Transactions
{REPORT ACCOUNT TRANSACTION DETAILS:T.Date.asc,T.Time,T.TransactionName,T.Amount:(ATT=Customer Cash Payment)}
>Total|||{REPORT ACCOUNT TRANSACTION DETAILS:T.Amount.Sum:(ATT=Customer Cash Payment)}


>Credit Transactions
{REPORT ACCOUNT TRANSACTION DETAILS:T.Date.asc,T.Time,T.TransactionName,T.Amount:(ATT=Customer Credit Card Payment)}
>Total|||{REPORT ACCOUNT TRANSACTION DETAILS:T.Amount.Sum:(ATT=Customer Credit Card Payment)}
2 Likes
3

Hey @Nizam I couldnt find this on knowledge base, can you please update it? Also, what expressions can I use here?

Is there a target account expression like (T.Target=“xyz”)?

4

Hello @Posflow,

That is a custom report design therefore it’s not published on the knowledge base.

You can check the below image to see what expressions you can use for this usage.(including Target Account as well)

T.Target-01
T.Target-011920×1027 181 KB

5

Hi @Nizam, I think we are misunderstanding each other, I am asking if you can update knowledge base so that the syntax for this is available in docs because
{REPORT ACCOUNT TRANSACTION DETAILS:} syntax is not available in Reports section of SambaPOS Knowledge base docs.

image
I want to constrain (ATT=Cash Expense) AND (T.Target=Staff Food) is there an expression for Target account or source account?

6

Hello @Posflow,

Indeed there is a document that includes mentioned report tag;

https://kb.sambapos.com/en/9-1-5-creating-accounting-reports-with-custom-reporting-tags/

7

Ive seen this one, I mean {REPORT ACCOUNT TRANSACTION DETAILS:} specifically, this one is not mentioned anywhere (at least not in easily found topis on the forum), there is no information about what other expression it has available other than (ATT=)

8

Hey @Nizam, I created this script that works the same but I have the control over date/time format and target account

In its essence its mimicking this

{ACCOUNT TRANSACTION DETAILS:Expense Cash Payment:Staff Food}
{ACCOUNT TRANSACTION DETAILS:Expense Bank Payment:Staff Food}}
{ACCOUNT TRANSACTION DETAILS:Cash Expense by Owner Transaction:Staff Food}}

Works the same way, you just pass down the parameters to the script.

DECLARE @Start DATETIME = CONVERT(VARCHAR(25), '{Start}', 126);
DECLARE @End DATETIME = CONVERT(VARCHAR(25), '{End}', 126);

DECLARE @ATT1 NVARCHAR(50)= '@1';
DECLARE @ATT2 NVARCHAR(50)= '@2';
DECLARE @ATT3 NVARCHAR(50)= '@3';
DECLARE @A1 NVARCHAR(50)= '@4';



SELECT   
		
	FORMAT(atv.Date,'MMM-dd HH:mm') as Date,
	atv.Name,
    atv.Debit AS Amount


  FROM AccountTransactionValues atv

  INNER JOIN AccountTransactionDocuments atd ON atv.AccountTransactionDocumentId = atd.Id
  INNER JOIN AccountTransactionDocumentTypes atdt ON atd.DocumentTypeId = atdt.Id
  INNER JOIN AccountTransactionDocumentTypeAccountTransactionTypes att ON atd.DocumentTypeId = att.AccountTransactionDocumentType_Id
  INNER JOIN AccountTransactionTypes attt ON attt.Id = att.AccountTransactionType_Id
  INNER JOIN Accounts a ON a.Id=atv.AccountId

  WHERE 
  atv.Date >= @Start
  AND
  atv.Date <= @End
  AND
  ( attt.Name = @ATT1 OR attt.Name= @ATT2 OR attt.Name = @ATT3 )
  AND
  a.Name=@A1
  AND
  atv.Debit > 0



  ORDER BY FORMAT(atv.Date,'MMM-dd HH:mm') ASC

In the reports you pass the parameters of your Account Transaction Type names the same way, and last one is the account name

Additionally you can put all 3 initial parameters the same name if you dont have 3 unique transaction types, it wont mess up the report.

[STAFF FOOD-EXPENSE:1,2, 1]
@@opexpenses:Expense Cash Payment,Expense Bank Payment,Cash Expense by Owner Transaction,Staff Food

>Total||[=F((TN('{ACCOUNT TOTAL:Staff Food}')),'#,#0.00')]

image

3 Likes