Creating an SSRS Report with a Dynamic Datasource

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.

NewReport

Create a datasource (not a shared datasource).

DataSource

Create a dataset that uses the datasource you’ve just created.

DataSet

Create a very simple report.

SimpleReport

Preview the report to ensure that it’s working.

Preview1

Now, to make it dynamic I’m going to add a server name parameter.

ParamProp1

ParamProp2

Having done this I need to alter the data source connection string to make use of the new parameter.

DataSourceExprButton

DataSourceExpr

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.

Preview2

Preview3

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!

Happy reporting!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s