Solved

Multi-Value Paramters in SSRS

Posted on 2010-11-15
15
607 Views
Last Modified: 2012-05-10
Hello,
I'm having an annoying problem here..
My query looks like       " where Month(ADM_Date) in (@MONTH)"
The Param @Month type is  Integer - setup to allow multiple values!

when I pass 1 or 2 indvidually , it works fine...
when I pass 1, 2 ,3 ,4 etc...  it fails " error: The Value provided for the report paramter 'Month' is not valid for its type"

when I change its type to TEXT, of course I get an error !

Any thoughts?

Thanks
0
Comment
Question by:Jason Yousef, MS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 50 total points
ID: 34137949
Hi,

Looks like '1,2,3' is a string ...

Is there any chance for you to pass '''1'',''2'',''3''' instead?

If you you just have to say there CAST(Month(ADM_Date) AS VARCHAR(2)) in (@Month)


That's my first impression....

Good luck
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34137974
Note that I set 2 single quotes surrounding 1, 2, 3 to have a proper string in single quotes...

0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 34138212
Hi Raul,
Thanks for stepping in, it didn't work and didn't give any errors.

just blank results as there's no such month numbers !

any other ideas?
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34138236
Hi,

Please verify that the query you're executing after replacing the param looks like

WHERE CAST(Month(ADM_Date) AS VARCHAR(2)) in ( '1', '2', '3' )

If looks like this and doesn't return anything, I'll try to figure out some (if possible)


Cheers.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34138270
Sorry,


Use this


WHERE CONVERT(varchar(2), Month(ADM_Date), 126) in ( @Month )

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34138284
CAST and dates are tricky...

Use CONVERT and 126 to convert it to ISO-8901

Ref.

http://msdn.microsoft.com/es-es/library/ms187928.aspx


Hope this works
0
 
LVL 8

Expert Comment

by:Hadush
ID: 34138349
If your query comes from store proc you have to use like split function to work for multivalue paramenter (that is the work around I know for reporting services).  refer to the link
http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34138353
Hi,

I know what happens,

I was preparing the query like it was for dynamic SQL, but it's not going to work like this I'm afraid...

Let me think plan b.


Cheers.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 34139717
Hi

There are several things that are unclear to me in your post.

You seem to be using T-SQL in SSRS IN(@Month) and that normally works very well but could you clarify how you supply the values?
1) Do you type them in? If yes then remember to type value hit enter and type another value (no commas, single quotes etc!)
2) If you provide values using dataset then make sure value is set correctly in the parameter.

I don't understand the bit about dynamic SQL :) So far I can see you try to use standard SSRS parameter filtering.

Hope that helps
Emil

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34143136
Hi,

I think @Hadush solution is a valid one, but if for any reason you cannot create another function to return the values in the list, you can try with dynamic SQL as I told you.

In this case, using you don't need to convert anything to string.

I don't like much this solution because of the dynamic SQL, but it works.


Cheers.
DECLARE @sql VARCHAR(MAX)
-- DECLARE @Month VARCHAR(50) SET @Month = '1,2,3'

SET @sql = 'SELECT  ... ' -- Your Query
SET @sql = @sql + ' WHERE Month(ADM_Date) in ( ' + @Month + ' )'

EXEC (@sql)

Open in new window

0
 
LVL 10

Expert Comment

by:itcouple
ID: 34143171
Hi

The subject is "Multi-Value Paramters in SSRS" why Dynamic T-SQL???? :)

Regards
Emil
0
 
LVL 21

Author Comment

by:Jason Yousef, MS
ID: 34149148
itcouple:
Thank you for stepping in...yes true..I'm not intending to use any dynamic SQL
and I'm not getting the values out of a query.

I've another report that when you click on a field, takes you to another report and passes the value that I already assigned.

see attached screen shot..

can;t use the enter, or without commas...
screenshot.jpg
0
 
LVL 10

Expert Comment

by:itcouple
ID: 34153038
Hi

That is much clearer now. How do you provide values for month? In the screenshot you have static value?
If you use expressions in there Iand want to join values into multi-value parameter array try using join function. I will have a go with that and let you know my findings.

Regards
Emil

0
 
LVL 10

Accepted Solution

by:
itcouple earned 450 total points
ID: 34153100
Did I use Join? Hmmm I should have said Split

Here is the expression I used to pass 10, 20 into subreport
=Split("10|20","|")

Hope that points you in the right direction
Regards
Emil
0
 
LVL 21

Author Closing Comment

by:Jason Yousef, MS
ID: 34154782
Thanks, Worked fine the split function, God bless for sharing the knowledge and helping.
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question