How to choose multiple values for parameter in ssrs2008

0
votes

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 of DataSource 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

1
ssrs-2008reporting-services
Please don't use textspeak (such as "some1") when asking questions on SO. - user359040

1 Answers

0
votes

You can't select from a parameter-specified database where the parameter has multiple values. This is because your query only includes a single select statement - you would need multiple selects unioned together, when trying to union the results from multiple databases.

I recommend that you uncheck the option to select multiple values from the database parameter.

If you absolutely have to be able to select from multiple databases specified by a singular parameter then you will need to rewrite your query like so:

select 'DB1' DBName, C1, C2, C3 from DB1.T1 inner join DB1.T2 on T1.C4 = T2.C4
where 'DB1' in (@SelectedDatabase) UNION ALL
select 'DB2' DBName, C1, C2, C3 from DB2.T1 inner join DB2.T2 on T1.C4 = T2.C4
where 'DB2' in (@SelectedDatabase) UNION ALL

...

You would need to hardcode the name of each database to be selected in each SELECT clause, and would need to have a select clause for each of the databases that could possibly be selected.