Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 compute sum result cannot show in report,how to

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-23 : 08:46:25
Everybody know how to display sql compute sum command their result in amount and qty in report?

I am try this query is successful show the total amount and qty in studio management server and show in screen. But run this query on report is without to show last column of qty and amount, why ?

------------------------------------------------------------
select
xsodetail.shopcode,xsodetail.memono,xsodetail.txdate,
xsodetail.sku,xsoheader.depositamt,xsopayment.paymentcode,xsopayment.paymentamt,
xsodetail.itemamt,xsodetail.salesqty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsoheader.shopcode + xsoheader.memono = xsopayment.shopcode + xsopayment.memono
where (xsodetail.sku = 'L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N' and xsodetail.txdate = CONVERT(varchar(100), GETDATE(), 112)
compute sum(xsopayment.paymentamt) , sum(xsodetail.salesqty)

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-23 : 12:20:20
Probably because the way you join. Try this:
select d.shopcode
,d.memono
,d.txdate
,d.sku
,h.depositamt
,p.paymentcode
,p.paymentamt
,d.itemamt
,d.salesqty
from xsoheader as h
inner join xsodetail as d
on d.shopcode=h.shopcode
and d.memono=h.memono
inner join xsopayment as p
on p.shopcode=h.shopcode
and p.memono=h.memono
where d.sku in ('L000254','L000256')
and h.voidflag='N'
and d.txdate=convert(varchar(100),getdate(),112)
compute sum(p.paymentamt)
,sum(d.salesqty)
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-23 : 13:35:19
Thanks so much ! you are big help to me !!!
Go to Top of Page
   

- Advertisement -