CODEWITHSUNDEEP
reporting-servicesssrs-2012
KeentoLearn
KeentoLearn

Reputation: 355

How to switch datasources in SSRS report

I have a report and which is coming from database MasterDB. Now I have created a copy of the same database named MasterArchive. I have created parameters for databases to display in dropdown. So, When user select MasterDB from select DataSource dropdown then datasource should change according to selection.

how to change the datasource based on the selection?

Can anyone please guide me how to achieve this.

enter image description here

enter image description here

enter image description here

enter image description here

Upvotes: 1

Views: 1086

Answers (2)

StevenWhite
StevenWhite

Reputation: 6034

You can actually write an expression for your connection string by clicking that fx button below the Build button. For example, the expression would be something like:

="Data Source=...;Initial Catalog=" + Parameters!DataSource.Value

Upvotes: 1

Alan Schofield
Alan Schofield

Reputation: 21703

As far as I know there is no elegant way of doing this other than using dynamic SQL

So in you Dataset query it might look something like 

DECLARE @sql varchar(max)

SET @sql = 'SELECT * FROM ' + QUOTENAME(@datasourceParameter) + '[dbo].[myTable]'

EXEC (@sql)

It's rather clunky but it will work.

There does not appear to be away of editing the datasource, it seems to be set once during report execution even if it's based on a parameter. I did test this briefly but could not get it to work. It might be worth further investigation though as that would be a more elegant solution.

Upvotes: 0

Related Questions