Skip to Content
author's profile photo Former Member
Former Member

How to displayed single value in DocTotal

Dear Experts,

I want single value in Query (M.DocTotal) Total (Rs.) so that i will received single Total value in Report.

SELECT Z.SERIESNAME, M.SERIES ,M.DocNum as 'A/R Invoice No.', M.DocDate as 'Inv. Date', M.CardCode as 'CUSTOMER Code',

M.CardName as 'Customer Name',

M.NumAtCard as 'Bill No. & Date',

ISNULL(L.ItemCode,'Service Item') as 'Item Code',

L.Dscription,

L.Quantity,

L.LineTotal,

L.TaxCode,

L.[VatSum],

M.WTSum AS 'TDS (Rs.)',

M.DocTotal as 'Total (Rs.)'

FROM OINV M

LEFT OUTER JOIN INV1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN INV4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN INV5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN INV3 Q ON M.DocEntry = Q.DocEntry

LEFT OUTER JOIN NNM1 Z ON M.Series= Z.Series

WHERE

(M.DocDate >= '[%0]' AND M.DocDate <= '[%1]')

And Z.SERIESNAME = '[%2]'

GROUP BY

Z.SERIESNAME ,M.SERIES ,M.DocNum,M.DocDate,M.CardCode,M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity,

L.LineTotal,M.DocEntry,M.[DiscSum],L.TaxCode,L.[VatSum],M.WTSum,M.DocTotal

ORDER BY

Z.SERIESNAME ,M.SERIES, M.DocNum,M.DocDate,M.CardCode,M.CardName,M.NumAtCard,L.ItemCode,L.Dscription,L.Quantity,

L.LineTotal,M.DocEntry,M.[DiscSum],L.TaxCode,L.[VatSum],M.WTSum,M.DocTotal

Regards

Amol

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jan 07, 2014 at 06:39 PM

    Hi

    If your report is in Crystal, no need to change the SQL, just create a group with the Invoice Number field and drag the Total (Rs) for the group footer.

    Rgs

    Fabio

    SAP 8.82 PL14 / Crystal Reports 2013


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 08, 2014 at 01:25 AM

    Hi,

    It is better to get sum of line total either by customer or item code.

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.