"Bruce L-C [MVP]" wrote:

> Here is a some good information posted by Robert Bruckner (a MS employee) in
> 2005 when multi parameters first came out and I too was in a learning curve.
> >>>>>>>>>>>>>>>
> Some additional information on multi value parameters in general: If you
>
> change a report parameter to be multi value, the .Value property will return
>
> an object[] rather than an object. Hence you can no longer e.g. write
>
> expressions like =Parameters!MVP1.Value.ToString().
>
> To access individual values of a multi value parameter you can use
>
> expressions like this:
>
> =Parameters!MVP1.IsMultiValue
>
> boolean flag - tells if a parameter is defined as multi value
>
> =Parameters!MVP1.Count
>
> returns the number of values in the array
>
> =Parameters!MVP1.Value(0)
>
> returns the first selected value
>
> =Join(Parameters!MVP1.Value)
>
> creates a space separated list of values
>
> =Join(Parameters!MVP1.Value, ", ")
>
> creates a comma separated list of values
>
> =Split("a b c", " ")
>
> to create a multi value object array from a string (this can be used
>
> e.g. for drillthrough parameters, subreports, or query parameters)
>
> See also MSDN:
>
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
>
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>
> >>>>>>>>>>>>>>>>
>
> One of the things I do sometimes with a large multi-select is to have my
> expression showing the parameters picked to check the number and if over a
> certain amount I just say something like > 10 parameters selected.
>
> Otherwise do this:
>
> ="Calendar Year(s) = " & " " & Join(Parameters!MVP1.Value, ", ")
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ryan Mcbee" <RyanMcbee@discussions.microsoft.com> wrote in message
> news:7E7CFDDB-3334-45F8-B4BD-944D2F767E0F@microsoft.com...
>
> > Bruce,
> > This fixed the problem, I am writing this down for future reference. I am
> > now getting a problem with a text box to show the input parameters in the
> > report. In my text box, I have the following formula "="Calendar Year"+"
> > "+
> > Parameters!Year.Value(0)"
> >
> > I would expect it to show all of the years that I selected, but I am only
> > seeing the first year. Any thoughts on this?
> >
> > Thanks,
> > Ryan
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This is your problem.
> >>
> >> AND (UPR30100.YEAR1 = @Year)
> >>
> >> This means year equals a value, not multiple values. As I said, if the
> >> syntax would not work if you hard coded it with commas then it won't work
> >> with a query parameter that accepts mutliple values.
> >>
> >> It needs to be
> >> AND (UPR30100.YEAR1 in(@Year))
> >>
> >> This will work for one or multiple.
> >>
> >>
> >>
> >> "Ryan Mcbee" <RyanMcbee@discussions.microsoft.com> wrote in message
> >> news:BC2AEF43-101B-44CE-AE2F-0C975792958D@microsoft.com...
> >> > Here is the syntax. My Year parameter is at the bottom. As I said, it
> >> > works
> >> > when I default in the year, but it won't let me select multiple years.
> >> >
> >> > Thanks,
> >> > Ryan
> >> >
> >> > "SELECT UPR30100.EMPLOYID, UPR30100.VOIDED, UPR30300.PYRLRTYP,
> >> > UPR30300.PAYROLCD,
> >> > SUM(UPR30300.UPRTRXAM) AS SUM, UPR30100.YEAR1 AS
> >> > YEAR,
> >> > UPR30100.EMPLNAME,
> >> > SUM(UPR30300.UNTSTOPY) AS Hours
> >> > FROM UPR30100 LEFT OUTER JOIN
> >> > UPR30300 ON UPR30100.CHEKNMBR = UPR30300.CHEKNMBR
> >> > AND
> >> > UPR30100.AUCTRLCD = UPR30300.AUCTRLCD
> >> > GROUP BY UPR30100.EMPLOYID, UPR30100.VOIDED, UPR30300.PYRLRTYP,
> >> > UPR30300.PAYROLCD,
> >> > UPR30100.YEAR1, UPR30100.EMPLNAME
> >> > HAVING (UPR30100.EMPLOYID = @Employeeid) AND (UPR30100.VOIDED = 0)
> >> > AND
> >> > (UPR30300.PYRLRTYP = 1)
> >> > AND (UPR30100.YEAR1 = @Year)"
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> We need to see the SQL you are doing. Think of it this way, if where
> >> >> you
> >> >> are
> >> >> putting in the parameter does not work with , separating the values
> >> >> selected
> >> >> then you are in trouble. Usually your query should be something like
> >> >> this:
> >> >>
> >> >> select somefield from sometable where anotherfield in (@MyParam)
> >> >>
> >> >> Note the above will work with a single value or multiple values.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Ryan Mcbee" <RyanMcbee@discussions.microsoft.com> wrote in message
> >> >> news:A01116B3-FC54-4C3F-96B0-F032BE913E31@microsoft.com...
> >> >> > No, the dataset is not based on a stored proc, it is a select
> >> >> > statement.
> >> >> > What will the Join() help with?
> >> >> >
> >> >> > Thanks,
> >> >> > Ryan
> >> >> > "Michael C" wrote:
> >> >> >
> >> >> >> Ryan,
> >> >> >> Is dataset1 based on a stored procedure? Have you tried using the
> >> >> >> Join()
> >> >> >> functionality?
> >> >> >>
> >> >> >> Michael C
> >> >> >>
> >> >> >> "Ryan Mcbee" wrote:
> >> >> >>
> >> >> >> > I have a query with a year parameter that works fine, but when I
> >> >> >> > make
> >> >> >> > the
> >> >> >> > year a multivalued parameter, i am getting an error that reads:
> >> >> >> > query
> >> >> >> > execution failed for data set 'dataset1'. incorrect syntax near
> >> >> >> > ",".
> >> >> >> >
> >> >> >> > Any ideas why it won't let me make the year a multivalued
> >> >> >> > parameter?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>