Custom Report Module - How to Add Payment Type & Department

Version 4
1

Hey Guys

Using the Custom Report Module, What code do you write in the script section of automation command.

Currently I am using the code provided in the tutorial which is:

select
T.TicketNumber,
min(convert(nvarchar,T.Date,105)) as Date,
min(convert(nvarchar,T.Date,108)) as Time,
COALESCE(min(case when TE.EntityTypeId = 2 then TE.EntityName else null end),’-’) as [Table Name],
min(T.TotalAmount) as TotalAmount
from Tickets as T
left outer join TicketEntities as TE on TE.Ticket_Id = T.Id
Where Date > ‘{Start}’ and Date < ‘{End}’
group by T.TicketNumber
order by Date,Time

This code shows the Ticket Number, Date, Time, Table Name and Total Amount as individual transactions which is perfect.

So how do I show 3 extra fields which include: Cash Amount, Credit Amount, Department

Thank you very much

2

To show payments you would need to join from the payments table based on ticket id HOWEVER this will result in duplicate ticket info if a ticket has multiple payments ie;

Ticket 1 - Ticket 1 info ticket 1 info - Cash Payment 1
Ticket 1 - Ticket 1 info ticket 1 info - Cash Payment 2
Ticket 1 - Ticket 1 info ticket 1 info - Card Payment 3

3

That’s fine, if you could show me what code to write, that would be much appreciated.

Also would I add the department?

4

Not familier with all the code you have used however this should give you the idea of how to add payments;

SELECT T.[TicketNumber], T.[Date], T.[TotalAmount], P.[Name], P.[Amount]
FROM Tickets T JOIN Payments P ON T.[Id] = P.[TicketId]
5

For department you have DepartmentId in tickets table so if you want that translated in to Department Name you would need to also SELECT D.[Name] from Departments table with JOIN [Departments] D ON T.[DepartmentId] = D.[Id]

6

You can also use the simpler report syntax for payments instead of SQL

http://www.sambapos.com/wiki/doku.php/custom_reporting

Although with version 4 you might be limited so SQL might be better alternative. Version 4 will never receive anymore updates.

7

Thanks Kendash for the alternative. I have been using Samba pos 4 for around a year now. I have always been able to find all the guides I need via the forum and programmed a lot of custom code into the pos system. Although because of time constraints for this particular project concerning the custom reports, I have requested help for the first time and I must say I am impressed by how fast you guys responded.

I will upgrading to version 5 shortly once I get the chance to test it

8

Thank you for your help JTRTech, appreciate it!

9

here is the report using Report Tags in v5… it might work in v4 im not sure.

[Sales by Ticket & Department:1, 1, 1,1,1,1,1,2]
>Ticket No|Date|Time|Table|Total Amount|Cash Amount|Credit Amount|Department
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,T.TotalAmount,PA.Cash,PA.Credit Card,T.Department:(TS.Status=Paid)}

Pasted image743×289 10.2 KB

EDIT: It appears to work just fine in v4

Pasted image1008×729 31.2 KB

1 Like
10

Wow that is perfect,but it doesn’t work for version 4.

I will upgrade in around 2 weeks to version 5. I have to figure out how to do in version 4 by tomorrow morning and then later I can use this code to produce the same result.

Normally I can figure these things out really easily but this time I got stuck so I had to come onto the forum as my last resort. I would appreciate any suggestions, thanks

11

This screenshot is it in v4. It works just fine.

Pasted image1010×761 33.4 KB

1 Like
12

Pasted image1876×891 51.4 KB

2 Likes
13

Oh it worked!! Thank you sooo much.

I was entering it into the script section but it worked by just putting it directly into the table syntax

14

Not sure what you mean by Script syntax and table syntax. You were trying to use report tax inside a SQL script?

BTW read this it will help your understanding bigtime.

http://www.sambapos.com/wiki/doku.php/custom_reporting

1 Like
15

Yes, haha, thanks again kendash

16

Wow, thanks for the link, exactly what I needed.

1 Like
17

Report tags are easier to use and much simpler as I demonstrated. For most cases they are just fine. SQL is much more flexible because it has endless possibilities constricted only by the SQL syntax. Report tags are coded by Emre to help solve most things without having to use long SQL code blocks.

I prefer Report Tags myself. In v5 Report Tags can be used for other clever things not just reports.

This explains most of the Report tags and their uses.

http://www.sambapos.com/wiki/doku.php/custom_reporting_tags

This shows some new abilities with v5.

http://www.sambapos.com/wiki/doku.php/custom_reporting_tags_for_v5

1 Like
18

Thanks Kendash will check it out on my way to the office tomorrow.

I have another question regarding the report. If I save the report, it is saved as an XPS file. Is there a way to save it as an (excel file) instead? like the csv builder does?

Or can I add a link below the table, that says (export sales data) ?

19

Cant you just open a CSV in excel?

20

Yes of course but when I save the report which is generated using the custom reports module. It saves as an XPS file. Which is similiar to a PDF format.

I wanted to know if it was possible to change the saving format to csv or to add a link below the report saying “export file” this would import the report as an csv rather than xps