Determine how many multi-value parameters have been passed

[SQLWilts]

I believe, and correct me if I'm wrong, that when you choose a parameter to be a multi-value parameter, it passes it as a comma delimited string, but is treated like an array.

I want to (in the simplest form) have a title of a report that includes all parameters (for example, "Report of " parm, parm, parm)

However, I am seeing that RS knows it is a multi value parm and is offering parm(0), parm(1) etc as value when I edit the text box and make it ="Report of " parm.value. How can I display the whole list of passed parameters, whether the user chooses one value from the drop down box or all?

 

[xlbo]

I'm only on 2000 so not sure exactly how 2005 executes multi value params but maybe a code function along these lines:

Function ParamOutput(ParamValue as array)
dim retval
dim i
for i = lbound(ParamValue) to ubound(ParamValue)
 retval = Paramvalue(i) & ","
next i
retval = left(retval,len(retval)-1)
return retval
End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DietSquirt]

Here's one of mine, this expression is in a text box in the report header. Hope this is what you're looking for.

="Customer Product Sales for "& "Fiscal "&Parameters!FYR.Value & " - "& "Cycles: "& iif(PARAMETERS!Month.COUNT = 12,"All",Join(parameters!Month.Value,","))&vbcrlf & iif(PARAMETERS!BusinessGroup.COUNT = 9,"All",Join(parameters!BusinessGroup.Value,","))

I had to know how many business groups I had (months was easy!) and if we add a business group I'll have to fix this formula. There may be a way to write a formula that adapts to that, I'm still new with SSRS.