How to choose multiple values for parameter in ssrs2008

Question

How to choose multiple values for parameter in ssrs2008

Ritesh_Dev

Ten Centuries

Points: 1299
More actions
November 23, 2011 at 9:04 am

#247387

I already asked about 2008 ssrs drop down menu for databases .. and what I did is ... just created the parameter(@Database) and changed the connection string like [="Data Source=servername;Initial Catalog="+Parameters!Database.Value]...
This is working fine when I select only one database at a time. But I am getting problem when I selected multiple databases. It gives me error. [The error has occurred during report processing]
Can some1 help me in this.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Can you elaborate a bit further please?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ritesh_Dev Ten Centuries Points: 1299 More actions · Answer 2

Hi,

I have to create drop down menu for databases so that same report can be use for different databases.

For that I changed the data source connection string and made it dynamic as in my previous post.

If i select one database it works fine. But when I select multiple databases then connection string doesn't support for that.

So can you provide me a way so that I can select multiple databases at the same time.

Thanks

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

You won't be able to use a multi valued parameter for the connection that way.

I would work it into the datasource's procedure. Something like ....

having the main proc accept multiple DBnames as a multi valued parameter and it handle the running of the procs on each db in that procedure

then returning that/those result sets to the report and group/page break etc... by DBname

or I'm misunderstanding the goal here. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ritesh_Dev Ten Centuries Points: 1299 More actions · Answer 4

Hi Jason,

Can you please explain in detail if possible with example.

Thanks

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

i'm making a bunch of assumptions on what how your report works, so bare with me... 😀

say your report simply lists all tables in the selected db's.

1. create the report

2. give it a parameter called @dbNames, multivalued

3. make the data source (procedure) of the proc accept the multivalued parameter and have it run the code for each db

4. returning all results in one result set (that includes the dbname)

5. have the report group and/or page break on new database name

any more details than that, you'll have to come up on your own. :w00t:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ritesh_Dev Ten Centuries Points: 1299 More actions · Answer 6

Thanks Jason!!!!

I will work on that.. Thanks again. 🙂