March 31, 2007 at 9:03 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/bKhawaja/2945.asp
May 3, 2007 at 4:29 am
I am afraid I cannot get this to work as it complains about connection string expressions are not allowed in Shared Data Sources, and does not allow closing the dialog. Was this something that used to work in an earlier version of SSRS I wonder?
May 3, 2007 at 10:59 pm
Hi Steve.
We are trying to get away from shared data source. You cannot use a shared datasource with this expression based connection string, it defeats the purpose of making it dynamic.
You pass in the server name and database name in the parameters or in the url to dynamically generate report against the database and server you are sending in. so your expression based connection string replaces the parameter names with actual name of the server and database like this.
="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value
Data Source=(local);initial catalog=Northwind
Hope i answered your question.
Bottom line is , You cannot use shared datasource in this case.
Bilal
May 7, 2007 at 3:45 am
Anyone got it working with multiple data sets? I get this error:
"Error during processing of the ConnectString expression of datasource ‘xxx’."
Any hint would be greatly appreciated!
Andrea
May 7, 2007 at 10:29 am
Ok, found it. the problem was only partial due to the multiple data sets: Of course, if you have other parameters in the report whose valid values depend on tables in the data base to choose, you have to put the server and db parameters on top of the parameter list ..
cheers
Andrea
May 8, 2007 at 5:10 am
I got real excited when I saw the article as this is something we need, but it is unfortunate that you cannot use it with shared datasources... We only work with shared datasources in SSRS...
Anyone has any ideas around manipulating connection information for shared datasources?
May 8, 2007 at 11:59 am
I've just tried this process and it works great for datasets that do not use stored procedure. When I use a stored procedure, RS cannot validate the procedure when I deploy and changes command type from stored procedure to text. Once on the server, the report can't execute the stored procedure(s) since they are now text.
Suggestions?
May 8, 2007 at 12:31 pm
OK. I stopped fighting RS and left the command as text and used the syntax
EXEC storedprocedure @parm1, @parm2
for the stored procedure.
Now I need assistance passing the server and database parameter in the URL instead of requiring the user to complete.
May 9, 2007 at 10:04 am
I still get an error, before I get to enter the server/database details, how does one stop this from happening?
Rosey
May 9, 2007 at 10:10 am
Sorry, I meant to ask, does this also work for embedded (Jump to) reports and if so, how do I get the embedded report to run using the same server/database parameters?
Thx,
Rosey.
Rosey
May 9, 2007 at 11:30 pm
Debby,
I only use Stored procedures and it works perfectly fine with Reporting Services 2005 expression based connection string.
Any time you edit the dataset, and try to save or hit ok after editing, it changes the commantype to Text from Stored procedure. So you have to manually change it back to Storedprocedure and Save the Report and then preview it or deploy it and it should work.
Hope it works for you now.
May 9, 2007 at 11:36 pm
Rosey,
it can definetly work for jump to reports. You just have to pass the serverName and database name to the target report that will be accepting these.
=Parameters!ServerName.Value
=Parameteres!DatabaseName.Value
May 9, 2007 at 11:47 pm
Adriaan,
Dynamic connection string is a Great feature in RS2005. When you use shared datasources, you HARDCODE the Servername and Database name in there which you cannot change.
What if you want to run the same report against a different Server and a different database? you have to create another dataset and tie that with the new report and re-deploy and now ure creating multiple reports for each server/database.
With Dynamic Connection string, You just need one report deployed on a WebServer.
you can run that report against any database on anyserver by simply passing in the servername and databasename.
That one report will generate different data each time you run the report against a different database.
What if you have 5 databases? you can create only one connection string in a dataset and thats hardcoded and cannot be changed when you deploy it. so that report will run against connectionstring/database you hardcoded.
With Dynamic connection string, nothing is hardCoded.. you simple pass it the Server and database names and it will show you the results.
Just One report against 100 databases!
May 9, 2007 at 11:55 pm
Debby,
You can pass in the ServerName and Database in the URL like this:
You need to have ReportViewer first and set its ReportServerURL property first:
Then do this in your code. you need to put your own server Name and database name and the path.
ReportViewerS1.ReportPath =
String.Format("/MyReportsFolder/ReportName&ServerName={0}&DatabaseName={1}", "Dev","Northwind")
Hope that helps.
Bilal
May 10, 2007 at 12:08 pm
I do not understand: "You need to have ReportViewer first and set its ReportServerURL property first: http://blockedcontent/ReportServer"
I am passing the url from an asp page (not .net) is this my problem? The url format is
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply