CODEWITHSUNDEEP
reporting-servicesdatasource
Justin R.
Justin R.

Reputation: 24071

How can I allow users to switch data sources for an SSRS report?

I have two SQL Server databases with identical schemas, but different data. I also have SSRS generating reports, in native mode, for one of the databases. All reports the same shared data source.

I would like to allow users to get reports for the other database. I created a second shared data source for the second database. Modifying the reports to use this second data source results in reports as expected.

Because the RDLs are the same, except for the data source, and because I don't want to maintain what are basically duplicate reports, I'm looking for a way to dynamically switch data sources, depending on user input.

Is there an easy means of accomplishing this? An existing solution would be best. Barring that, can the RDL's data source be parametrized? Or, can the RDS's connection string be parametrized?

Upvotes: 3

Views: 2255

Answers (2)

Jarid Lawson
Jarid Lawson

Reputation: 45

I'm going to piggy back on the last answer. Yes, use parameters in the connection string, but populate the data to ensure your users aren't seeing database names, etc. that you might not want them to see for security. You can store the values in a table, pass both the human readable and SQL Server specific values.

Does that make sense?

Upvotes: 0

AUSteve
AUSteve

Reputation: 3258

You can use parameters or other expressions in the connection string.
See http://msdn.microsoft.com/en-us/library/ms156450%28SQL.90%29.aspx (2005)
or http://msdn.microsoft.com/en-us/library/ms156450.aspx (2008)

Upvotes: 1

Related Questions