- Open the Data Source Explorer view
- Create all the connection profiles you need. e.g. test, staging and production for each connection type. Give them names which indicate the environment they are in (MS_SQL_Test, MS_SQL_Prod etc.)
- Export the required connection profiles to a file in the report project e.g. connectionStore.xml
- In the report project, create a new DataSource and select the second radio button 'Create from a connection profile in the profile store'
- Select the profile store file and choose a connection profile. Perhaps deselect the 'Use default data source name' and enter a environment-independant name (e.g. MS_SQL instead of MS_SQL_Test). Choose Ok to end
- Open the Data Source and select 'Property Binding'
- Edit 'Connection Profile Name' -- params["__runtime"].value.toLowerCase() == 'prod' ? 'MS SQL Prod' : 'MS SQL Test' -- or something similar. The returned value must correspond to the name of a connection profile in the store.
- Define a parameter named '__runtime' and use the Combo Box option to set the valid values: 'test', 'prod' etc
- That's it
When I deployed this to the server (websphere) the profile switching between prod & test did not work. This was because the profile store was not being found. Unfortunately, there is no error message - it just takes the default connection settings. The connection store needs to be placed in the server's context root.