I’ve been asked this question a few times recently and so thought I’d put it on the blog as a “how to”.
This is going to be a basic report only to demonstrate the concepts and steps required in order to create a report that has a dynamic datasource. The data that this report returns isn’t going to be particularly useful but it serves as a nice demo regardless.
So… first things first start a report project and create a new report.
Create a datasource (not a shared datasource).
Create a dataset that uses the datasource you’ve just created.
Create a very simple report.
Preview the report to ensure that it’s working.
Now, to make it dynamic I’m going to add a server name parameter.
Having done this I need to alter the data source connection string to make use of the new parameter.
Click OK and then OK on the datasource properties box. Then when you preview your report again you have a dropdown list containing the two servernames. Select the server you want to run the report against and you should see that you get the server name and database count from each one.
As I said this is a very simple example of a report you can run using a dynamic datasource but it demonstrates the steps required. The most common example of where this sort of functionality can be useful is creating custom dashboards for server estates. You could even have a database table that contains all your server names, and alter the properties of the servername parameter to get its list of servers from that table. The possibilities are endless!