Need Condition formula in Crystal report | Query !!!

[Former Member]

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

[Former Member]

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