Tuesday, February 14, 2012

Can ONE report parameter update MULTIPLE query parameters?

Hi there,
Is it possible to have a single report parameter actually be used to update
several query parameters used by a stored procedure in my report. The stored
procedure I'm using requires 7 parameters ... but I can determine what the
values should be for the last six based on the value the user assigns to the
first one. Therefore, instead of forcing the user to assign all 7 ... I
wanted to be able to set the remaining six based on what they assign to the
first.
Is this possible? And if so, how?
Thanks - GIf you can write T-SQL this is very easy. Go to the generic query designer:
Do something like this:
declare @.SQL varchar(255)
select @.SQL = 'select name as somename from ' + @.Database + '.dbo.sysobjects
where xtype = ''U'' order by name'
exec (@.SQL)
I know you are doing a stored procedure but the concept is the same. Note
that @.SQL is declared by @.Database isn't. That is because @.Database is
mapped to a report parameter. If you put the above in the generic query
designer and execute it you are prompted for the Database. If a report
parameter is not automatically created in the form design go to
Report->Parameters and create the report parameter then come back to the
Data tab, click on ..., go to Parameters tab and map the Query Parameter to
the Report Parameter.
I suggest first getting my example to work and understand what is happening
and then move on to your stored procedure.
Hope that helps.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:7E59E3DB-F8EE-40B1-9440-944935BD85B6@.microsoft.com...
> Hi there,
> Is it possible to have a single report parameter actually be used to
> update
> several query parameters used by a stored procedure in my report. The
> stored
> procedure I'm using requires 7 parameters ... but I can determine what the
> values should be for the last six based on the value the user assigns to
> the
> first one. Therefore, instead of forcing the user to assign all 7 ... I
> wanted to be able to set the remaining six based on what they assign to
> the
> first.
> Is this possible? And if so, how?
> Thanks - G

No comments:

Post a Comment