SSRS display with and without filter for Mth & Yr

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 2005 Forums

Analysis Server and Reporting Services (2005)

SSRS display with and without filter for Mth & Yr

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-08-27 : 22:41:14

hello

Anybody knows of how to set the report to display default query without selecting any filter and subsequently user filter the report according to filter?

Scenerio:
Display reports 6 months prior to current date when load and subsequently user select Month & Year filter (in multivalue).

Datasource:
select *
from t1 a
inner join t2 b on a.ID = b.ID
where convert(datetime, '01' + YMth + ' ' + convert(varchar(4), Yr)) >
dateadd(month, datediff(month, 0, getdate()) - 6, 0)
AND YMth=@YMth AND Yr=@Yr order by a.pName

Month filter:
SELECT DisplayBLMth
FROM tbBLMth
ORDER BY DATEPART(MONTH, CAST('1900-' + DisplayBLMth + '-01' AS DATETIME))

Month
MthNo: 1,2,3,4,5,6,7,8,9,10,11,12
DisplayBLMth: Jan,......Dec

Yr filter
SELECT distinct Yr from t1 where Yr = @Yr

For the above, only able to get from Mar 2009 instead.
When other month and year filter is selected, it will display Empty. report.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources