Is it possible to construct a dataset where the parameter of the report is the where clause?
I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.
Hi,
you want to use parameter in the report use @.parameter_name.
|||Here's what I want to do...SELECT name, phone FROM contacts WHERE @.parameter_name
This does not work.
I have also tried, but it does not work:
DECLARE @.s AS nvarchar(1000)
SET @.s = N'SELECT name, phone FROM contacts WHERE ' + @.parameter_name
EXEC sp_executesql @.s
|||
You need to set your procedure up properly:
@.Parameter_Name varchar(1000) = null
,
AS
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME = @.Parameter_Name
|||Thanks for your reply, but I need you to further clarify.From your example, it is expecting to define the value for 'NAME'. What I want to do is to have the *entire* where clause as a parameter. e.g. @.whereclause = WHERE name = 'bob' and phone = '2222'
|||*bump* can any of the experts look into this?
|||
Hi ajhuddy!
Jim is correct in that you need to base your parameter values against a fieldname. In your example this should work:
SELECT NAME, PHONE
FROM CONTACTS
WHERE NAME IN(@.Parameter_Name) AND PHONE IN(@.Parameter_Phone)
This query will pull in all selected parameter values into your report.|||Thanks for the reply Chuck,So is there any humanly possible way to make the parameter the entire where clause? I don't want to the SQL statement of the report to be hard-wired for all the possible fields. E.g. @.whereclause = "WHERE name = bob or phone = 2222"
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
|||
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
|||Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
|||Aiwa,Thanks very much for the reply. This is exactly what I want to do. In your example, are you inferring that @.where is the parameter for the report? If so, this is what I have been trying to do, but I get errors for each field on the report. E.g. :
[rsFieldReference] The Value expression for the textbox ‘b_name’ refers to the field ‘b_name’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
|||
Aiwa wrote:
Hi,
If I understand you correctly this is an example on how to use dynamic SQL:
Code Snippet
DECLARE @.select NVARCHAR(1000)
DECLARE @.from NVARCHAR(1000)
DECLARE @.where NVARCHAR(1000)
DECLARE @.QueryString NVARCHAR(3003)
SET @.select = 'SELECT <Columns> '
SET @.from = 'FROM <Table> '
SET @.where = 'WHERE <WherePart>'
SET @.QueryString = @.select + @.from + @.where
EXEC sp_executesql @.QueryString
If you work with char datatypes in your where clause,you will have to double the quotes
Code Snippet
SET @.where = 'WHERE name = ''YourValue'''
Hi,
This is an important requirement for me, as I need the user to be able to create advanced 'where' criteria before launching the report.
Did you have a look at report builder?
HTH,
Aiwa,
Yes, I did look at the report builder, and it is great - but it can only create simple reports. Ideally, the 'filter' option that exists in the report builder would exist in the standard reports.
|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2041825&SiteID=1&mode=1sql
