Customise SQL report and automate running

• Project ID:

  1463550

• Project Type:

  Fixed

• Budget:

  £20-£250 GBP
  (Approx. €24-€312 EUR)

Project Description:

We run a Volusion web store, which has a sales report built into it, which I need to customise to
**remove 'order details count' and 'quantity' **
**but add 'payment method'.**

In a perfect world, I would like the report to run automatically at 00:00 on the last day of each month and email itself to me.
The report should be sorted by payment method (ie VISA, MASTERCARD, PAYPAL) with a total for each and then by date.
So to summarise:
Fields required:
OrderDate
Orders_count
paymentmethod
paymentamount
payment authorized
payment received
salestax
shipping cost
COGS
Profit
Profit margin
MONTHLY TOTALS FOR ALL NUMERICAL FIELDS

If there is any way that the report can be created as a well laid out pdf file, that would be amazing. I dont know if this is possible though.

Here is sql from the current report:


SELECT Orders.Year,Orders.Orders_Count,Orders.PaymentAmount,Orders.Total_Payment_Authorized,Orders.Total_Payment_Received,Orders.SalesTax1,Orders.SalesTax2,Orders.SalesTax3,Orders.TotalShippingCost,Orders.OrderDetails_Count,Orders.Quantity,Orders.COGS,Orders.Profit,Orders.ProfitMargin FROM (SELECT Orders.Year,Orders.Orders_Count,Orders.PaymentAmount,Orders.Total_Payment_Authorized,Orders.Total_Payment_Received,Orders.SalesTax1,Orders.SalesTax2,Orders.SalesTax3,Orders.TotalShippingCost,OrderDetails.OrderDetails_Count,OrderDetails.Quantity,OrderDetails.COGS,OrderDetails.Profit,OrderDetails.ProfitMargin FROM (SELECT DATEPART(YY, Orders.OrderDate) AS Year, Max(Orders.OrderDate) As OrderDate ,Max(Orders.ShipDate) As ShipDate,Count(Orders.OrderID) As Orders_Count,Sum(Orders.PaymentAmount) AS PaymentAmount,Sum(Orders.Total_Payment_Authorized) AS Total_Payment_Authorized,Sum(Orders.Total_Payment_Received) AS Total_Payment_Received,Sum(Orders.SalesTax1) AS SalesTax1,Sum(Orders.SalesTax2) AS SalesTax2 ,Sum(Orders.SalesTax3) AS SalesTax3,Sum(Orders.TotalShippingCost) AS TotalShippingCost FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON Orders.PaymentMethodID = PaymentMethods.PaymentMethodID) LEFT JOIN ShippingMethods WITH(NOLOCK) ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID) LEFT JOIN Customers WITH(NOLOCK) ON Orders.CustomerID = Customers.CustomerID) WHERE Orders.OrderStatus <> 'Cancelled' GROUP BY DATEPART(YY, Orders.OrderDate)) Orders INNER JOIN (SELECT DATEPART(YY, Orders.OrderDate) AS Year, Count(OrderDetails.OrderDetailID) As OrderDetails_Count,Sum(OrderDetails.Quantity) AS Quantity,Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS,Case When SUM(Vendor_Price) IS NULL Then null Else Sum((Case When OrderDetails.ProductCode like 'DSC-%' Then OrderDetails.ProductPrice - ISNULL(OrderDetails.Vendor_Price,0) Else OrderDetails.ProductPrice - OrderDetails.Vendor_Price End) * OrderDetails.Quantity) End AS Profit,CASE SUM(OrderDetails.ProductPrice * OrderDetails.Quantity) WHEN 0 THEN 0 ELSE ROUND(((SUM(OrderDetails.ProductPrice * OrderDetails.Quantity) - SUM(OrderDetails.Vendor_Price * OrderDetails.Quantity)) / SUM(OrderDetails.ProductPrice * OrderDetails.Quantity)) * 100, 1) END AS ProfitMargin FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON Orders.PaymentMethodID = PaymentMethods.PaymentMethodID) LEFT JOIN ShippingMethods WITH(NOLOCK) ON Orders.ShippingMethodID = ShippingMethods.ShippingMethodID) LEFT JOIN Customers WITH(NOLOCK) ON Orders.CustomerID = Customers.CustomerID) LEFT JOIN OrderDetails WITH(NOLOCK) ON Orders.OrderID = OrderDetails.OrderID WHERE Orders.OrderStatus <> 'Cancelled' GROUP BY DATEPART(YY, Orders.OrderDate)) OrderDetails ON Orders.Year = OrderDetails.Year) Orders ORDER BY Orders.Year DESC

Skills required:

SQL