Skip to Content
Author's profile photo Former Member

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

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      HI

      Can you post the screen shot of query results along with query...please

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Kamba

      I am getting a DEADLOCK error when running this query.

      Author's profile photo Former Member
      Former Member

      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'