Table filter using multi value parameter

Question

On my report I have 5 tablix controls.

I have a multi value parameter that allows the user to select a maximum of 5 values.

I need to use each value of my multi value parameter to filter the data in the 5 tablix controls.

This work fine if i select 5 values, but when I select anything less i'm encountering an error.

This is the code that I am trying to use to pass a value to the filter if 5 values aren't selected, but it is failing whenever anything less than 5 values are chosen.

=iif(Parameters!iproduct_counter.count = 5, Parameters!iproduct_counter.Value(4),
iif(Parameters!iproduct_counter.count = 4, Parameters!iproduct_counter.Value(3),
iif(Parameters!iproduct_counter.count = 3, Parameters!iproduct_counter.Value(2),
iif(Parameters!iproduct_counter.count = 2, Parameters!iproduct_counter.Value(1),
Parameters!iproduct_counter.Value(0)))))

Any ideas

Thanks in advance

Answer 1

IIF will evaluate all its arguments before its evaluated.
http://msdn.microsoft.com/en-us/library/27ydhh0d(v=vs.90).aspx

If one argument cannot be evaluated because it raises an exception, the whole expression will return an error, even though the argument should never have been reached in the first place.

For example:
=IIf(1=1,1,  Parameters!iproduct_counter.Value(26))

Can you elaborate on why, you use such an expression to filter your parameters in the first place?
Are you only interested in filtering your tables on the value of the last parameter?

The expression
=Parameters!iproduct_counter.Value
will return the complete array. You can use it as the right hand side of a filter expression with the IN operator.

---

The Data Specialist (Blog)

Answer 2

Not sure about your actual requirements.

Instead of your statement following code will work without error and with same result!

=Parameters!product_counter.Value(Parameters!product_counter.Count - 1)

---

Bipin. P | ERNST & YOUNG GSS