Dynamic Query

I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
Works example http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
so I can get my head around creating reports.
I am already struggling with Dynamic Queries, my first attempt has failed.
= "SELECT New_LiveDate, New_sale_description, New_Amount FROM New_GIISale"
& Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
Parameters!Amount.Value)
I get an error
"Cannot set the command text for data set xxxxxxxxx"
"Error during processing of the CommandText expression of dataset xxxxxxx"
Can anyone tell me where I'm going wrong and point me in the direction of
any other tutorials on creating these queries.
Thanks in advance.
StuartFirst off, I only use dynamic queries in very very rare circumstances.
Unless you are doing something like dynamically determining the table to
query in most cases you do not need to do that.
Now, when you do need to do a dynamic query the thing to realize is that you
are creating a string with the query you want. The best way to see if you
are creating the string properly is to have a report with nothing on it but
a text box. Then set the textbox to an expression. The expression would be
what you have below (so your report would still have the report parameters).
Note that with a dynamic query you don't get a field list generated or the
report parameters created for you automatically.
Also, just glancing at it it looks to me the last line is the problem:
Parameters!Amount.Value)
should be
Parameters!Amount.Value & ")"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:AAF6F385-0C34-461B-93EA-8EAC9A379885@.microsoft.com...
> I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
> Works example
> http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
> so I can get my head around creating reports.
> I am already struggling with Dynamic Queries, my first attempt has failed.
> = "SELECT New_LiveDate, New_sale_description, New_Amount FROM
> New_GIISale"
> & Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
> Parameters!Amount.Value)
> I get an error
> "Cannot set the command text for data set xxxxxxxxx"
> "Error during processing of the CommandText expression of dataset xxxxxxx"
> Can anyone tell me where I'm going wrong and point me in the direction of
> any other tutorials on creating these queries.
> Thanks in advance.
> Stuart
>|||You should rewrite your query like this and forget about dynamic sql...
SELECT
New_LiveDate,
New_sale_description,
New_Amount
FROM
New_GIISale
WHERE
(@.Amount != 0.0 and
New_Amount = @.Amount) or
@.Amount = 0.0
As a best practice I also would suggest to always package your queries
in stored procedures on the database...
Hope this helps!
--
Ben Sullins
http://bensullins.com