April 26, 2011 at 12:06 am
2002 2003 2004 2005
Jan 10 8 7 6
Feb
Mar
Apr
.
.
.
.
.
.
Dec
if there is no record for month mar means its not displayed how to display that
April 26, 2011 at 4:09 am
Hi Usharani,
it's hard to show a complete solution, since you haven't posted any information about table layout, data etc. But usually you can do something like this:
DECLARE @data as table (
id int identity(1,1),
t datetime,
n int
)
INSERT INTO @data (t, n) VALUES ('2002-01-15', 17)
INSERT INTO @data (t, n) VALUES ('2002-01-20', 4)
INSERT INTO @data (t, n) VALUES ('2003-05-15', 12)
INSERT INTO @data (t, n) VALUES ('2004-07-15', 5)
INSERT INTO @data (t, n) VALUES ('2005-02-15', 9)
INSERT INTO @data (t, n) VALUES ('2002-04-15', 8)
INSERT INTO @data (t, n) VALUES ('2003-03-15', 12)
INSERT INTO @data (t, n) VALUES ('2004-08-15', 23)
INSERT INTO @data (t, n) VALUES ('2005-12-15', 1)
SELECT datepart(month, t),
SUM(CASE WHEN datepart(year, t) = 2002 THEN n ELSE 0 END) AS [2002],
SUM(CASE WHEN datepart(year, t) = 2003 THEN n ELSE 0 END) AS [2003],
SUM(CASE WHEN datepart(year, t) = 2004 THEN n ELSE 0 END) AS [2004],
SUM(CASE WHEN datepart(year, t) = 2005 THEN n ELSE 0 END) AS [2005]
FROM @data
GROUP BY datepart(month, t)
ORDER BY datepart(month, t)
Good luck!
/Markus
April 26, 2011 at 4:38 am
2002 2003
Jan Count
.
.
Dec
For displaying year i use(=Year(Fields!BirthDate.Value))
For diplaying Month i use(=MonthName(Month(Fields!BirthDate.Value)))
For Count i use (=Count(Id))
if no records for Jan means the row not displayed in matrix... am new to reporting... is it possible to display the month even if no records for that month.....
April 26, 2011 at 5:40 am
Hi,
create a month-table and (left or right) join with that.
I see that my query is not a perfect example, but since I have nothing else to go on I use it to show the principle...
Assuming the setup from my last post:
-- Create month-table
DECLARE @months as table
(
nMonth int
)
-- Fill month-table with month numbers from 1 to 12
insert into @months
select top 12 row_number() OVER (ORDER BY sc1.id)
from Master.dbo.SysColumns sc1
-- Use the table as a base in the query:
SELECT nMonth,
SUM(CASE WHEN datepart(year, d.t) = 2002 THEN n ELSE 0 END) AS [2002],
SUM(CASE WHEN datepart(year, d.t) = 2003 THEN n ELSE 0 END) AS [2003],
SUM(CASE WHEN datepart(year, d.t) = 2004 THEN n ELSE 0 END) AS [2004],
SUM(CASE WHEN datepart(year, d.t) = 2005 THEN n ELSE 0 END) AS [2005]
FROM @months m
left outer join @data d
on datepart(month, d.t) = m.nMonth
GROUP BY nMonth
ORDER BY nMonth
Then, in your case you change to something like this to get the month name (assuming you name the column containing the month number to myMonth):
(=MonthName(Fields!myMonth.Value))
I hope you can solv your problem with these hints... otherwise you need to post some example of your query. 🙂
/Markus
April 26, 2011 at 5:47 am
AM not Able To modify the storeed procedure.. i want to change that in designing because the same stored procedure is used for some other reporting... is it possible to change that in designing.......
April 26, 2011 at 9:34 am
Hi,
you should be able to change source for your report. So instead of using the Stored Procedure as is, you write a query using the Stored Procedure and join the result with some other data. This can be done by OPENQUERY.
I found a simple example on this page:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=410&lngWId=5
/Markus
April 27, 2011 at 6:07 am
Thanks Ya... The join query works only if we are not using any filters like
grouping by some fields or getting results of particular years....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply