CR query

[Former Member]

Hi Experts,

To create a purchase order, I have used this formula

SELECT op.docentry,

op.docnum,

op.docdate,

op.cardcode,

op.cardname,

p1.itemcode,

p1.dscription as 'Item Name',

p1.Quantity,

p1.price,

p1.linetotal,

isnull(vat.taxsum,0) as 'VAT Amount',

isnull(cst.taxsum,0) as 'CST Amount',

Excise.taxsum as 'Cenvat Amount',

Cess.TaxSum as 'Cess Amount',

Hcess.Taxsum as ' Hcess Amount',

op.discsum as 'Discount',

op.TotalExpns as 'Freight Charges',

op.doctotal

from OPCH op

inner join PCH1 p1 on

op.docentry=p1.docentry

left outer join (select * from pch4 where statype=1) vat on p1.linenum=vat.linenum and p1.docentry=vat.docentry

left outer join (select * from pch4 where statype=4) cst on p1.linenum=cst.linenum and p1.docentry=cst.docentry

left outer join (select * from pch4 where statype=-90) excise on p1.linenum=excise.linenum and p1.docentry=excise.docentry

left outer join (select * from pch4 where statype=-60) cess on p1.linenum=cess.linenum and p1.docentry=cess.docentry

left outer join (select * from pch4 where statype=-55) Hcess on p1.linenum=Hcess.linenum and p1.docentry=Hcess.docentry

And in CR2008 generating the report.

Am I going right?

Is there any option so that VAT comes when there is VAT amount and CST comes when there is CST amount? How can we do that?

Please Help. Thanks in advance

Regards,

Rupa Sarkar

[former_member206488 · Accepted solution]

Hi Rupa,

Try this query:

SELECT op.docentry,
	op.docnum,
	op.docdate,
	op.cardcode,
	op.cardname,
	p1.itemcode,
	p1.dscription as 'Item Name',
	p1.Quantity,
	p1.price,
	p1.linetotal,
	(SELECT ISNULL(Sum(TaxSum),0) FROM PCH4 where statype=-90 and DocEntry=op.DocEntry) as 'Cenvat Amount',
	(SELECT ISNULL(Sum(TaxSum),0) FROM PCH4 where statype=-60 and DocEntry=op.DocEntry) as 'Cess Amount',
	(SELECT ISNULL(Sum(TaxSum),0) FROM PCH4 where statype=-55 and DocEntry=op.DocEntry) as 'Hcess Amount',
	(SELECT ISNULL(Sum(TaxSum),0) FROM PCH4 where statype=1 and DocEntry=op.DocEntry) as 'VAT Amount',
	(SELECT ISNULL(Sum(TaxSum),0) FROM PCH4 where statype=4 and DocEntry=op.DocEntry) as 'CST Amount',
	op.doctotal 

FROM OPCH op 
     LEFT OUTER JOIN PCH1 p1 on op.DocEntry=p1.DocEntry 
	 LEFT OUTER JOIN PCH4 T on T.DocEntry=p1.DocEntry and p1.LineNum=T.LineNum

in Crystal report select both the fields i.e. CST field and VAT field.

Now select CST field, Right click--Format field.

In Common tab ,check supress tick box and click on formula icon in front of supress.

paste below formula .

WhilePrintingRecords;
if {CSTFieldName}=0.00 then
true
else
false

do similar for VAT field.change CST field to VAT field Name in formula.

it will print VAT amount if CST is 0 and vice versa.

Thanks,

Neetu

[kvbalakumar]

Hi Rupa,

I'm not sure what you are asking.

But, try this

1.Remove the two fields, VAT amount and CST amount in CR design (not from Query)
2.Create a formula field (F001), somthing like
 if {VAT Amount} > 0 then 'Vat'
else if {CST Amount} > 0 then "CST"
else " "
3.Create another formula field (F002),
    if {VAT Amount} > 0 then {Vat Amount}
    else if {CST Amount} > 0 then {CST Amount}
    else 0
3. Add a text field in details area and drag both the F001 and F002 field into it.

Your ouput will be like

VAT 1000.00
CST 2100.50
   0
....

Note: Try to select the VAT Amount (other fields) from Report fields of Formula editor.

Regards,

Bala