We help IT Professionals succeed at work.

Multi-Value Paramters in SSRS

638 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
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Note that I set 2 single quotes surrounding 1, 2, 3 to have a proper string in single quotes...

Jason YousefSr. BI  Developer

Author

Commented:
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?
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.
Sorry,


Use this


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

Open in new window

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

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

Commented:
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

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

Commented:
Hi

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

Regards
Emil
Jason YousefSr. BI  Developer

Author

Commented:
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

Commented:
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

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jason YousefSr. BI  Developer

Author

Commented:
Thanks, Worked fine the split function, God bless for sharing the knowledge and helping.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.