I have a requirement to create dynamic connection for SSRS reports where the connection information (Server & DB) will be worked out in c# code, so no interaction with end user selecting(Server or DB).
Catch here is report on its load will connect to specified DB and then populate the several other parameters like dropdowns etc at runtime using current connection.
For same i went down the route of Expression based connection string in SSRS. It all works well till i keep the "Dynamic" connection parameter type "Visible" and during report run pass the connection string from frontend which is not what i want.
How can i run report with dynamic connection from backend and then populate other parameters on report load itself?
Best Answer
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