Using report parameters as field names in a SELECT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joseph Rea, Accord Software - 29 Mar 2006 20:19 GMT

Hi,

I am having a problem in Reporting Services 2005. I want to use report
parameters as field names in a select statement, but the query just returns
the value of the parameter.

Ex. SELECT @pField1 FROM customers
returns the actual string value of @pField1

I'm entering this query in the Generic Query Designer as the command text
for my dataset. Is what I'm trying to do possible?

The reason I'm trying to do this is, I have a report with a Matrix on it.
For the Columns data, I'm using Branch/Division. For Rows, I have days of the
month selected. Then for the data area, I need to select the data as per a
filter entered by the user via a droplist of pre-determined values (in the
Report Parameter). The problem is that for each possible value in the filter
droplist, that's a different field in the DB table. So, for the report
parameter, I have each different field name in the DB as the value for each
different filter option. That way, when the Report Parameter gets it's value
from the filter, the data fields would automatically update in the Matrix. If
this logic is wrong, please let me know, as that negates the original
question.

Thanks very much in advance.

Reply to this Message

Sandeep - 29 Mar 2006 21:38 GMT

One way to do this would be to use a stored procedure in the report and pass
in the parameters. You could then create a sql dynamically in the stored
procedure and execute it using sp_executesql.

-Sandeep

> Hi,
>
[quoted text clipped - 21 lines]
>
> Thanks very much in advance.

Reply to this Message

Joseph Rea, Accord Software - 29 Mar 2006 21:58 GMT

Hi Sandeep,

Thanks for your response. Unfortunately in this project, we cannot use
stored procedures (as per client specifications). Any other thoughts?

Thanks.

> One way to do this would be to use a stored procedure in the report and pass
> in the parameters. You could then create a sql dynamically in the stored
[quoted text clipped - 27 lines]
> >
> > Thanks very much in advance.

Reply to this Message

Asher_N - 30 Mar 2006 07:06 GMT

Build the query string in a code module using the parm.

Something like:
The data set would say: =code.buildsql(parameters!p1.Value)

the code would be:

function buildsql(byval p1 as string)
buildsql="select " & p1 & " from table"
end function

=?Utf-8?B?Sm9zZXBoIFJlYSwgQWNjb3JkIFNvZnR3YXJl?=
<JosephReaAccordSoftware@discussions.microsoft.com> wrote in
news:FCEA8F0B-4E2F-488B-94C2-A737CFA4CA5B@microsoft.com:

> Hi Sandeep,
>
[quoted text clipped - 34 lines]
>> >
>> > Thanks very much in advance.

Reply to this Message

Manda Ho - 31 Mar 2006 02:57 GMT

How about define the SQL as string? Like

http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx

<Joseph Rea>; "Accord Software"
<JosephReaAccordSoftware@discussions.microsoft.com> wrote in message
news:79203109-F01B-4F7A-9535-BFE51D82675A@microsoft.com...

> Hi,
>
[quoted text clipped - 21 lines]
>
> Thanks very much in advance.

Reply to this Message

Amarnath - 31 Mar 2006 07:38 GMT

Hi Joseph,

Try using like this, it will work.
e.g Put this code in your data tab.
declare @str as nvarchar(1000)
set @str = ''
set @str = @str + 'select * from emp where emp = ' + @emp

exec sp_executesql @str

If you have problems with single and double quotes use double quotes and put
this command.
set quoted_identifier off

Amarnath

> Hi,
>
[quoted text clipped - 21 lines]
>
> Thanks very much in advance.

Reply to this Message