How to parameterize linked server references in SSRS datasets?

Question

I'm developing a ASP.NET web application that displays some SSRS reports. My application's database is located in a different server from my report's database, considering that reports consume data from both servers there's a linked server in report database server to application's server.

In one of dataset reports I have a query as bellow:

SELECT T.column1, T1.column2 FROM localTable T JOIN [linkedServer].[databasename].tablename T1 ON T.id = T1.id

There's only 3 reports, so it was ok to replicate [linkedServer].[databasename] when i changed the application to another environment.

My question is: what's the best way to parameterize [linkedServer].[databasename] in order not to need replace all occurrences every time the server and/or database names change?

Answer 1

next to the query field in the dataset, there is a little fx which allows you to enter an expression for the query.

from your example the query would be

= "SELECT T.column1, T1.column2 FROM localTable T  JOIN [" + Parameters!ReportParameter2.Value + "].[databasename].tablename T1 ON T.id = T1.id"

or sth in the like.

in a off topic note i would also recommend to never use the server.database.owner.table syntax, but openquery(server, 'query'). this is for performance reasons, as the server.database.owner.table will copy the whole table over and the openquery retrieves only the result of the query.