2005: displaying multi-select parameter values in a text box

I am using Reporting Services 2005. My parameter is a multi-select one. I
want to display what parameters the user chose when they run the report so
others know what data the report was filtered by. When I put the below text
string in a text box, I get an error. When I put the index value after the
string it shows that item if there is one for that index #, and an error if
there isn't. If they chose 5 items for that parameter, I want all 5 to show
up automatically and not have to put the index number for each one to display
it.
=Parameters.pState.value (this gives a query error)
=Parameters.pState.value(3) (this displays the value of the 4th parameter
value they chose. If they didn't choose 4 parameters, then it gives an array
out of bounds error)
If they chose 4 items, I want a piece of code that will automatically show
all 4 (e.g. AZ, NM, NY, FL)
thanks in advance!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
Hope this helps,
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
>I am using Reporting Services 2005. My parameter is a multi-select one. I
> want to display what parameters the user chose when they run the report so
> others know what data the report was filtered by. When I put the below
> text
> string in a text box, I get an error. When I put the index value after
> the
> string it shows that item if there is one for that index #, and an error
> if
> there isn't. If they chose 5 items for that parameter, I want all 5 to
> show
> up automatically and not have to put the index number for each one to
> display
> it.
> =Parameters.pState.value (this gives a query error)
> =Parameters.pState.value(3) (this displays the value of the 4th parameter
> value they chose. If they didn't choose 4 parameters, then it gives an
> array
> out of bounds error)
> If they chose 4 items, I want a piece of code that will automatically show
> all 4 (e.g. AZ, NM, NY, FL)
> thanks in advance!|||Thank you Robert. You are a God among men! This was SO helpful...
"Robert Bruckner [MSFT]" wrote:
> 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
>
> Hope this helps,
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
> >I am using Reporting Services 2005. My parameter is a multi-select one. I
> > want to display what parameters the user chose when they run the report so
> > others know what data the report was filtered by. When I put the below
> > text
> > string in a text box, I get an error. When I put the index value after
> > the
> > string it shows that item if there is one for that index #, and an error
> > if
> > there isn't. If they chose 5 items for that parameter, I want all 5 to
> > show
> > up automatically and not have to put the index number for each one to
> > display
> > it.
> >
> > =Parameters.pState.value (this gives a query error)
> > =Parameters.pState.value(3) (this displays the value of the 4th parameter
> > value they chose. If they didn't choose 4 parameters, then it gives an
> > array
> > out of bounds error)
> >
> > If they chose 4 items, I want a piece of code that will automatically show
> > all 4 (e.g. AZ, NM, NY, FL)
> >
> > thanks in advance!
>
>

2005 reporting services and parameters

I have created an SP that has 3 parameters.
I would like to creat a report using this SP. in other reporting tools I
have created the report and I had it call an SP instead of using a query.
the reporting tools would alway add the paramters to the report and ask for
the info.
I can't seem to create a report using SQL reporting services. I add
"exec ProcNAme" in the report wizard, but it tells me it is expecting
paramaters. I can't hard code the paramters, that would defeat the purpose
of params.
TIA,
JoeSupport for sp in the wizards is weak. Hard code the parameter just for the
wizard.
exec yourproc 'sometext', 0.101,'someothertext'
After running the wizard go to the dataset, change the command type to
stored procedure and put in just the name of the stored procedure (do not
put exec).
yourproc
RS will now recognize the parameters and add report parameters to match the
query parameters and map them. It is important to understand that query
params and report parameters are two different things. The reason it is
important is because you can base a query parameter on a expression instead
of using a report parameter.
Anyway, that is an aside. Follow the above and it should work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:0E52E72C-190A-4052-BA09-4527EB8831B3@.microsoft.com...
>I have created an SP that has 3 parameters.
> I would like to creat a report using this SP. in other reporting tools I
> have created the report and I had it call an SP instead of using a query.
> the reporting tools would alway add the paramters to the report and ask
> for
> the info.
> I can't seem to create a report using SQL reporting services. I add
> "exec ProcNAme" in the report wizard, but it tells me it is expecting
> paramaters. I can't hard code the paramters, that would defeat the
> purpose
> of params.
> TIA,
> Joe|||Thank you! This was driving me crazy.
"Bruce L-C [MVP]" wrote:
> Support for sp in the wizards is weak. Hard code the parameter just for the
> wizard.
> exec yourproc 'sometext', 0.101,'someothertext'
> After running the wizard go to the dataset, change the command type to
> stored procedure and put in just the name of the stored procedure (do not
> put exec).
> yourproc
> RS will now recognize the parameters and add report parameters to match the
> query parameters and map them. It is important to understand that query
> params and report parameters are two different things. The reason it is
> important is because you can base a query parameter on a expression instead
> of using a report parameter.
> Anyway, that is an aside. Follow the above and it should work for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:0E52E72C-190A-4052-BA09-4527EB8831B3@.microsoft.com...
> >I have created an SP that has 3 parameters.
> > I would like to creat a report using this SP. in other reporting tools I
> > have created the report and I had it call an SP instead of using a query.
> > the reporting tools would alway add the paramters to the report and ask
> > for
> > the info.
> > I can't seem to create a report using SQL reporting services. I add
> > "exec ProcNAme" in the report wizard, but it tells me it is expecting
> > paramaters. I can't hard code the paramters, that would defeat the
> > purpose
> > of params.
> > TIA,
> > Joe
>
>|||Have you also noticed that SQL 2005 reporting services does not like #temp
tables?
I dont understand what Microsoft was thinking, I use temp tables in just
about every Stored Proc I write.
Thanks,
Joe|||Not true.
I use temp tables all the time.
Do the following. Do not explicitly drop the table in your stored procedure.
I.e. do not do this:
drop table #temp
Let it fall out of scope naturally.
Second, do a select statement before exiting your stored procedure.
Something like this:
select * from #temp
Just about every stored procedure I use uses temp tables without any problem
whatsoever.
If you continue to have problems post some examples of what you are doing.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@.microsoft.com...
> Have you also noticed that SQL 2005 reporting services does not like #temp
> tables?
> I dont understand what Microsoft was thinking, I use temp tables in just
> about every Stored Proc I write.
> Thanks,
> Joe|||Thank you. I will try this on my next proc. Most of my temp tables are
created using select into statements. This could be my issue.
Thanks again for the help.
Joe
"Bruce L-C [MVP]" wrote:
> Not true.
> I use temp tables all the time.
> Do the following. Do not explicitly drop the table in your stored procedure.
> I.e. do not do this:
> drop table #temp
> Let it fall out of scope naturally.
> Second, do a select statement before exiting your stored procedure.
> Something like this:
> select * from #temp
> Just about every stored procedure I use uses temp tables without any problem
> whatsoever.
> If you continue to have problems post some examples of what you are doing.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@.microsoft.com...
> > Have you also noticed that SQL 2005 reporting services does not like #temp
> > tables?
> >
> > I dont understand what Microsoft was thinking, I use temp tables in just
> > about every Stored Proc I write.
> >
> > Thanks,
> > Joe
>
>|||Select into is fine. But, you also need to finish up with a Select * from
#temp.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:9743C586-BF52-4FAB-ABF0-84A09F22958D@.microsoft.com...
> Thank you. I will try this on my next proc. Most of my temp tables are
> created using select into statements. This could be my issue.
> Thanks again for the help.
> Joe
>
> "Bruce L-C [MVP]" wrote:
>> Not true.
>> I use temp tables all the time.
>> Do the following. Do not explicitly drop the table in your stored
>> procedure.
>> I.e. do not do this:
>> drop table #temp
>> Let it fall out of scope naturally.
>> Second, do a select statement before exiting your stored procedure.
>> Something like this:
>> select * from #temp
>> Just about every stored procedure I use uses temp tables without any
>> problem
>> whatsoever.
>> If you continue to have problems post some examples of what you are
>> doing.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
>> news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@.microsoft.com...
>> > Have you also noticed that SQL 2005 reporting services does not like
>> > #temp
>> > tables?
>> >
>> > I dont understand what Microsoft was thinking, I use temp tables in
>> > just
>> > about every Stored Proc I write.
>> >
>> > Thanks,
>> > Joe
>>