Dynamic Query Report Server 2005

Because of several reasons i have to set the query statement of a report
dynamically. Does anybody know if that is possible just before rendering
a report? I use the web Services interface for accessing the reports.
thanks for any suggestions
MarkusYou cannot modify the query statement through e.g. SOAP. You would need to
republish the report. But is this really necessary? Did you look into using
an expression-based query commandtext (which is already available in
RS2000)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Another thing you could do is to write a Stored Procedure which accepts a
parameter and issues one of several queries, based on the parameter, or
perhaps generates the query string and executes it via sp_executesql
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Robert, what do you mean "expression-based query commandtext"?
Here is my problem - I need to pass in a parameter with multiple values.
What is the recommended way to accomplish this?
The only suggestion I've gotten so far is to create a user based
function on the reporting SQL server that can parse my parameter string
into a table. I don't wish to do this if there is an easier way.
So then I think, hey! maybe I can generate my own sql then pass it to
the report. Wrong. Apparently I can define the query if I'm creating a
data driven subscription but not if I just want to pass it to an
existing Report? This makes absolutely no sense to me.
Does anyone have a recommendation for me?
thanks,
Ian Stallings
Robert Bruckner [MSFT] wrote:
> You cannot modify the query statement through e.g. SOAP. You would need to
> republish the report. But is this really necessary? Did you look into using
> an expression-based query commandtext (which is already available in
> RS2000)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Markus" <markus@.m-s-wunderlich.de> wrote in message
> news:cu36m2$dq9$1@.online.de...
>>Because of several reasons i have to set the query statement of a report
>>dynamically. Does anybody know if that is possible just before rendering a
>>report? I use the web Services interface for accessing the reports.
>>thanks for any suggestions
>>Markus
>
>|||By expression based he means the following. Go to the generic view of the
query designer (hover over the buttons to the right of the ... to find the
one to click on). You can do either of these two things:
select * from sometable
or you can put in an expression:
="select * from sometable"
I use this technique for having a parameter specify my order by but you
could use this for your needs as well. Note that you have to make everything
perfect for this to work. I usually first just have a report with parameters
and a single textbox on the report (no query to start off with). I assign
the expression to the textbox and test it out until I see the proper SQL
string.
Here is an example for using a parameter
="SELECT * FROM sometable order by " & parameters!SortBy.value
The point here is that you are dynamically creating the SQL statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ian Stallings" <jovian_moon@.hotmail.com> wrote in message
news:%23Lm1WnjNFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
> > You cannot modify the query statement through e.g. SOAP. You would need
to
> > republish the report. But is this really necessary? Did you look into
using
> > an expression-based query commandtext (which is already available in
> > RS2000)?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Markus" <markus@.m-s-wunderlich.de> wrote in message
> > news:cu36m2$dq9$1@.online.de...
> >
> >>Because of several reasons i have to set the query statement of a report
> >>dynamically. Does anybody know if that is possible just before rendering
a
> >>report? I use the web Services interface for accessing the reports.
> >>thanks for any suggestions
> >>
> >>Markus
> >
> >
> >|||Just a follow up, I have fixed this problem. I ended up using a user
defined function to parse the parameter (which is passed in as '1,2,3'
.. etc) and then return a table with the datatypes I need, I then query
against that and return a recordset that I use in my dataset query.
Here are more details in case anyone comes searching later:
http://weblogs.asp.net/jmoon/archive/2005/04/01/396649.aspx
- Ian Stallings
Ian Stallings wrote:
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
>> You cannot modify the query statement through e.g. SOAP. You would
>> need to republish the report. But is this really necessary? Did you
>> look into using an expression-based query commandtext (which is
>> already available in RS2000)?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Markus" <markus@.m-s-wunderlich.de> wrote in message
>> news:cu36m2$dq9$1@.online.de...
>> Because of several reasons i have to set the query statement of a
>> report dynamically. Does anybody know if that is possible just before
>> rendering a report? I use the web Services interface for accessing
>> the reports.
>> thanks for any suggestions
>> Markus
>>
>>