SSRS - Dynamic Connection Strings
One of the cool features of SSRS in the September CTP is the ability to change data source connection strings dynamically using functions.
In RS2000 with a little bit of work we were able to give users the ability to repoint a report to a database with a consistent schema on the same server by using dynamic SQL in the data tab and passing the database name as a query parameter like this:
="select ContactName, City, PostalCode from " & Parameters!DBName.value & "..Customers"
Sending the user to a different server was possible but took a bit more work.
The RS team have now solved the issue by adding a function button to the connection string dialogue box when defining the data source for a data set.
Now, instead of stringing dynamic SQL queries together you can simply create a parameter-driven data source string. The DB name, server name and any other part of the connection string you want can change according to your design. There are quite a few ways I can think of using this new functionality. A particular SQL2005 example? Think database snapshots: give your users the ability to report off their choice of a regularly created database snapshot. Simple user-driven point-in-time reporting. Very interesting...
I've created a simple example report which dynamically connects to one of three different databases. In order to set up the databases execute the script in DynamicDBCreate.sql. This will create 3 databases each with one table called MyTestTable with a database specific record in each. Import the Dynamic Data Source.rdl file into a SSRS 2005 solution and run it. There is no need to create a shared data source. Dynamic connection functionality can only be embedded in a report, the connection function button is disabled on shared data sources. Once finished you can clean up the databases by running the DynamicDBDrop.sql script. Download the files here.
Enjoy!
18 comments:
i cant't download your Samplecode.
Strange. What exact problem are you having?
In your Blog, you say that it is also possible to make the server connection dynamic in SSRS 2000 although it takes some efort. Do you have an example of how this works?
In order to achieve this you first need to create a Linked Server on the server you are initially connected to. You then reference this in your dynamic SQL string to connect to the other server. Instead of using the report parameter to dynamically add just the [DatabaseName] part of the string you would be passing in [LinkedServerName].[DatabaseName]. For example the finished string would look something like: select * from MyLinkedServer.Northwind.dbo.Customers instead of the single server alternative select * from Northwind..Customers.
HTH
Nick
The exact problem I'm having is that the Samplecode won't download.
Just checked. The code is definitely still available. There may be something in your environment that is blocking it. If you like email me at nick.barclay[at]gmail.com and I will send you the sample code.
Cheers,
Nick
How come I don't see the "Function button" is enabled when creating a shared data source using VS 2005? The trick to dynamically change connection string does not work if this is not allowed. Pls tell me why. Thanks!
The function button is disabled because you can't dynamically change a shared data source - this is by design. The data source for reports with dynamic connection functionality need to be local (i.e. not shared) to the report itself.
HTH,
Nick
Okay,
I am new to SSRS and wanted to know how can I create a Local data source for a report.
Also , I want the same report to dynamically fecth the server and Database Name and other connection properties from my connection strings that I store locally as UDL files.
Is ur this post would do the trick ?
Thanks,
Asmita
I am getting 'connection string expression are not allowed in shared data sources" error after giving the connection string as "="Data Source=(local); Initial Catalog=" &Parameters!DB.Value". How can I give the dynamic connection string
You can't have a shared data source with a dynamic connection string. The data source must be local to the report.
HTH,
Nick
That anonymous guy is very rude, what does he want? the moon on a stick!
Thanks for the article.
My problem is that I am having a parameter like this..
=Parameters!SQL.Value
While deploying in VS 2005 i am getting the error message
"[rsParameterReference] The ConnectString expression for the data source ‘DS1’ refers to a non-existing report parameter ‘ConnectionString’. ".
What could be the reason.. Could you help me to sort this out?
thanks
Raj
hai,
I am trying to a offline report with individual connection can any one help me out
i am trying to connect
Hi Nick,
This is related to passing connection string to a ssrs report as parametrs. I can change my server name, database as per the user requiremnt. I am using following code.
="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DBName.Value & ";User=" & Parameters!UserN.Value & ";Password=" & Parameters!pass.Value & ";"
And its working fine without any report parameter.
But when i use report parameter in a where clause and try to execute the same it gives me error as "Error during processing ConnectionString expression of dtasource 'DS1'" DS1 is a data source name.
Please let me know if there is any solution for this problem.
Thanks,
Tushar
Thank you very much Nick, It has worked perfectly OK.
The reason so many people confused is that they are trying to see the expression on shared connection.
I thought a screen shot process might have cleared the ambiguity.
A picture is worth more than 1000 words.
Thanks again
Nick one more question,
Say there are 3 snapshots
DBTest1
DBTest2
DBTest3
Assume 1 2 3 are monthly snapshots.
I have a report that should point to the correct month each time I ran the report.
I hope you my point.
ConnectionProperties>
SQL
="Data Source=SESH-BEJAWADA\MS2008;Initial Catalog=" & Parameters!Database.Value
true
Parameters!Database.Value this value should be connected to DBTest1
when the report is run in Jan Month and DBTest2 for February month.
Any idea through GUI. I am planning to modify the the month number by modifying the rdl file with xml parser.
Do you think my aproach is fine.
Something like below.
string theXml; // initialized before we get here
XmlDocument doc = new XmlDocument();
doc.LoadXml(theXml);
// find the element(s) we want to modify, using an XPath query
XmlNode geekNode = doc.SelectSingleNode("/OrgChart/Techies/Geek");
// modify the node
geekNode.InnerText = "Fred";
geekNode.Attributes.RemoveAll();
XmlAttribute attr = doc.CreateAttribute("surname");
attr.Value = "Flintstone";
geekNode.Attributes.Append(attr);
string fileName = @"c:\Mydir\Mysubdir\Mydoc.xml";
XmlDocument myDoc = new XmlDocument();
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
myDoc.Load(fs);
// find the element(s) we want to modify, using an XPath query
XmlNode geekNode = doc.SelectSingleNode("/OrgChart/Techies/Geek");
// modify the node
XmlAttribute surNameAttr = geekNode.Attributes["surname"];
surNameAttr.Value = "Flintstone";
// write the document back to the file system
fs.Seek(0, SeekOrigin.Begin);
fs.SetLength(0);
myDoc.Save(fs);
Let me know your thoughts ..
Please mail me if possible
to abbi1680@gmail.com
Waiting for your reply
Post a Comment