August 26, 2014 at 11:05 pm
I have an ssrs report that takes two parameters: @Division and @Year. It's made up of a list item grouped by Division, inside of which are two tables grouped by Year.
Both tables are displaying same information:
rows:
number of referrals
number of move-ins
columns:
months of the year
Right now they display identical data, but I need one table to display data for the year passed in as parameter, and the second table to display data relevant only to the the *prior* year of whatever is passed by @year. That way both tables reporting on same stuff, different time frames.
I have tried to set expression in the Group Properties for the year row group of the second table to:
=Fields!Year.Value - 1,
but this doesn't work.
Eventually I need to bring in one more table that shows same data but for @Year -2!
How can I do this?
--Quote me
August 27, 2014 at 12:24 am
I think I did it right...
Here's my dummy data:
SELECT '0' AS TType,'1' AS IDNo,'2013-10-22' AS MoveInDate,'10' AS MonthNo,'2013' AS MoveYear UNION ALL
SELECT '1','2','2013-09-09','9','2013' UNION ALL
SELECT '0','3','2014-05-13','5','2014' UNION ALL
SELECT '0','4','2013-12-19','12','2013' UNION ALL
SELECT '0','5','2014-04-12','4','2014' UNION ALL
SELECT '1','6','2013-11-12','11','2013' UNION ALL
SELECT '1','7','2014-03-07','3','2014' UNION ALL
SELECT '0','8','2013-12-01','12','2013' UNION ALL
SELECT '1','9','2014-05-20','5','2014' UNION ALL
SELECT '1','10','2013-12-23','12','2013' UNION ALL
SELECT '1','11','2014-07-14','7','2014' UNION ALL
SELECT '0','12','2013-11-01','11','2013' UNION ALL
SELECT '1','13','2014-08-01','8','2014' UNION ALL
SELECT '1','14','2014-02-09','2','2014' UNION ALL
SELECT '0','15','2014-04-19','4','2014' UNION ALL
SELECT '1','16','2013-11-23','11','2013' UNION ALL
SELECT '0','17','2014-06-01','6','2014' UNION ALL
SELECT '1','18','2014-05-17','5','2014' UNION ALL
SELECT '0','19','2014-05-16','5','2014' UNION ALL
SELECT '0','20','2014-08-01','8','2014' UNION ALL
SELECT '0','21','2014-06-27','6','2014' UNION ALL
SELECT '1','22','2013-10-01','10','2013' UNION ALL
SELECT '1','23','2014-07-12','7','2014' UNION ALL
SELECT '1','24','2014-06-24','6','2014' UNION ALL
SELECT '1','25','2014-05-20','5','2014' UNION ALL
SELECT '1','26','2014-03-26','3','2014' UNION ALL
SELECT '1','27','2013-09-26','9','2013' UNION ALL
SELECT '0','28','2014-07-23','7','2014' UNION ALL
SELECT '0','29','2014-07-21','7','2014' UNION ALL
SELECT '0','30','2013-11-30','11','2013' UNION ALL
SELECT '0','31','2013-10-18','10','2013' UNION ALL
SELECT '1','32','2014-02-14','2','2014' UNION ALL
SELECT '0','33','2013-12-29','12','2013' UNION ALL
SELECT '1','34','2013-09-16','9','2013' UNION ALL
SELECT '0','35','2014-04-12','4','2014' UNION ALL
SELECT '1','36','2014-05-11','5','2014' UNION ALL
SELECT '1','37','2014-07-15','7','2014' UNION ALL
SELECT '0','38','2013-11-02','11','2013' UNION ALL
SELECT '0','39','2014-03-11','3','2014' UNION ALL
SELECT '1','40','2014-03-02','3','2014' UNION ALL
SELECT '1','41','2014-07-31','7','2014' UNION ALL
SELECT '1','42','2014-07-20','7','2014' UNION ALL
SELECT '0','43','2014-02-24','2','2014' UNION ALL
SELECT '0','44','2014-04-05','4','2014' UNION ALL
SELECT '0','45','2014-07-12','7','2014' UNION ALL
SELECT '0','46','2014-05-12','5','2014' UNION ALL
SELECT '1','47','2013-09-15','9','2013' UNION ALL
SELECT '1','48','2014-06-21','6','2014' UNION ALL
SELECT '1','49','2014-03-08','3','2014' UNION ALL
SELECT '0','50','2013-09-07','9','2013;
On one tablix, my filter is =YEAR(Parameters!StartDate.Value)
On the other, it's =YEAR(Parameters!StartDate.Value)+1
The fun part is that you have to make sure your filter for the report returns two years, not one.
Here's my dummy stored procedure:
ALTER PROC uspMoveIns
@StartDate DATE
AS
SELECT MoveInID
, MoveInDate
, Month(MoveInDate) AS MoveInMonth
, YEAR(MoveInDate) AS MoveInYear
, MoveType
FROM MoveIn
WHERE MoveInDate>=@StartDate
AND MoveInDate<=DATEADD(yyyy,2,@StartDate);
The stored procedure returns two years worth of data, and the two tablixes are filtered to show one year apiece. The "year 1" matrix has a filter [MoveInYear]=YEAR(Parameters!StartDate.Value) and the second has a similar filter: =YEAR(Parameters!StartDate.Value)+1
August 27, 2014 at 7:54 am
pietlinden, thankyou so much.
My dataset is already pulling in all the years but....not in date form.
What you've pointed out to me is that I need to leave date in it's raw state and do the datepart in the SSRS expression - not within the query.
I will try this and thank you for helping me. (It is hard to learn the ins and outs of tablix).
--Quote me
August 27, 2014 at 8:45 pm
move_date = datetime datatype (format 2013-08-27 00:00:00.000)
This expression works
=Year(Fields!move_date.Value)
but
this expression will not reduce the year by one:
=Year(Fields!move_date.Value)-1
I have also tried
Datepart("yyyy", Fields!move_date.Value) -1
also tried
=Parameters!year.Value - 1
how to build ssrs expression so that one year is subtracted from the year datepart?
--Quote me
August 28, 2014 at 4:55 am
In a word, use DATEADD().
for example...
=DateAdd("d",3,Today)
August 28, 2014 at 7:04 am
I am grouping each table on move_date (date time stamp).
Group properties group expression:
=Year(Fields!move_date.Value)
That part works but each table groups data by 2014, 2013, 2012.
Will you please show me the expression to use in the Group Properties filter for?
current year, current year - 1, current year - 2?
--Quote me
August 28, 2014 at 7:17 am
Finally:
The filter expressions are as follows:
current year
=year(today)
last year
=year(today)-1
year before
=year(today)-2
I now have 3 tables, each showing a different year of data in the same report.
:sick: I nearly died, but now push forward with adding some charts to this report.
--Quote me
August 28, 2014 at 7:21 am
If you really mean the current year as in YEAR(GETDATE()), then
=YEAR(Today())-1 is last year: 2013
=YEAR(Today())-2 is two years ago: 2012
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply