SSRS. Dataset or View? Pros and cons.

In the last few months I've been using the tool SQL Server Reporting Services (SSRS), which was Microsoft's answer to Crystal Report. It uses Visual Studio and SQL Server as a way to publish report files as web pages.

A dataset is the "heart" of an SSRS report. It is simply a query that you run from the report. In many ways it's similar to running a view in SQL, in the sense that the query may join multiple tables, use parameters, use common table expressions (CTE's) and almost every function that a query can do.

One problem that may happen is if you have to aggregate your data in the dataset itself, but also you have to pass parameters, which may not map exactly to what is in the data source. Example: if you want to pass parameters that say Low, Medium, High, but your dataset says 0, 1, 2. You would have to convert the data in the dataset when it comes in and when it outputs. It can get complicated quickly.

For whatever reason you might want to create a dedicated view in SQL; a view whose sole purpose is to feed your report, and this feels like it should solve all your problems, because you put the burden of complexity at the SQL level. Better yet: anyone can modify the SQL, and as long as it outputs the same columns, your SSRS report should be OK. This is a good model when it comes to decoupling areas of concern.

But behind that apparently perfect system there is trouble waiting: you added one more dependency to your report. If for any reason someone decides to change names to the view, or modify one of the columns, then the report will break. This won't happen if the SQL query is inside the report; it is self contained.

There's also a general assumption with views that will worsen this situation. Generally, in the context of a datamart, it's assumed that one view can be used for multiple purposes, such as a data source for another view. It's very easy for anyone to see the results of your view and use it to create a multiple chain of dependency only using views. Unless you have a policy of not making any changes, your view will be subject to change

I have used dedicated views for reports before because they made the dataset selections simpler in the SSRS report, but the dependencies are just a problem waiting to happen, so I had a change of heart; now the way I would create a report with a query is entirely inside an SSRS report file. I lose the visibility to the SQL query, but I gain more long-term robustness.