Multi value parameters in SQL RS

[pvidya]

Hi,

I need some help getting multi-value parameters to work in SQL Reporting Services. Using VS 2005 to develop the report, backend is SQL 2005.

I have been reading around and I "think" I understand the concept of Split, Join, that the multivalue param is an array, cannot be used as such, difficult to use with procs etc. But I seem to be missing something basic.

My report runs off a proc. I am seeking to filter using the filter tab on the report's table's properties. I am trying to give the user the option to choose which companies' data he can view. Data returns companies, A, B, C, D... In the filter I tried:
- Fields!Company in Join(Parameter!MVparam.Value,",")
- Fields!Company = Join(Parameter!MVparam.Value,",")
- Fields!Company in Join(Parameter!MVparam.Value(0),",")
- True = Switch(Fields!Company <> Parameter!MVparam.Value(0), True, Fields!Company <> Parameter!MVparam.Value(1), True ,....

I also placed these expressions in an empty field on the report and it all returns true. So, I am wondering what it is that I am missing.

What I am really trying to do to provide the user with the option to exclude certain companies.. but i don't the see the 'Not In' option in the operator list in the screen where we set filters.

Thank you for your time.

Vidya.

 

[jymm]

I do this sort of thing in the sql (may not be as pretty as the filters, but it works pretty well)

for example I have a report that the users can select all items - or specific items

My default for the parameter (@ItemList) is ALL and I point the source for that at a dataset that has a union in it
select 'ALL' as RevenueLabel
union all
SELECT distinct RevenueReportLabel as RevenueLabel
FROM Items
WHERE ...

this way the user can select as many items for the report as they want.

This point you in a good direction?

Then the sql code to pull the sales simple has a where that says
where (RevenueReportLabel IN (@ItemLabel) OR 'ALL' IN (@ItemLabel))

 

[jymm]

oops - paste got out of whack & I was in a hurry...

@ItemLabel & @ItemList are the same and those last two lines should by above the question if it works... gotta get more Dew...

 

[pvidya]

Hi jymm,

Thanks for your response.

I am trying to do it one of two ways:
1) filter in SQL : for this I need to pass the multivalue parameter to SQL. This is not happening as when I click two value in the drop down and try to run the report, I get a "Must declare scalar variable @rptMVparam" message.

2) filter on the report: common logic does not seem to give me the results I expect ... in other words, "Not working"! Some of the logics I tried are on the original post.

I am not having trouble creating the drop down list for the multi value parameter.

Thanks,
Vidya.

 

[jymm]

I will only be able to help in the SQL end - if you want to do the filter I will let others help as I never got that to work very well.

in your parameters for the report (from Layout tab hit Report->Report Parameters) the name of your parameter should say rptMvParam --- then in the sql you can use the code
where Company IN (@rptMVParam)

this should work so that you could do multiple companies.

If you only want one company it should read
Where company = @rptMVParam

this help or hinder?

 

[pvidya]

I am having trouble getting the SQL proc accept the mv param....it accepts and works when there is only one object. Gives that error: "Must declare scalar variable @rptMVparam" when I click on two values.

Thanks.

 

[jymm]

are you talking a real SQL sp when you say 'proc' OR a SQL QUery? I resorted to just putting the query in.

 

[pvidya]

Yeah, real SQL stored procedure. Report's data set just has EXEC [db_name].[dbo].[proc_name] <dateparam1>,<dateparam2>

Stored procedure is rather long and calls other procedures there and therefore, the code cannot be placed directly in the report's data set.

 

[jymm]

ah - I understand now - Like our friend Simon says - RS cheats

Read here

http://sqlblogcasts.com/blogs/simon...multivalue-parameter-to-a-query-using-IN.aspx