Do you ever get questioned regarding the validity of data provided by your SSRS Reports? I do regularly: ‘This does not look right’, ‘Are you sure that is the right number?’ that kind of thing. As a rule my answer is I don’t know. The report simply reflects the data that is in the database, so let’s look at it.
Understanding your finished product is all about understanding how SSRS collects and regurgitates the data in the data source. SSRS has:
I am not going to go into all of these, but suffice to say each of these is an opportunity for data to be mangled. The DataSource is basically the connection to the source table, view or stored procedure, typically in SQL, however it could be any number of other data sources. As a rule, I use SQL Servers or SharePoint Servers, There can be many DataSources in a single SSRS Report. The DataSet is just what the name implies. It provides data to elements in the report. A Dataset cannot provide data to a parameter or filter that is to be used to filter the very same dataset. That would cause circular logic and the world would implode, so don’t do it. There can be many datasets and usually tend to be many on any given report. Mind you that we can filter in the dataset using a where clause in a query, or we could filter using a filter expression defined on the dataset object. There are reasons to do one or the other and I am not going to dig into that right now. Within the report itself we can add objects that allow for grouping, filtering, sorting and whatnot. These include but are not limited to:
Trust me there’s lots more in there that we can set filter or a grouping and easily change the end product.
As you can see there are lots of ways just within SSRS to manipulate the data output. Unfortunately, we are not done yet. The Source of the data can also be manipulated to provide data in whatever manner we are looking for. If we are connecting to a table, well that’s pretty cut and dry (unless that is simply a manifestation of a view). However if we are connecting to a view or a stored procedure, then all bets are off, because those are capable of providing a manipulated view of any number of tables and other views.
Rule of thumb: document your SSRS to the best of your ability, and get to know your underlying data before you get started. Finally, be prepared to interrogate your data ad nauseum both for your understanding as well as justification of your end product.
SSRS is not simply an extension of Microsoft SQL Server, it is a monster all to itself. If you are not prepared to meet it’s challenges, you will never be able to meet those of the business users it serves.