Skip to Content
avatar image
Former Member

Need Condition formula in Crystal report | Query !!!

Hi Experts,


I request all to please help me in one of the query or in crystal report.



Appreciate if you please can help me to achieve this result.

I have one report in which transaction from different tables are display like OWTR,OIGN,OIGE,ODLN etc.. What help I need from you all is we have 2 field name called 'FG_Qty' and 'Desp_Qty' in query, when this 2 field data becomes equal i.e Quantity in FG_Qty gets equal to Desp_Qty then it should not display in report.

Below is a part of my query for Fg_Qty and Desp_Qty:

isnull((select sum(I1.Quantity)  from IBT1_LINK I1 inner join OWTR ON I1.baseentry = OWTR.DocEntry and I1.Basetype = OWTR.Objtype Where I1.BatchNum = T1.BatchNo and I1.ItemCode = T1.ItemCode and I1.BaseType = 67 and I1.DocDate >= {?@Fdate} and I1.DocDate <= {?@Tdate} and I1.WhsCode in ('FG-E','FG-Expot') and I1.Direction = 0 and isnull(U_Disp_Fg_Gq,'NA') <> 'No' ),0)

+

isnull((select sum(I1.Quantity)  from IBT1_LINK I1 inner join OIGN ON I1.baseentry = OIGN.DocEntry and I1.Basetype = OIGN.Objtype Where I1.BatchNum = T1.BatchNo and I1.ItemCode = T1.ItemCode and I1.BaseType = 59 and I1.DocDate >= {?@Fdate} and I1.DocDate <= {?@Tdate} and I1.WhsCode in ('FG-E','FG-Expot') and I1.Direction = 0 and isnull(U_Disp_Fg_Gq,'NA') <> 'No' ),0)

-

isnull((select sum(I1.Quantity)  from IBT1_LINK I1 inner join OIGE ON I1.baseentry = OIGE.DocEntry and I1.Basetype = OIGE.Objtype Where I1.BatchNum = T1.BatchNo and I1.ItemCode = T1.ItemCode and I1.BaseType = 60 and I1.DocDate >= {?@Fdate} and I1.DocDate <= {?@Tdate} and I1.WhsCode in ('FG-E','FG-Expot') and I1.Direction = 1 and isnull(U_Disp_Fg_Gq,'NA') <> 'No' ),0)

)

as FG_Qty,

isnull((select sum(I1.Quantity)  from IBT1_LINK I1 inner join ODLN ON I1.baseentry = ODLN.DocEntry and I1.Basetype = ODLN.Objtype Where I1.BatchNum = T1.BatchNo and I1.ItemCode = T1.ItemCode and I1.BaseType = 15 and I1.DocDate >= {?@Fdate} and I1.DocDate <= {?@Tdate} and I1.WhsCode in ('FG-E','FG-Expot') and I1.Direction = 1 and isnull(U_Disp_Fg_Gq,'NA') <> 'No'),0)

as Desp_Qty

I want If Fg_Qty = Desp_Qty then it should not come in report.


Also advise if i can do it in crystal report also.


Attached file is a full query.

Looking forward for all your support.

Regards,

Harshal Makwana

55.txt.zip (1.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Dec 30, 2014 at 02:52 PM

    Dear Harshal,

    Without study your query, I would try to put in the select expert of the crystal report one formula like this:

    "FG_Qty<>Desp_Qty"

    Just a suggestion. 😊

    Best Regards,

    Antonis

    Add comment
    10|10000 characters needed characters exceeded