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 [login to view URL],Orders.Orders_Count,[login to view URL],Orders.Total_Payment_Authorized,Orders.Total_Payment_Received,[login to view URL],[login to view URL],[login to view URL],[login to view URL],Orders.OrderDetails_Count,[login to view URL],[login to view URL],[login to view URL],[login to view URL] FROM (SELECT [login to view URL],Orders.Orders_Count,[login to view URL],Orders.Total_Payment_Authorized,Orders.Total_Payment_Received,[login to view URL],[login to view URL],[login to view URL],[login to view URL],OrderDetails.OrderDetails_Count,[login to view URL],[login to view URL],[login to view URL],[login to view URL] FROM (SELECT DATEPART(YY, [login to view URL]) AS Year, Max([login to view URL]) As OrderDate ,Max([login to view URL]) As ShipDate,Count([login to view URL]) As Orders_Count,Sum([login to view URL]) AS PaymentAmount,Sum(Orders.Total_Payment_Authorized) AS Total_Payment_Authorized,Sum(Orders.Total_Payment_Received) AS Total_Payment_Received,Sum([login to view URL]) AS SalesTax1,Sum([login to view URL]) AS SalesTax2 ,Sum([login to view URL]) AS SalesTax3,Sum([login to view URL]) AS TotalShippingCost FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON [login to view URL] = [login to view URL]) LEFT JOIN ShippingMethods WITH(NOLOCK) ON [login to view URL] = [login to view URL]) LEFT JOIN Customers WITH(NOLOCK) ON [login to view URL] = [login to view URL]) WHERE [login to view URL] <> 'Cancelled' GROUP BY DATEPART(YY, [login to view URL])) Orders INNER JOIN (SELECT DATEPART(YY, [login to view URL]) AS Year, Count([login to view URL]) As OrderDetails_Count,Sum([login to view URL]) AS Quantity,Sum(OrderDetails.Vendor_Price * [login to view URL]) AS COGS,Case When SUM(Vendor_Price) IS NULL Then null Else Sum((Case When [login to view URL] like 'DSC-%' Then [login to view URL] - ISNULL(OrderDetails.Vendor_Price,0) Else [login to view URL] - OrderDetails.Vendor_Price End) * [login to view URL]) End AS Profit,CASE SUM([login to view URL] * [login to view URL]) WHEN 0 THEN 0 ELSE ROUND(((SUM([login to view URL] * [login to view URL]) - SUM(OrderDetails.Vendor_Price * [login to view URL])) / SUM([login to view URL] * [login to view URL])) * 100, 1) END AS ProfitMargin FROM (((Orders WITH(NOLOCK) LEFT JOIN PaymentMethods WITH(NOLOCK) ON [login to view URL] = [login to view URL]) LEFT JOIN ShippingMethods WITH(NOLOCK) ON [login to view URL] = [login to view URL]) LEFT JOIN Customers WITH(NOLOCK) ON [login to view URL] = [login to view URL]) LEFT JOIN OrderDetails WITH(NOLOCK) ON [login to view URL] = [login to view URL] WHERE [login to view URL] <> 'Cancelled' GROUP BY DATEPART(YY, [login to view URL])) OrderDetails ON [login to view URL] = [login to view URL]) Orders ORDER BY [login to view URL] DESC
Hi
I am working as Data base Developer from 9 years and have developed many automated SQL Scripts and Report.
Please let me know that which Database Software are you using ...
Thanks
Hi ,
Good Day.
I have very good team of specialist who can be able to provide smart solution in C#, ASP.net, Oracle and SQL Server from India.
With best regards,
Sudip Chatterjee