Dynamic conditional report parameters error. SQL Server 2005 SSRS

Given the following dataset:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"
I am trying to dynamically generate a report based on pattype,
facility, and fromdate. The problem I am running into is that it keeps
choking on the fromdate parameter and I don't know why. I keep getting
'Cannot set the command text for data set (above data set)'.
Is it a problem with syntax? Am I not typecasting correctly? The data
type for 'fromdate' in the database is DateTime.
I'm wondering if this might not be possible to do, given the first two
lines in the WHERE statement use @. parameters and the IIF statement
doesn't...
Any advice would be greatly appreciated. Thank you...Correction on the SELECT statement:
="Select * " &
"From pat " &
"WHERE (pattype IN (@.pattype)) " &
"AND (facility IN (@.facility)) " &
IIf(Parameters!fromdate.Value = "","", "AND fromdate = " & <--
CDate(Parameters!fromdate.Value)) &
"Order By pattype,facility"|||Two things, first, you are assembling a string. When trying to debug this
what I suggest is to have a report with the report parameters and a single
textbox that you assign this expression to so you can see the resulting
string. This helps you figure out what is going on.
One of the things you are doing wrong is with your @.pattype and @.facility.
You have that embedded in your string, RS will not be replacing these for
you. What you need to do is this:
"WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") & ")) " &
Here is a summary from Robert a MS employee on a variety of expressions for
multi-value parameters.
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue boolean flag - tells if a parameter is
defined as multi value
=Parameters!MVP1.Count returns the number of values in the array
=Parameters!MVP1.Value(0) returns the first selected value
=Join(Parameters!MVP1.Value) creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ") creates a comma separated list of
values
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178558124.887441.311130@.y5g2000hsa.googlegroups.com...
> Given the following dataset:
> ="Select * " &
> "From pat " &
> "WHERE (pattype IN (@.pattype)) " &
> "AND (facility IN (@.facility)) " &
> IIf(Parameters!fromdate.Value <> "","", "AND fromdate = " &
> CDate(Parameters!fromdate.Value)) &
> "Order By pattype,facility"
> I am trying to dynamically generate a report based on pattype,
> facility, and fromdate. The problem I am running into is that it keeps
> choking on the fromdate parameter and I don't know why. I keep getting
> 'Cannot set the command text for data set (above data set)'.
> Is it a problem with syntax? Am I not typecasting correctly? The data
> type for 'fromdate' in the database is DateTime.
> I'm wondering if this might not be possible to do, given the first two
> lines in the WHERE statement use @. parameters and the IIF statement
> doesn't...
> Any advice would be greatly appreciated. Thank you...
>|||First of all, thank you. I appreciate your reply.
Now that you mention it, that makes obvious sense. I overlooked that
while I was going through the Books Online tutorials and I feel they
just 'jumped' too quickly without describing it the way you did. So
thanks for that.
The problem I'm running into now is that I am at this point:
="Select *" &
" From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
")) " &
" AND (facility IN (" & Join(Parameters!facility.Value, ",
") & "))" &
Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
(fromdate IN (" & Parameters!fromdate.Value & ")) Order By
pattype,facility,fromdate")
I'm still getting command text errors though. Can you offer any other
thoughts? Perhaps elaborate on how you use a textbox for a resulting
string (or is this useful after I get past my current problem?)? Or
can you point me to other tutorials that get more involved with what I
am trying to do? Books online don't really go deep enough.
Thanks again for your help. I assure you I am rigorously working on
this. Any help is once again greatly appreciated.
Jay|||The best way to do this is to assign this expression to a textbox so you can
see it (copy the report and delete everything except the parameters and the
textbox).
What I bet is happening is that your data types are strings. That means what
you really want is not a comma separated string. You want single quotes
around it. You could write some code behind reports that you bind the
parameter to that takes the parameter and returns a string all properly
formatted.
I have a suggestion, use the user sortable columns instead of sorting it
yourself this way. Then you would not have to use an expression for your
dataset definition in the first place. Using expressions for this is
generally a pain. For one thing, it will not give you a list of fields. You
have to use a regular sql string to get your field list and then change it
to an expression after than.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178567450.999831.232960@.q75g2000hsh.googlegroups.com...
> First of all, thank you. I appreciate your reply.
> Now that you mention it, that makes obvious sense. I overlooked that
> while I was going through the Books Online tutorials and I feel they
> just 'jumped' too quickly without describing it the way you did. So
> thanks for that.
> The problem I'm running into now is that I am at this point:
> ="Select *" &
> " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ", ") &
> ")) " &
> " AND (facility IN (" & Join(Parameters!facility.Value, ",
> ") & "))" &
> Iif(Parameters!fromdate.Value = ""," Order By pattype,facility", " AND
> (fromdate IN (" & Parameters!fromdate.Value & ")) Order By
> pattype,facility,fromdate")
> I'm still getting command text errors though. Can you offer any other
> thoughts? Perhaps elaborate on how you use a textbox for a resulting
> string (or is this useful after I get past my current problem?)? Or
> can you point me to other tutorials that get more involved with what I
> am trying to do? Books online don't really go deep enough.
> Thanks again for your help. I assure you I am rigorously working on
> this. Any help is once again greatly appreciated.
> Jay
>
>|||OK I think we're getting somewhere now...I hooked up the expression to
a textbox like you advised.
This works:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
This does not work (#error is returned in the textbox):
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" & " From pat" &
" WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
")) "
It appears to be choking on the join statement. I doublechecked the
syntax and I'm pretty sure I'm using it correctly...Do you see
anything wrong by any chance?
I'm going to shop around for an advanced manual I think. Books online
and the Osbourne SSRS manual doesn't go over this area very well at
all...
Thanks again for your help.|||Does the first syntax work or only works if you check a single value?
Also, just in case. I have in the past had problems with carriage returns.
Try putting it all on one line.
I use the Join(Parameters ...) syntax when I am showing the selected
parameters at the top of the report.
You got me curious. I did the following. I created a new report. I put a
single textbox on the report. I copied and pasted your expression which uses
the Join() below. Next I added a parameter called pattype, multi-select,
string and put a few values in.
It ran and it did as I suspected it would do. You end up with this:
Select facility, account, fromdate, thrudate, mednum, last_name, pattype
From pat WHERE (pattype IN (T1,T2,T3))
Note that this would work if the value type was integer but with a value
type of string this is invalid SQL. It needs single quotes around each
parameter.
I tried the one you say works below and it does not work of me. I get a
#Error.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178632816.621829.101750@.e51g2000hsg.googlegroups.com...
> OK I think we're getting somewhere now...I hooked up the expression to
> a textbox like you advised.
> This works:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Parameters!pattype.Value & ")) "
> This does not work (#error is returned in the textbox):
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" & " From pat" &
> " WHERE (pattype IN (" & Join(Parameters!pattype.Value, ",") &
> ")) "
> It appears to be choking on the join statement. I doublechecked the
> syntax and I'm pretty sure I'm using it correctly...Do you see
> anything wrong by any chance?
> I'm going to shop around for an advanced manual I think. Books online
> and the Osbourne SSRS manual doesn't go over this area very well at
> all...
> Thanks again for your help.
>|||Trying again. I replied but looks like it didn't go through...
I think that's it (the single quotes around the string values). Now I
get a convert to int data type error when the column in the database
is of type char/string.
Is there an example of a formed statement that uses the Join method
with multi selects for strings? I tried [ Join("'" & value & "'",
",") ] but that didn't work. Perhaps I need to create a loop or load
an array?
Bruce, thank you...|||From user Jeje: sure in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:'toto', 'tata', 'tutu'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1178802164.886275.281550@.y5g2000hsa.googlegroups.com...
> Trying again. I replied but looks like it didn't go through...
> I think that's it (the single quotes around the string values). Now I
> get a convert to int data type error when the column in the database
> is of type char/string.
> Is there an example of a formed statement that uses the Join method
> with multi selects for strings? I tried [ Join("'" & value & "'",
> ",") ] but that didn't work. Perhaps I need to create a loop or load
> an array?
> Bruce, thank you...
>|||Holy painful lesson...lol.
I got it:
="Select facility, account, fromdate, thrudate, mednum, last_name,
pattype" &
" From pat" &
" WHERE (pattype IN (" & "'" & Join(Parameters!
pattype.Value,"','") & "'" & ")) " &
" AND (facility IN (" & "'" & Join(Parameters!
facility.Value,"','") & "'" & ")) "
Man that made me feel so stupid. My mental block was the fact that I
needed to think from a perspective of VBScript writing SQL/T-SQL...
Well the good news is that now I can throw a few 'Iif' statements on
each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
dropdowns, I can create ONE dynamic statement/report that the user can
do with as they please and report on whatever they want.
Bruce. Thank you for being there and seeing me through this. I
appreciate that.|||Glad you got it to work. Dynamic queries (and getting the single quotes
right) is a pain. If you ever use openquery it gets worse because you need
to double the amount of single quotes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jay" <Jay.Pompano@.gmail.com> wrote in message
news:1179330253.302418.191850@.n59g2000hsh.googlegroups.com...
> Holy painful lesson...lol.
> I got it:
> ="Select facility, account, fromdate, thrudate, mednum, last_name,
> pattype" &
> " From pat" &
> " WHERE (pattype IN (" & "'" & Join(Parameters!
> pattype.Value,"','") & "'" & ")) " &
> " AND (facility IN (" & "'" & Join(Parameters!
> facility.Value,"','") & "'" & ")) "
> Man that made me feel so stupid. My mental block was the fact that I
> needed to think from a perspective of VBScript writing SQL/T-SQL...
> Well the good news is that now I can throw a few 'Iif' statements on
> each 'WHERE/AND' and, along with SELECT DISTINCT hookups on the other
> dropdowns, I can create ONE dynamic statement/report that the user can
> do with as they please and report on whatever they want.
> Bruce. Thank you for being there and seeing me through this. I
> appreciate that.
>|||On May 16, 11:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Glad you got it to work. Dynamic queries (and getting the single quotes
> right) is a pain. If you ever use openquery it gets worse because you need
> to double the amount of single quotes.
Hey one last quick question if you don't mind and I'll stop bugging
you.
I found it once in SQL Server 2005 Books Online/Tutorials, but I put
it down and can't find it anymore. There was a section on how to call
a webservice from SSRS and get the XML back. Have you, by any chance,
come across this?
I even remember what it looked like too. You had to put the webservice
URL in the data source connection string and the XML namespace/wsdl
for the data set (I think) and some other stuff like that.
Thanks Bruce.|||Found it...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-
a98f-234454d5d267.htm