Best-Practice for SSRS Data Source in ASP.Net MVC Project

Question

I have an existing ASP.Net MVC project that is using entities / repositories in a separate project. I need to add reporting functionality to this project via SSRS and I'm wondering what the best way is to handle data access for it.

As I have migrator.net and a repository data layer implemented I feel that using the database directly and creating db-dependent sql is out of the question. My first thought was to create a web service inside of my web project and use that, but this requires me to write new code and maintain a web service that I currently do not need for anything else.

My next thought was to use my data assembly and leverage my existing repositories in code to populate the reports. Is this possible? I am new to this whole stack and am not very familiar with any of the tools.

How is this commonly achieved? In previous JEE projects we always used CXF and created a web service for our reporting tools, but I never felt that was the best solution. How is this commonly tackled in .Net/SSRS?

Answer 1

You can use a custom managed code assembly from your reports. This link and this link will get you started. I should point out though that this is NOT a trivial task, and there can be many things that get in the way of achieving this.

To be honest, i think you are asking for a whole bunch of pain in trying to achieve this architectural nirvana. It might feel dirty, but things are a lot simpler if you just set up a datasource that points to your database and have your reports use that (did i mention you could also consider a managed code SQLCLR assembly in the database? Did i mention that they too could be painful to implement?).

Let SSRS do what it does best - go to the database and crunch data and present it. You can have it use stored procs. You can use custom code within your reports. You can have the reports presented within your web page, or you can have them spawned separately via web/pdf/doc/xls etc, you can even deliver the report by email. Just don't try to shoe-horn it into a specific architectural model that it isn't really designed for - you will need the patience of a saint to achieve it.