Skip to Content

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

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. kambadasan v

    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

    (0) 
    1. Bongani Dlamini 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

      (0) 
        1. Bongani Dlamini 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

          (0) 
          1. kambadasan v

            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

            (0) 
              1. Priya Lakshmanan

                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’


                (0) 

Leave a Reply