asked on

SSRS 2016 - is there a way I can allow the user to choose between 1 of 2 data sources?

I have a report that needs to be run for 1 of 2 databases. The (2) databases are identical in structure, but not in data.

We have a company database that is identical in structure for 2 clients. I will call the clients "Coke" and "Pepsi" for this example.

I want the user to have a drop down in the report so the user can select to run the report for Coke or Pepsi. The stored procs that create the datasets exist in both Coke and Pepsi databases.

Is this possible or am I stuck managing 2 separate reports?

David Favor

Just have a selection mechanism for each database, so the user chooses either the Coke or Pepsi database + runs their report.

Seems simple.

Maybe I'm missing something about what your asking.

Try adding more detail to your question.

sqlagent007

ASKER

Yep, sounded simple to me too, but I can't find the place in SSRS report builder that allows me to create a drop down or radio button for the user to select the 2 different data sources.

I have 2 databases: "Coke" and "Pepsi". I want the user to be able to select what database before they click "view report".

Where can I create a drop down for data source selection?

Pawan Kumar

Here you will have to handle the things in DB. Create a SP and dynamically pass the DB name in there. And then use the 4 part name to get the data. Pass the database in to the below sp and all will be taken care of..

CREATE PROC GETDATA
(
	@DBName VARCHAR(50)
)
AS

BEGIN

	EXEC ( 'SELECT * FROM ServerName.' + @DBName + '.dbo.TableName' )

END

Open in new window

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nitin Sontakke

I believe OP is asking the selection (UI) part can be performed in SSRS itself, to which I believe, no. This is something that needs to be implemented in ssrs report invoking layer itself, i.e., outside ssrs.

sqlagent007

ASKER

Thanks: Nakul Vachhrajani, this article looks like what I need.

http://www.gethynellis.com/2011/07/ssrs-dynamic-data-sources.html

sqlagent007

ASKER

Thank you also: Pawan Kumar

However we can't have linked servers between the 2 environments = (

sqlagent007

ASKER

Thanks!