More fun with expression-based connection strings in Reporting Services
Books online has a good explanation of what you can do with expression-based connection strings in the help topic "Connecting to a Data Source" (https://msdn2.microsoft.com/en-us/library/ms156450.aspx).
For example, it shows you how to dynamically change the data source (server) that your report uses based on a parameter value:
="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks
It also gives some good guidance around what to expect when you play with this functionality...specifically:
Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.
...and...
Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Datasets window and the Parameters list will not update automatically.
A co-worker and I found one thing that was missing. Some people might want to dynamically change the SQL credentials used to access a data source, kind of like this:
= "server=(local);initial catalog=AdventureWorks;uid=" & Parameters!UserName.Value & ";pwd=" & Parameters!PassWord.Value
This gets a bit tricky. First, when you initially build your data source you must provide credentials to access the data source (in the "Credentials" tab) so that you can run/test your queries and get a list of fields populated in your dataset. Then (as the notes above mention), when your report is "ready", you go back in and change the connection string to an expression.
However, you'll find that once you publish the report, it continues to use the *original* set of credentials you set in the credentials tab...not the new "dynamic" ones provided in your parameters.
The trick here is that after you publish, you must remember to go to the the Data Sources tab in Report Manager and select the "Credentials are not required” option. It's only at that point that the username/password that you originally set for the report is ignored and SSRS starts using the dynamic ones.
...And one more quick note...Do you really want to pass credentials via parameter values in the first place? NO! Instead, I would probably build an expression based connection string that references code in a custom assembly which builds the entire connection string for me based on non-confidential information I pass in.
Comments
Anonymous
July 24, 2006
The comment has been removedAnonymous
December 19, 2006
The comment has been removedAnonymous
December 20, 2006
Sounds like your "expression/connection string" doesn't evaluate to a valid connection string once you put it together.Why don't you hardcode the connection string on a temporary basis, then drop a textbox on the report and make it's value the result of this expression. Then you can see whether or not the connection string itself is any good?Anonymous
December 27, 2006
Thanks, I've been down that route already. I did that exact thing. The expression evaluates fine and in fact all the custom function does right now is spit out the same static connection string I have been using.Do you have an example of code that does work as you suggested in your article?Anonymous
January 15, 2007
Does expression based connection strings work with stored proceduresAnonymous
May 16, 2007
Hi as you mentioned i have done. But it fails at real time it is working fine during preview of rdl file but once it deploy on the report server the we can dynamically pass database name through parameters but not the UserId and Password. so I think it may be theoratically possible but not practically.If you have code then please send!Anonymous
May 17, 2007
Sarvesh, you can do this. However, if you attempt to pass in a username / password, you do need to select that Credentials are not required (as mentioned above. Are you sure you did this?Anonymous
July 02, 2007
I initially encountered the same problems as Sarvesh. I found that I needed to configure an unattended report processing account to get this working. I followed the steps in this article:http://msdn2.microsoft.com/en-us/library/ms156302.aspxThere is more information is in the "No Credentials" section of this article:http://msdn2.microsoft.com/en-us/library/ms160330.aspxHope this helps,BenAnonymous
August 15, 2007
I am having the same type of problem. Works in designer but get the following error when deployed to server: An error has occurred during report processing.Error during processing of the ConnectString expression of datasource ‘Dynam’. Any Suggestions?Anonymous
November 27, 2007
The comment has been removedAnonymous
November 28, 2007
The comment has been removedAnonymous
December 09, 2008
Hi,I want to use a dynamic connection string to display my reports, but this sample doesn't work in my case.My data source name is : xxx_clearquest and my static connection string is :Data Source=nanrp1001nanrp1001;Initial Catalog=CSAProdWhen I use this static data source my reports are well displayed. So i add in my report the "ServerName" variable with the default value "nanrp1001nanrp1001", and modify the data source connection string like this :="data source=" & Parameters!ServerName.Value & ";initial catalog=CSAProd"All seems to be right, but when I display my reports the following error occurs :An error has occurred during report processing.Cannot create a connection to data source 'xxx_clearquest'.For more information about this error navigate to the report server on the local server machine, or enable remote errors.The report cannot connect to the data source. I try to found logs on server, but don't found anythings refers to this error.Have you got any suggestions ? ThanksAnonymous
December 09, 2008
I finally found my issue.Expression-based connection seem to doesn't work with shared datasources. I add an embedded datasource in my report and it's finnaly work.Helpfull link : http://msdn.microsoft.com/en-us/library/ms159165.aspxAnonymous
January 21, 2009
PingBack from http://www.keyongtech.com/2195316-expression-based-connection-stringsAnonymous
January 28, 2009
I was able to get the expression based connection string to work in BIDS and when it was deployed. It took a few tries to get it right, but when i did, it worked in both...but thank you for your post, it was helpful...Anonymous
May 21, 2009
thank you if you could explain about oracle connection string in ssrs 2008 i would be gladAnonymous
June 19, 2009
PingBack from http://debtsolutionsnow.info/story.php?id=3777Anonymous
April 01, 2010
hey i was working on reports with dynamic connection strings if they are to be entered manually my report works fine. but what i need is i need to populate the list of servernames in the network and if i select one of the server names that should generate a list of data bases for that server and then i need to connect to the query using those values for the connection string to my dataset that runs the actual report. if any body has any valuable information please feel free to post it here or else please mail me at crunchycocktail@gmail.com. guys this is urgent. i was able to generate the list of databases by enetering the name of the server parameter but i even want the servername as a parameter. thanks