So i am using a view for my report and my question is can i have a where clause parameter select * from vw_rpt_inv_report where=@where? The reason i want to use that is when my report is executed my view has 28 columns right so a user should be able to use any column from my report as a parameter. Does that make any sense ?FM

Hi FM, Try this out, 1. Create a report level parameter "Year" with data type as "Integer" 2. Now you query should be something like this:- SELECT * FROM vw_rpt_inv_report WHERE = @Year Note:- Assuming "Year" is the column of the view "vw_rpt_inv_report". Please let us know your feedback. Thanks KumarKG, MCTS

Hi Kumar, But i want to use any column from my report as a parameter value. If for example my view give me like 5 columns in the resultset i want to pick any of those 5 columns as my paramter. If i use "year" which is part of my view i would only get values of year when a user inputs like year="2000". usually its like this select * from vw_rpt_inv_report where year=@year But can it be select * from vw_rpt_report where (all the columns )= @where parameter?FM

You could use dyanamic sql: declare @Sql nvarchar(800); set @sql = 'select * from vw_rpt_inv_report where ' + @year; exec sp_executesql @sql;Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

Hi Russel, My dataset is a View though... thats very important to use i beleive.FM

sp_executesql executes any sql statement, including selects on views.Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

So in my dataset properties window under my query i could use this DECLARE @SQL NVARCHAR(800); SET @SQL = 'SELECT L.COMPANYNAME AS EXPR1, L.LOCATIONNAME AS EXPR2, L.ADDRESSLINE2 AS EXPR3, L.CITY AS EXPR4, L.STATE AS EXPR5, L.COUNTRY AS EXPR6, L.ZIPCODE AS EXPR7, "isf".* FROM VW_RPT_INV_REPORT ISF, LOCATION L WHERE ' L.LOCATIONID = ISF.LOCATIONID + L.LOCATIONID=@LOCATIONID ???FM

HI FM, You can use below t-sql query, DECLARE @SQL NVARCHAR(800); SET @SQL = 'SELECT L.COMPANYNAME AS EXPR1, L.LOCATIONNAME AS EXPR2, L.ADDRESSLINE2 AS EXPR3, L.CITY AS EXPR4, L.STATE AS EXPR5, L.COUNTRY AS EXPR6, L.ZIPCODE AS EXPR7, "isf".* FROM VW_RPT_INV_REPORT ISF, LOCATION L WHERE L.LOCATIONID = ISF.LOCATIONID AND L.LOCATIONID = ' + @LOCATIONID EXEC SP_EXECUTESQL @SQL; Assuming @LOCATIONID is your report level parameter. Please let us know your feedback. Thanks KumarKG, MCTS

I get unable to parse the query when i try to run it .FM

Hi FM, I have tried with the below query and it is working perfectly, --DECLARE @ID INT--VARCHAR(10) --SET @ID = 3--'1' CREATE TABLE #TEMP1 ( ID INT, ST VARCHAR(10) ) INSERT INTO #TEMP1 VALUES ( 1, 'abc' ) INSERT INTO #TEMP1 VALUES ( 1, 'abc' ) INSERT INTO #TEMP1 VALUES ( 2, 'def' ) INSERT INTO #TEMP1 VALUES ( 3, 'uvw' ) INSERT INTO #TEMP1 VALUES ( 3, 'uvw' ) CREATE TABLE #TEMP2 ( ID INT, DT DATETIME ) INSERT INTO #TEMP2 VALUES ( 1, '2011-05-10 13:32:18.587' ) INSERT INTO #TEMP2 VALUES ( 2, '2011-04-18 13:32:18.587' ) INSERT INTO #TEMP2 VALUES ( 3, '2010-01-20 13:32:18.587' ) DECLARE @SQL NVARCHAR(800); SET @SQL = 'SELECT #TEMP1.*, #TEMP2.DT FROM #TEMP1 INNER JOIN #TEMP2 ON #TEMP1.ID = #TEMP2.ID WHERE #TEMP1.ID = ' + CAST ( @ID AS VARCHAR ) EXEC SP_EXECUTESQL @SQL; DROP TABLE #TEMP1 DROP TABLE #TEMP2 Here is the sample RDL file:- http://cid-15e0e13bd0545504.office.live.com/self.aspx/.Public/DynamicSQLQuery.rdl Note:- You might need to change the DataSource Please let us know your feedback. Thanks KumarKG, MCTS

declare @where nvarchar(1000) set @where = 'select i.*, L.COMPANYNAME, L.LOCATIONNAME, L.ADDRESSLINE2, L.CITY, L.STATE, L.COUNTRY, L.ZIPCODE FROM VW_RPT_INV_STANDARD_FORMAT I JOIN LOCATION L ON L.LOCATIONID=I.LOCATIONID and WHERE ' + :whereclause EXEC ( @where ) I am getting an error when i run this code. Error: ORA-06550: LINE 1,COLUMN9 PLS- 00103 :encountered the symbol "@" when expecting one of the following begin function package pragma procedure subtype use <an identifier> <adouble-quoted delemited-identifier> form current cursorFM