January 14, 2009 at 7:56 am
I am executing a stored procedure in SSRS. All the data from it is being returned (all 58,000 rows) when I only want only the current year's data (about 1200 rows), The data contains a col in the format yyyy/mm which I want to filter on. I have 2 datasets:-
EXEC spdata
and
SELECT DISTINCT Key AS PayPeriod
FROM
WHERE LEFT(Key,4)>=2008
ORDER BY Key
I also have a report parameter PayPeriod.
At runtime I can select from a list as defined by the second parameter but all the data is being returned, probably because I haven't told SSRS how to only bring back the data which matches the report parameter but I'm not quite sure the best way to do this. Should I do this filtering in Management Studio before I execute the sp and if so, how do I do this. Any help appreciated - I suspect this is quite simple really.
January 14, 2009 at 8:43 am
You add a Parameter to the stored procedure say @year
configure the SP to utilize the parameter @year
SELECT DISTINCT Key AS PayPeriod
FROM
WHERE LEFT(Key,4)>=@year
ORDER BY Key
Refresh in SSRS Designer
Voila! you'll see a parameter being asked by SSRS when you run the report
January 14, 2009 at 9:26 am
Many Thanks - I'll give that a go
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply