Hi,
I have an SSRS matrix report which is working fine, but there's addtional requirements to include the current month in the columns which i have a hard time on how to work on this. the problem is there's no records that falls on this month of april.
Here is my sample data and result:
Drop table #sample
Create table #sample
(Itemid nvarchar(25), datephysical datetime, qty int)
Insert into #sample(itemid,datephysical,qty) values ('PPPPP','2013-11-15',100)
Insert into #sample(itemid,datephysical,qty) values ('PPPPP','2013-12-20',100)
Insert into #sample(itemid,datephysical,qty) values ('XXXXX','2013-12-10',50)
Insert into #sample(itemid,datephysical,qty) values ('MMMMM','2014-01-03',250)
Insert into #sample(itemid,datephysical,qty) values ('BBBBB','2014-02-25',300)
Insert into #sample(itemid,datephysical,qty) values ('BBBBB','2014-03-14',10)
Insert into #sample(itemid,datephysical,qty) values ('TTTTT','2014-03-15',100)
select * from #sample
where datephysical between ('2013-01-01') and ('2014-04-02')
--How do i fill up the April column with 0 even there's no records transaction.
Itemid---Apr 2014--Mar 2014--Feb 2014--Jan 2014--Dec 2013--Nov 2013
------------------------------------------------------------------------
PPPPP----0------------0---------0---------0----------100------100
XXXXX----0------------0---------0---------0----------0--------0
MMMMM----0------------0---------0---------250--------0--------0
BBBBB----0------------10--------300-------0----------0--------0
TTTTT----0------------100-------0---------0----------0--------0
THank you.