IIF and multi-valued parameters

Hi

i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.

basically, the report gets loads of data back from DataCube.

if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.

but

i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart

so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)

am doing this by trying to get the selected value or "xxxxx" if noting selected

to hide the second chart when only one category has been selected, tried

e.g. for the third chart

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

Parameters!LOOKUPITEMCategoryDescription.Label(2),

"xxxxx")

doesnt work - gives an #Error as a reult

But it works fine when there are > 2 categories selected

also

this works fine when there is 2 or >2 category selected

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

"more than 2",

"not more than 2")

it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!

help please

thx

IIf evaluates everything in the expression and does not employ shortcutting. This means the expression Parameters!LOOKUPITEMCategoryDescription.Label(2) will cause a problem if there are less than two items selected, because it is evaluated regardless. This means that you will need to change the way you check if a parameter option has been selected, maybe by doing an InStr() on Parameters!LOOKUPITEMCategoryDescription, or by calling a function with the parameter and looping safely through it in the function.

Also, i would think it is better to hide you chart with an expression in the Visibility|Hidden property rather than trying to use the Filters property.

|||

You have to use your report code to achieve this. Have a global variable of type object array in your code and write a function in the code which will receive an object array and store it in the global variable. Then have an invisible textbox at the top of your report and call this function from the textbox expression like Code.YourFunction(Parameters!LOOKUPITEMCategoryDescription.Value).

Then write another function in the code which will receive the index and check if the count of the items in the object array is greater than the received index and return the appropriate item from the object array. Call this function in your Filter expression of the chart.

If you need example about how to do it, please let me know.

Shyam

|||

sluugy & Shyam

thx for this - should have posted an answer as i had already realised about vb evaluating the whole expression in IIF and written the function as described

thx

m