C# – Dynamic connections/Expression based connection string SSRS

cnetreporting-servicessql server

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

Related Topic