CCS Point of Sale
You are here: start » guides » reporting » custom_report
Differences
This shows you the differences between two versions of the page.
|
guides:reporting:custom_report [2012/06/13 23:04] markphillips2 |
guides:reporting:custom_report [2018/12/23 12:11] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | 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' | ||
| - | |||
| - | ====== 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 - ((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\\ | ||
| - | 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 - ((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\\ | ||
| - | |||
| - | GROUP BY ServerName,ProductName\\ | ||
| - | ORDER BY ServerName,ProductName\\ | ||
Trace:
