CCS Point of Sale

Custom Reports

To run these reports:

  1. From Office - (Main Toolbar) - Reporting - Pivot Reporting -
  2. Go to the Advanced Tab.
  3. 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.
  4. Paste the statement into the advanced text area.
  5. 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:

SmithJohn777-777-8888848130
SmithMark777-555-4499848214

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]

1) , 2) UnitPrice * Quantity) * (tblProductOrderDetails.Discount / 100) * 100),2

Navigation

^From the Ground Up!| |[[Getting Started:]]| |[[Installation Guide:]]| |[[Pre Installation:]]| |[[Install CCS:]]| |[[Install New Database:]]| ^Guides| |[[Guides:]]| |[[FAQ:]]| |[[Trouble Shooting:]]| ^Credit Card Setup| |[[Data Cap Epay:]]| ^CCS Administration| |[[ Office:Office - General Management]]| ^CCS Terminal| |[[Office:Terminal]]| ^Hand Held Setup| |[[Hand Held:|Tablet]]| ^Kitchen Display| |[[KDS Software Setup:]]|