Use Entity Framework as SSRS DataSource (.rdl files)

Question

I have 100's of .rdl files residing in my report server & all the reports are using shared datasource and embeded datasets in each report. I was using stored procedures for querying db. All works fine here. I have some problems working with .rdl files.

1. Not able to upload the .rdl files one by one & specify the datasource to each uploaded .rdl files.

2. Also, Planning to use the web application EDMX as the datasource for all my reports. So that, I can use any database server to connect to my data with entity framework without changing much code changes. Because, stored procedures wont be feasible in my case.

   I need to know what is the best way of handling the above 2 scenario's. If you have some good knowledge about this, Guide me to keep going. I ll be highly appreciated.

Answer 1

1. You can programatically change the datasources of reports on the SSRS server by using the the SetItemDataSources web service method.
2. You can use the XML Connection Type to have report data come in from a XML web service such as OData instead of directly from a SQL server. There are limitations with this approach (E.x.: It doesn't support the 'Refresh Fields' in the 'DataSet Properties' window). A better alternative is to create your own Data Processing Extensions where you create the IDbCommand/IDataReader to connect to your EDMX and retrieve the data. This it allows you to decouple SSRS from your backend (E.x.: If you want to transfer your data as JSON, you can do that within your custom data procession extension since this isn't supported by the XML Connection Type.