I'm trying to create an order report in CRM that for on the selected date, it shows each product on a new line and the total quantity ordered for the day for all customers.
I have managed to get each product on a new line, but the quantity is still showing a separate line for each individual order.
I want to show:
Product A total - 7
Product B total - 6
But instead I have the line totals as well as the grand total.
Is there any way of getting rid of the unrequired detail?
Also, I would like to limit the report to a selection of product families but cannot get the sql syntax right to do so (novice!)
Any help would be greatly appreciated.
Can you post your SQL query you are currently using.
Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm
Hi Daniel, it's about as basic as they come!
CREATE VIEW vSummaryOrderReport
select * from orders o
inner join orderitems oi on o.Orde_OrderQuoteID=oi.OrIt_orderquoteid and OrIt_Deleted is null
where o.orde_deleted is null
The only way I've managed it in the past is to hard code into the source view the timeframe (e.g. a 6 month period, or a 3 month period)
Created using the following SQL (just need to add into the WHERE the hard coded date filter like orde_opened > GETDATE()-30 to show the information for a rolling 30 days.
SELECT prod_name, SUM(TQ.TQ)FROM OrdersOUTER APPLY (SELECT orit_productid, SUM(orit_quantity) AS TQ FROM OrderItems WHERE OrIt_orderquoteid = Orde_OrderQuoteID AND orit_deleted IS NULL AND OrIt_LineType = 'i' GROUP BY OrIt_productid) AS TQLEFT OUTER JOIN NewProduct ON prod_productid = TQ.orit_productidWHERE Orde_Deleted IS NULL AND prod_name IS NOT NULLGROUP BY Prod_name
I tried that but am obviously doing something wrong as I get an SQL error message.
Here is what I've copied in:
SELECT prod_name, SUM(TQ.TQ)
(SELECT orit_productid, SUM(orit_quantity) AS TQ
WHERE OrIt_orderquoteid = Orde_OrderQuoteID AND orit_deleted IS NULL AND OrIt_LineType = 'i'
GROUP BY OrIt_productid) AS TQ
LEFT OUTER JOIN NewProduct ON prod_productid = TQ.orit_productid
WHERE Orde_Deleted IS NULL AND prod_name IS NOT NULL AND orde_opened > GETDATE()-30
GROUP BY Prod_name
What's in the CRM SQL Log? It should tell you what the error is. Let me know and I'll adjust the script accordingly.
This is the last entry -
Feb 11 2019 10:57:05.455 5860 10324 1 execsql,time,sql,errormsg 62 CREATE VIEW vSummaryOrderReport
Create View or Function failed because no column name was specified for column 2
My Mistake, change the first part
SELECT prod_name, SUM(TQ.TQ) AS Total_Quantity
Thank you - that worked a dream!
Really appreciate it.
© The Sage Group plc 2017All Rights Reserved