Differences

This shows you the differences between two versions of the page.

--- guides:reporting:custom_report [2012/06/08 17:18]
markphillips2
+++ guides:reporting:custom_report [2018/12/23 12:11] (current)
@@ Line 9: / Line 9: @@
   - Press the button 'Open Report From Statement'
-**Sales by Vendor/Supplier**
+===== 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)\\
@@ Line 22: / Line 104: @@
-**Sales of Products By Employee - Grouped by Employee - Sorted by Employee.**
+===== Sales of Products By Employee - Grouped by Employee - Sorted by Employee. =====
-SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
+SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,\\
 SUM(round(UnitPrice * Quantity - ((UnitPrice * Quantity) * (tblProductOrderDetails.Discount / 100) * 100),2)) as TotalSales
-FROM tblProductOrderDetails
+FROM tblProductOrderDetails\\
-INNER JOIN tblServiceType
+INNER JOIN tblServiceType\\
-ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
+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\\
-WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
+**Sales of Products By Employee - Grouped by Employee - Sorted by Product.**\\
-AND FilterType=0
-GROUP BY ServerName,ProductName
+SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,\\
-ORDER BY ServerName,ProductName
+SUM(round(UnitPrice * Quantity - ((UnitPrice * Quantity) * (tblProductOrderDetails.Discount / 100) * 100),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\\
-**Sales of Products By Employee - Grouped by Employee - Sorted by Product.**
+GROUP BY ServerName,ProductName\\
+ORDER BY ServerName,ProductName\\
-SELECT ServerName as Employee,ProductName,SUM(Quantity) as QTYSold,
+===== Report for Pulling Phone numbers will the Cell Service =====
-SUM(round(UnitPrice * Quantity - ((UnitPrice * Quantity) * (tblProductOrderDetails.Discount / 100) * 100),2)) as TotalSales
-FROM tblProductOrderDetails
+SELECT ClPhone,CellPhone,SettingName as ServiceProvider\\
-INNER JOIN tblServiceType
+FROM tblClients\\
-ON tblProductOrderDetails.ServiceTypeID=tblServiceType.ServiceTypeID
+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.\\
+[[https://www.youtube.com/watch?v=jqSnH5Yv7gc|Product Search and Update|]]
+. Office - (Main toolbar) - Inventory - Product Search
+. 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:\\
-WHERE CompletionDate Between '01/01/2012' And '01/31/2012'
+SELECT SUM(LineTotal),\\
-AND FilterType=0
+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]\\
-GROUP BY ServerName,ProductName
-ORDER BY ServerName,ProductName

Trace: • crm_lists • statistics • hold_items_for_printing • posinstructions.png • multiplecashdrawersetup • general • calendar_and_events • data_cap_epay • custom_report • changing_order_type

Differences

Navigation

Search

Toolbox