CCS Point of Sale
You are here: start » guides » reporting » custom_report
Table of Contents
Custom Reports
To run these reports:
- From Office - (Main Toolbar) - Reporting - Pivot Reporting -
- Go to the Advanced Tab.
- Copy from the report selection below. You may need to adjust the 'Where' Sections. There is criteria that can be input such as names, dates and numbers. These should be properly formatted. The Report Statement include example criteria.
- Paste the statement into the advanced text area.
- Press the button 'Open Report From Statement'
People Who's Main 'Follow Up' date is X Days out
Replace [X] with the number of days you wish to filter.
SELECT clLastName as LastName, clFirstName as FirstName,clPhone as Phone,
clAccountNumber as AccountNumber,DateDiff(d, FollowUp, GetDate()) as DaysOut
FROM tblClients
WHERE DateAdd(d ,[x],FollowUp)>=60 AND FollowUp IS NOT NULL
ORDER BY clLastName,clFirstName,clAccountNumber
Result Example:
Smith | John | 777-777-8888 | 8481 | 30 |
Smith | Mark | 777-555-4499 | 8482 | 14 |
Payments on Receivables
SELECT AmountPaid,Payments.OrderDate,Payments.CompletionDate,PaymentType.TenderType,
Coalesce(Clients.clLastName, ) + ', ' + Coalesce(Clients.clFirstName,
),Payments.OrderID
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0;
Result Example:
300.00 | 2012-03-08 16:14:10.000 | 2012-03-08 16:14:10.000 | Cash | —, Karen, | 65 |
125.00 | 2012-05-15 17:07:09.000 | 2012-05-15 17:07:09.000 | MC | —, Tim, | 90 |
50.00 | 2012-02-16 16:50:13.000 | 2012-02-16 16:50:13.000 | Visa | —, Joe, | 100 |
150.00 | 2012-02-01 14:31:02.000 | 2012-02-01 14:31:02.000 | Visa | —, Joe, | 30 |
75.59 | 2012-04-06 17:05:10.000 | 2012-04-06 17:05:10.000 | Visa | —, Michael | 501 |
50.00 | 2012-03-22 16:48:08.000 | 2012-03-22 16:48:08.000 | Cash | —, Michael | 501 |
50.00 | 2012-05-01 15:22:38.000 | 2012-05-01 15:22:38.000 | Visa | —, Michael | 501 |
100.00 | 2012-03-13 17:45:55.000 | 2012-03-13 17:45:55.000 | Visa | —, Michael | 501 |
Summary of of Payments on Outstanding Invoices (Grouped by Date and By Payment Type)
SELECT SUM(AmountPaid),PaymentType.TenderType,LEFT(Payments.CompletionDate,11)
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0
GROUP By LEFT(Payments.CompletionDate,11),PaymentType.TenderType;
Result Example:
100.00 | MC | Jun 8 2012 |
125.00 | MC | May 15 2012 |
75.59 | Visa | Apr 6 2012 |
130.00 | Visa | Apr 23 2012 |
150.00 | Visa | Feb 1 2012 |
50.00 | Visa | Feb 16 2012 |
Summary of of Payments on Outstanding Invoices (Grouped by Date)
SELECT SUM(AmountPaid),LEFT(Payments.CompletionDate,11)
FROM tblProductOrderTenderType1 as Payments
INNER JOIN tblProductOrders1 as Orders
ON Payments.OrderID=Orders.OrderID
LEFT JOIN tblTenderType as PaymentType
on Payments.TenderType=PaymentType.TenderTYpeID
LEFT JOIN tblClients as Clients
ON Payments.ClientID =Clients.ClientID
WHERE Orders.ActivePO<>0
GROUP By LEFT(Payments.CompletionDate,11),PaymentType.TenderType
Result Example:
10.00 | Jun 6 2012 |
300.00 | Mar 8 2012 |
50.00 | Mar 22 2012 |
125.00 | May 26 2012 |
100.00 | Jun 8 2012 |
125.00 | May 15 2012 |
Sales by Vendor/Supplier
SELECT tblClients.CompanyName as Vendor,
Details.ProductName,SUM(Quantity),SUM(Quantity*UnitPrice)
FROM tblProductOrderDetails as Details
INNER JOIN tblProducts as Products
ON Products.ProductID=Details.ProductID
INNER JOIN tblClients
ON tblClients.ClientID=Products.SupplierID
WHERE CompletionDate BETWEEN '01/01/2012' AND '01/01/2013'
GROUP BY tblClients.CompanyName ,Details.ProductName
ORDER BY tblClients.CompanyName ,Details.ProductName
Sales of Products By Employee - Grouped by Employee - Sorted by Employee.
SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
SUM(round(UnitPrice * Quantity - 1) as TotalSales
FROM tblProductOrderDetails
INNER JOIN tblServiceType
ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
AND FilterType=0
GROUP BY ServerName,ProductName
ORDER BY ServerName,ProductName
Sales of Products By Employee - Grouped by Employee - Sorted by Product.
SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
SUM(round(UnitPrice * Quantity - 2) as TotalSales
FROM tblProductOrderDetails
INNER JOIN tblServiceType
ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
AND FilterType=0
GROUP BY ServerName,ProductName
ORDER BY ServerName,ProductName
Report for Pulling Phone numbers will the Cell Service
SELECT ClPhone,CellPhone,SettingName as ServiceProvider
FROM tblClients
INNER Join tblSettingsServer
ON tblClients.InsTertiaryNum =tblSettingsServer.SettingName
Report for Oregon Medical Marijuana Sales DOB
SELECT OrderID,DOB
FROM tblproductOrders
LEFT JOIN tblclients on
tblclients.clientid=tblProductorders.clientid
WHERE [CompletionDate] BETWEEN '10/01/2015' AND '3/31/2016'
AND (ServiceType='Rec Sale' OR ServiceType='Med Sale' Or ServiceType='Recommendation')
Report for Oregon Medical Marijuana Sales Itemized
You will want to make sure that your Marijuana products are all departmentalized as “Marijuana” this will ensure the report pulls only Marijuana Item Sales.
SELECT CompletionDate,Products.ProductName,Quantity
FROM [tblProductOrderdetails] as Details
INNER JOIN tblProducts as Products
ON Products.ProductID=Details.ProductID
INNER JOIN tblProducts as Departments
ON Departments.ProductID=Products.DepartmentID
WHERE [CompletionDate] BETWEEN '10/01/2015' AND '3/31/2016'
AND [Products].[ProdModifierType]=1
AND Departments.ProductName='Marijuana'
ORDER BY [Products].[ProductName]
Oregon Reporting - Group Sales by Item Type
Items must be 'Typed' by Subcategory according to the naming rules of Oregon. This applies to the report, as well as the requirement for Receipts.
Use the Subcategory Column - with the the Update Items Searched feature (as shown in the video) To assign the Subcategory the name of the type of each item, so that it will show on the receipts as dictated by the State
Identify your Patient's TypeID
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:
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.
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.
Product Search and Update|
1. Office - (Main toolbar) - Inventory - Product Search 2. Search for the items you wish to update.
Running the Report
Replace the date range where needed.
SELECT [ClAccountNumber],Subcategory.ProductName,UnitName,SUM(Quantity)
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]
LEFT JOIN tblClients
ON tblClients.[ClientID]=Details.[ClientID]
WHERE [ClientType]=10
AND [CompletionDate] BETWEEN '06/01/2016' AND '06/30/2016'
GROUP 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: • usedclothing • serializedprepackagemedicine • managingbatches • install_ccs • guides • custom_report