SUM or AVG using multi-value parameter in Expression

hi there!

i have ready-for-use dataset, have mutli-value parameter (integer type) use calculate sum or avg:

=sum(iif(fields!end_prd_id.value=join(parameters!avg_month2013.value,

","), fields!fact.value,nothing))

read lot join , split , array string used in multivalued parameters, code above works if 1 value selected in multi-valued parameter. in case choose 2 values values "" or blank, whatever... in case choose 3 parameters #error.

what need is: correct expression calculation based on multiple choice in multi-value parameter.

i know expression:

sum(iif(fields!end_prd_id.value=parameters!month_calc.value(0),fields!fact.value,

nothing))

works fine if there 2 selected values in multi-parameter.

please advice me way out! i`m going crazy :) thanx alot in advance!

hello,

in case, if choose multiple values in report, expression: join(parameters!avg_month2013.value,",") return string value. field end_prd_id integar type field, cannot result. second expression post work fine in test, however, return value when end_prd_id value equal parameter’s first value. because of select first value of parameter in expression: parameters!month_calc.value(0).

in order solve problem, can try use expression below:
=sum(iif(instr(join(parameters!month_calc.value,","), cstr(fields!end_prd_id.value)), fields!fact.value,nothing))

if have questions, please feel free let me know.

regards,
alisa tang

SQL Server  >  SQL Server Reporting Services, Power View