compute sum result cannot show in report,how to

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)

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-23 : 13:35:19

Thanks so much ! you are big help to me !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources