Creating Dynamic Data Sources in SSRS
For my first blog post, I decided to write about a cool little project that came across my desk a few months ago. The request was to create a SSRS report that could be used to insert records to a database by supplying the user with dropdown parameter values from a list of tables. With a simple stored procedure, you can easily set up a report to insert records to a table, but there was an added requirement for the user to be able to select the site (database) that the values should come from. As the user needed to be able to select the site first, the rest of the parameters needed to be set up depending on which value they selected. You can set up the data sources dynamically using a couple different techniques, but I’ll explain how I approached it.
The first step that I took was creating a table within my DBA database to host the possible databases for each site. (If you don’t have a DBA database set up on your instance, I highly recommend it, as they can be very handy when setting up dynamic packages/reports as well as testing new processes.) I also added a bit “isActive” column to allow users to easily come in and adjust which sites should be used.
My Local Table is Set Up and Ready for the Report:
Once you have the table set up, you can begin creating the initial data source to be able to pull back these values. The first data source you want to create will be connecting to the instance and database that you just created your site list table in. Using the below query in your dataset, you can bring back the list of active sites for the user to choose.
Once you have your dataset configured, you will want to create a parameter on the report for the user to be able to select their requested site. You can create this parameter just as you would any other by right clicking on the Parameter folder then going to Add Parameter and filling in your name and Label. For the available values, you will want to add in the data set that you just created with the fields being your Database Names.
Configuration for the Database Selection Parameter:
Now that the user has selected their requested site, you can create the dynamic data source to be used for the rest of the parameters. To create this, you will want to add a new data source and then click on the expression button beneath edit.
Setting Up the Data Source to Use an Expression:
In this case, all of the databases are stored on the same server so you are able to hardcode in the server name, but you will need to be able to pass in the users selection for the database. To do this, you are able to choose the Parameters category to make the selection. The syntax for your connection string should now look similar to this:
="Data Source=SERVERNAME; initial catalog=" & Parameters!SelectSite.Value
Expression Set Up and Ready for Use:
You can now use this data source for the remaining datasets to pull back any other parameters that may be needed. I’ve found that you will need to create another temporary data source to one of your databases when setting up your datasets in order to populate the available columns. Once the columns are validated, you can change the connection back to your dynamic data source and everything will be good to go.
Username Dynamically Changes Depending on Your Site Selection:
You can also easily modify this expression to dynamically set the Instance your report runs against instead. I have found this technique very useful when creating dashboards, as you can easily select which instance to check. What if you needed the user to be able to select both the SQL instance and the database? Well, you could store the full connection string within an added column in your table and, when setting up your parameter, use that column for your returned Value field. From there, you can adjust your data source expression to pass in the selected value.
I hope you’ve enjoyed my first blog post. If you have any feedback or questions, please leave them in the comments below or look me up on my Google+ profile! Check back soon for other great blog posts from my colleagues and me in the near future.