How to use Dynamic data sources in your SSRS report.

You can have situations in which your report should be executed on multiple databases. The user should be able to select the desired database on which the report should run. In this blog post I will explain how you can do this.
To be able to select to different database you need to make use of a dynamic data source. A data source makes use of a connection string. It is possible to pass the connection string of a data source as an expression. By using an expression, you can make use of parameter values to pass the servername and database name to the connection string. There is only one restriction of a dynamic data source. The data source should be embedded within the report. It can not be implemented with a shared data source. In this blogpost I will use a second database in which I retrieve the available SQL server\databases on which my reports should be executed.

• Open your report
• Add 2 report parameters
• ServerName
• DatabaseName
• Add datasource named: DynamicDataSource. Use the a 'hard coded' connection string. For instance:  Data Source=MySQLServer1;Initial Catalog=MyDatabase1.

• Add datasource to the database with all SQL Server\databases. In my example named: Synergy
• Add a embedded dataset to retrieve SQL Server and Database information.
  
  
• Configure the available values for the report parameters: ServerName and DatabaseName.
  
  
• Add all datasets and report items to your report.
• Test your report using the 'hard coded' connection string.
• If everything works fine, change the 'hard coded' connection string with next expression
  ="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value
  
  
• Run the report and select a value for the report parameters ServerName and DatabaseName 

Enjoy it.

How to change the database for your PowerPivot sheet.

As of Office 2010, you can use PowerPivot in combination with Excel. The PowerPivot is a very powerfull tool to analyze the data in your database. After having build some nice pivots on one of your databases, it can happen that you want to re-use the same sheet on a different database.

In this blog I will explain how you can update your PowerPivot sheet with data from a different database.

• Open your Excell sheet.
• Select the PowerPivot ribbon tab.

• Select the PowerPivot Window button in the ribbon. The PowerPivot window is now started.
• Select the design tab.
• Select button: Existing Connections.

• Select your PowerPivot data connection.

• Press edit button.
• Change the server and or database you want to use.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• Press the 'test connection' button to see if you can connect to the database.
• Press save.
• Press Close
• Select the Home tab in the PowerPivot ribbon.

• Select Refresh all button. Now the data refresh window is displayed.

 

• Press Close when data is updated successfully.

Now we need to update the excell sheet with the data in the power pivot sheet.
 

• Go back to your excell sheet.
• Select the Data tab in the ribbon.
• Select the refresh all button.

Now all data in your excell sheet is updated with the data of the selected database.
Enjoy analyzing your data with the PowerPivot.