The possibilities are endless when you use SQL. Almost all my reporting is done with SQL rather than Custom Reports… this is because I am very familiar with SQL, so I really have not taken the time learn the Reporting that Emre built into SambaPOS… and (unfortunately?), I don’t intend to learn Custom Reporting, since I can do everything I want in SQL much quicker. I realize this is not the case for everyone and the tools that Emre has created for Reports is fantastic, so I am not detracting from that fact.
It is difficult to help you with the SQL unless you are extremely specific on what you want to see. It is not difficult to script/code, but since there is so much flexibility when it comes to provisioning data from the DB, the details need to be very clear. Emre has already hinted with some good links on exactly how this is done.
The first question is which detail is the main facet that you want to see. It sounds like you need [AccountTransactionValues] since this is where the “money” is stored. This detail is important, because we need to know where to start, so we can determine the JOIN properly. That is, we don’t join Transactions to Tickets, instead we join Tickets to Transactions… the order of the join can make a difference in many cases, especially when we start to think about whether we want an Outer Join as opposed to an Inner Join. If not done properly, you will miss some data.
Once we have that information, we start at the inside and work outward. We continue to join tables that contain data we need, and then possibly wrap that in an outer query to provide summary data (working outward).
You can see that our base element contains good information, but it is difficult to read. So we join other tables to get readable [Name] values instead of [Id] values. We built our join, but lets make it look nicer by omitting [Id] columns from the base, and “replace” them with the [Name] values from our joined tables…
USE [SambaPOS5JV]
GO
SELECT
tv.[Id]
,tv.[Name] as [TxValName]
--,tv.[AccountTransactionId]
,t.[Name] as [TxName]
--,tv.[AccountTransactionDocumentId]
,d.[Name] as [DocName]
--,tv.[AccountTypeId]
,at.[Name] as [AccountType]
--,tv.[AccountId]
,a.[Name] as [Account]
--,tv.[AccountTransactionTypeId]
,tt.[Name] as [TxType]
,tv.[Date]
,tv.[Debit]
,tv.[Credit]
,tv.[Exchange]
FROM [dbo].[AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] t on t.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
Notice we commented out the [Id] values from the base, and replaced them with the [Name] value of the joined tables, so now our result looks much better…
Ok, great. But what about the Ticket and the Entity?
