Report parameter as a where clause

Hello

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'''

|||

Aiwa wrote:

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