SSRS Report – Dynamic Connection String and Parameters

If you're dealing with relatively small datasets, it's probably fine to get data from SharePoint lists but this is not supported directly until SSRS 2008 R2. However, you can try to adapt the technique described here for earlier versions:

http://www.codeproject.com/Articles/24469/SQL-Reporting-Services-data-from-SharePoint-lists

This example explains how to do this with SharePoint 2007 - I'm not sure what you'd need to do to make it work with SharePoint 2010. You don't indicate in your question which version of SharePoint you're using, so I'll throw it out there just in case it's helpful.

If you're using SSRS 2008 R2, then you have a built-in SharePoint list source which you can learn about here: http://www.mssqltips.com/sqlservertip/2068/using-a-sharepoint-list-as-a-data-source-in-sql-server-reporting-services-2008-r2/

I would consider it a reasonable source if the reports run fast enough. If they don't, you might need to set up a separate database so that you can tune the queries and/or the data to get better performance.

Ok i figured out how to do this...i was doing right things on the report server (setting parameters, visibility etc) but was making mistake on notifying the report service to include the new parameter value during current report execution, so parameter value was being set correctly but wasn't part of current execution cycle.

In order to achieve this i called LoadReport2 method on ReportExecutionService of SSRS http://technet.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.loadreport2.aspx

This returns me with ExecutionInfo2 which contains ExecutionId for current report

http://technet.microsoft.com/en-us/library/reportexecution2005.executioninfo2.aspx

Using this ExecutionId and setting value for ONLY the parameter (dynamic connection string parameter in my case) i want to change, make call to SetExecutionParameters2 of SSRS report service

SetExecutionParameters2(executionId, new ServiceValues{Name & Value})

http://technet.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.setexecutionparameters2.aspx

Now the report runs as expected with dynamic connection parameter as Hidden with no default values and also loads up the dependent parameters on the report using dynamic connection :)

Thanks again to Irb and Ian for inputs