pass parameter to view - SQL Server Forums

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

Analysis Server and Reporting Services (2012)

pass parameter to view

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-06-13 : 14:26:36

how can i pass paramerter (time) from reporting server to my db
i mean, i have report in reporting, and his dataset is very simple

select * from V_Test

my view in the DB ,and in the where i have :
where datevalue < GETDATE()

i want that GETDATE(), will be parameter.

how can i do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 00:50:00

make the query like


select * from V_Test where datevalue < @date

on running this in report data tab it will prompt for the value. give a suitable value,run query to get results. Then refresh the dataset. field as well as parameters will be added automatically to report.
You can right click on left top corner of report in design tab and choose report parameters and it will show parameters tab where you can change the properties of the created parameter like datatype,default value etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-06-14 : 06:25:04

thanks but,
i want to pass this parameter to the view

i mean in the view i want to add this
where datevalue < @date

(because i do not bring to SELECT this datevalue field)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 06:41:24

quote:
Originally posted by inbs

thanks but,
i want to pass this parameter to the view

i mean in the view i want to add this
where datevalue < @date

(because i do not bring to SELECT this datevalue field)

you cant pass parameter to a view. Its like a virtual table.
If you want to make query parameterized then it should be a stored procedure/function and not a view.
For SSRS, best would be to use stored procedure as backend with your required parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources