1. Home
  2. Point-Of-Sale Software
  3. Adding conditions to sales reports

Adding conditions to sales reports

For accounting reasons, our parent company needs to know the amount of sales made to employees of our store. I thought I had this figured out with a SQL query (see below) but it turns out the numbers from my query are very different from the numbers returned to the Sales > Top Customers report.

Is there a way to run the Top Customers report, but adding a condition (not in the filter option list) to include only rows where Customer.Employee = 1?

I appreciate any tips.

Here's my SQL, maybe there's something easily seen that I missed...

SELECT c.LastName + ', ' + c.FirstName AS associate, SUM (e.FullPrice) AS regularprice, SUM(e.Price) AS soldprice, SUM (e.FullPrice) - SUM(e.Price) AS discount FROM Customer AS c INNER JOIN [Transaction] AS t ON c.ID = t.CustomerID INNER JOIN TransactionEntry AS e ON t.TransactionNumber e.TransactionNumber WHERE (t.Time BETWEEN '1/1/2009' AND '1/31/2009 23:59:59') AND (c.Employee = 1) GROUP BY c.LastName + ', ' + c.FirstName WITH ROLLUP

Reply to
montare
Loading thread data ...

Reply to
Otto

You can copy the Sales - Top Customers Report.qrp file and rename it something like "Custom - Top Employee Purchases.qrp" - then open the report and edit it with notepad, where you will make the following entries:

ReportTitle = "Top Employee Purchases Report"

SelCriteria = "Customer.Employee = 1"

The SelCriteria entry acts as a WHERE clause in a SQL query, therefore including only purchases of customers flagged as employees.

Run the report from the custom area > For accounting reasons, our parent company needs to know the amount of

Reply to
convoluted

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.