CCS Point of Sale
You are here: start » guides » reporting » custom_report
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
guides:reporting:custom_report [2016/07/05 18:32] markph [Identify your Patient's TypeID] |
guides:reporting:custom_report [2018/12/23 12:11] (current) |
||
|---|---|---|---|
| Line 170: | Line 170: | ||
| Make sure that this report is pulling the right type of customers, your patients. Change the ClientType in the statement below to include the proper ID from your client type table. | Make sure that this report is pulling the right type of customers, your patients. Change the ClientType in the statement below to include the proper ID from your client type table. | ||
| Run this statement from the Pivot Reporting module:\\ | Run this statement from the Pivot Reporting module:\\ | ||
| - | Select * FROM tblClients\\ | + | Select * FROM tblClientType\\ |
| Then Identify which ClientTypeID is the one that is associated with your Patients. We have had people name their patient types different names, so this variable is something you'll need to verify.\\ | Then Identify which ClientTypeID is the one that is associated with your Patients. We have had people name their patient types different names, so this variable is something you'll need to verify.\\ | ||
| Line 176: | Line 176: | ||
| ==== Configuration==== | ==== Configuration==== | ||
| - | Watch this video to get started and understand the following things you'll need to do and navigate. It will show you how to use the item search section to update and modify items like you will need to. | + | Watch this video to get started and understand the following things you'll need to do and navigate. It will show you how to use the item search section to update and modify items like you will need to.\\ |
| - | https://www.youtube.com/watch?v=jqSnH5Yv7gc | + | [[https://www.youtube.com/watch?v=jqSnH5Yv7gc|Product Search and Update|]] |
| 1. Office - (Main toolbar) - Inventory - Product Search | 1. Office - (Main toolbar) - Inventory - Product Search | ||
| Line 186: | Line 186: | ||
| SELECT [ClAccountNumber],Subcategory.ProductName,UnitName,SUM(Quantity) \\ | SELECT [ClAccountNumber],Subcategory.ProductName,UnitName,SUM(Quantity) \\ | ||
| - | FROM tblClients \\ | + | FROM [dbo].[tblProductOrderDetails] AS Details \\ |
| - | + | ||
| - | LEFT JOIN [dbo].[tblProductOrderDetails] AS Details \\ | + | |
| - | ON tblClients.[ClientID]=Details.[ClientID] \\ | + | |
| LEFT JOIN [dbo].[tblProducts] AS Products \\ | LEFT JOIN [dbo].[tblProducts] AS Products \\ | ||
| - | ON Products.ProductSubtypeID = Details.ProductID \\ | + | ON Products.ProductID = Details.ProductID \\ |
| LEFT JOIN [dbo].[tblProducts] AS Subcategory \\ | LEFT JOIN [dbo].[tblProducts] AS Subcategory \\ | ||
| ON Subcategory.ProductID = Products.[productsubtypeID] \\ | ON Subcategory.ProductID = Products.[productsubtypeID] \\ | ||
| + | |||
| + | LEFT JOIN tblClients \\ | ||
| + | ON tblClients.[ClientID]=Details.[ClientID] \\ | ||
| WHERE [ClientType]=10 \\ | WHERE [ClientType]=10 \\ | ||
| AND [CompletionDate] BETWEEN '06/01/2016' AND '06/30/2016' \\ | AND [CompletionDate] BETWEEN '06/01/2016' AND '06/30/2016' \\ | ||
| - | GROUP BY [clAccountNumber],Subcategory.ProductName,UnitName \\ | + | GROUP BY [ClAccountNumber],Subcategory.ProductName,UnitName \\ |
| - | ORDER BY [ClAccountNumber],Subcategory.ProductName,[UnitName] \\ | + | ORDER BY [ClAccountNumber],Subcategory.ProductName,[UnitName]\\ |
| + | ===== Quarterly Sales Taxes Grouped By Item Type ===== | ||
| + | |||
| + | Change the date range:\\ | ||
| + | |||
| + | SELECT SUM(LineTotal),\\ | ||
| + | Subcategory.ProductName,UnitName,SUM(Quantity), \\ | ||
| + | Sum(UnitPrice*Quantity) AS NetSales, \\ | ||
| + | SUM(LineTotal) AS TaxTotal, \\ | ||
| + | Sum(UnitPrice*Quantity) + SUM(LineTotal) AS GrossSales\\ | ||
| + | FROM [dbo].[tblProductOrderDetails] AS Details\\ | ||
| + | LEFT JOIN [dbo].[tblProducts] AS Products \\ | ||
| + | ON Products.ProductID = Details.ProductID \\ | ||
| + | LEFT JOIN [dbo].[tblProducts] AS Subcategory \\ | ||
| + | ON Subcategory.ProductID = Products.[productsubtypeID] \\ | ||
| + | WHERE\\ | ||
| + | Details.ServiceType='Rec Sale' \\ | ||
| + | AND [LineTotal]<>0\\ | ||
| + | AND Details.[CompletionDate] BETWEEN '07/24/2016' AND '07/25/2016'\\ | ||
| + | GROUP BY Subcategory.ProductName,UnitName \\ | ||
| + | ORDER BY Subcategory.ProductName,[UnitName]\\ | ||
Trace:
