Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier