Dynamic conditional report parameters error. SQL Server 2005 SSRS -- sql server reporting services

sql server reporting services : Dynamic conditional report parameters error. SQL Server 2005 SSRS

Jay
5/7/2007 10:15:24 AM

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...

Jay
5/7/2007 10:38:31 AM

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"

Bruce L-C [MVP]
5/7/2007 12:41:50 PM

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

[quoted text, click to view]

Jay
5/7/2007 12:50:51 PM

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

Bruce L-C [MVP]
5/7/2007 3:39:15 PM

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

[quoted text, click to view]

Bruce L-C [MVP]
5/8/2007 12:00:00 AM

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

[quoted text, click to view]

Jay
5/8/2007 7:00:16 AM

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.

Bruce L-C [MVP]
5/10/2007 12:00:00 AM

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

[quoted text, click to view]

Jay
5/10/2007 6:02:44 AM

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...

Jay
5/16/2007 8:44:13 AM

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.

Bruce L-C [MVP]
5/16/2007 10:53:42 AM

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

[quoted text, click to view]

Jay
5/17/2007 6:33:55 AM

On May 16, 11:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com>
[quoted text, click to view]

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.

Jay
5/17/2007 11:46:49 AM

Found it...

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/d23408e5-e65b-4f49-
a98f-234454d5d267.htm

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn