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
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS Matrix Report

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-02 : 20:57:22
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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-14 : 08:05:50
Don't do "select *" (in fact, don't ever do that in production code!). Instead, construct a query that ensures a 0 value is returned for months that are missing from the input table.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-22 : 20:44:31
Sorry guys for my late reply. This is already solved. thanks.
Go to Top of Page
   

- Advertisement -