Sales Report
Hi guys
I need some assistance with a sales report, I need to write a report in crystal reports and i decided to make it a query based report since I will be pulling info from 2 tables (invoices and credits)
This is the query that I have
SELECT ObjType, DocDate, ItemCode, Dscription, SUM(Quantity) as ‘Total Quantity’, LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
FROM
( SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
UNION
SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], (T1.[Quantity]*-1), (T1.[LineTotal] * -1), T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode )
InvoicesMinusCreditsTable
GROUP BY ObjType, DocDate, ItemCode, Dscription, LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
Thanks
HI
I joined all the tables,you can specify the fields from the table by calling the instance as t0,t1 etc. ex:t0.objtype,t2.Quantity
select * from OINV t0 inner join inv1 t1 on t0.docentry = t1.docentry inner join RIN1 t2 on t1.DocEntry = t2.BaseEntry
inner join ORIN t3 on t2.DocEntry = t3.DocEntry
revert me for any clarificaqtions
Hi Kamba
Please elaborate on your solution as I seem to be having a problem.
It's running fine on the first, third and fourth group when I am filter by dates, problem is with the second group. It displays for all the entries in the system.
I've grouped the report by Item Group and summed the quantity and amounts.
If there is a solution that can give me report that is more like Net Sales Report with date parameters.
Thanks
HI
Can you post the screen shot of query results along with query...please
HI
Here is the query
SELECT ObjType, DocDate, ItemCode, Dscription, SUM(Quantity) as 'Total Quantity', LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
FROM
( SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
UNION
SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], (T1.[Quantity]*-1), (T1.[LineTotal] * -1), T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode )
InvoicesMinusCreditsTable
GROUP BY ObjType, DocDate, ItemCode, Dscription, LineTotal, GroupNum, U_PymtMethod, ItmsGrpNam
HI
I suspect this should be change.
Try this:
SELECT ObjType, DocDate, ItemCode, Dscription, SUM(Quantity) as 'Total Quantity', SUM(LineTotal) as 'Total Value', GroupNum, U_PymtMethod, ItmsGrpNam
FROM
( SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
UNION
SELECT T0.[ObjType], T0.[DocDate], T1.[ItemCode], T1.[Dscription], (T1.[Quantity]*-1), (T1.[LineTotal] * -1), T4.[GroupNum], T4.[U_PymtMethod], T3.[ItmsGrpNam] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode )
InvoicesMinusCreditsTable
Hi Kamba
I am getting a DEADLOCK error when running this query.
Hi ,
You may try this query...
SELECT 'A/R INVOICE' AS TYPE,V0.DocNum,
V0.DocDate,C0.CardCode,C0.[GroupNum],V1.ITEMCODE,V1.Dscription AS ITEMNAME,
-1*V1.Quantity,-1*V1.LINETOTAL,C0.[U_PymtMethod]
FROM OINV V0 (NOLOCK)
INNER JOIN INV1 V1 (NOLOCK) ON V0.DOCENTRY=V1.DocEntry
INNER JOIN OCRD C0 (NOLOCK) ON V0.CARDCODE=C0.CARDCODE
WHERE V0.DocDate>='[%0]' AND V0.DocDate<='[%1]' AND V0.DOCTYPE='I'
UNION ALL
SELECT 'A/R CREDIT MEMO' AS TYPE,V0.DocNum,V0.DocDate,C0.CardCode,C0.[GroupNum],
V1.ITEMCODE,V1.Dscription AS ITEMNAME,
-1*V1.Quantity,-1*V1.LINETOTAL,C0.[U_PymtMethod]
FROM ORIN V0 (NOLOCK)
INNER JOIN RIN1 V1 (NOLOCK) ON V0.DOCENTRY=V1.DocEntry
INNER JOIN OCRD C0 (NOLOCK) ON V0.CARDCODE=C0.CARDCODE
WHERE V0.DocDate>='[%0]' AND V0.DocDate<='[%1]' AND V0.DOCTYPE='I'