Hi, We have a report that I need to be able to select the user against which it is run. I want to be able to select a parameter that derermines which login is used. One user has access to the test schema and the other has access to the prod schema. The object names are the same. I am using an ODBC connection. I can use either a shared datasource or an embeded datasource. Any suggestions would be appreciated. Thanks, Trish Trish Leppa

Hi Trish Leppa, From your description, you would like to change the report data source based on report parameter, right? Please correct me if my understanding is wrong. If that’s the case, I suggest you can specify the data source as a parameter, include the parameter reference in the connection string. Please take the following steps as a reference. 1. We can create another report parameter named ServerName represent the data source, and create a dataset for the parameter like following(just a example): If @parameter=’user1’ Select ‘test schema’ as ServerName else Select ‘prod schema’ as ServerName. 2. Type in the following expression in Data source connection string (this is just a example): ="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks" After complete this steps above, if select user1, then test schema data source is displayed in the ServerName parameter pane, and if select others, the prod schema data source is displayed in the ServerName parameter pane. For more information about Expression-based Connection Strings, please refer to the following article, Data Connections, Data Sources, and Connection Strings (SSRS): http://msdn.microsoft.com/en-us/library/ms156450.aspx If you have any question, please feel free to ask. Thanks, Eileen

THanks so much! I think that will work.Trish Leppa

well since this is for Oracle ODBC so it will be different. And in Oracle the user is the schema. What you can do is create 2 ODBC connections, one to your test and another to your production environment. For simplicity sake you can name the ODBC connections the same name your Oracle users are called and I'll assume they both share the same password. (if they have different passwords you may have to embed embed the password within the connection string. Then create a SSRS text parameter with these two names as the available values. I named my parameter: dsource In your dataset create an embedded datasource to one of these ODBC connections. Test and make sure its working. You might as well set up your report layout now because your dataset fields will not update once you use an expression connection. Then go back to your data source and modify the connection string to an expression: ="Dsn=" & Parameters!dsource.Value