Back Tracing Data from SSRS to source Data

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:

  • Datasources
  • Datasets
  • Parameters
  • Filters
  • Grouping

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:

  • Charts
  • Tables
  • Gauges
  • Maps

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.

SSRS Images from Database

SSRS Images from Database

SSRS Report Builder 3.0 is a very good tool once you understand it. But clearly some parts were built by eager, thorough coders and others were less ambitious. I fear the ability to embed images from a Database is one such example. Conceptually it is right, the executional is, well, less than exceptional. There are very few actual options for rendering, specifically, compressing the image for faster rendering. Now, I do realize that a developer could attach some code to accomplish it, however, it is not very realistic or easily accomplished. Perhaps in the next version they will update this…perhaps.

Friday 3/1/13

Heading to Lumber yard to get stuff to build and gardening center to get stuff for, well…the garden, duh!?!?

Also, gotta get the car inspected, oil change, and washed, real bad!!! WOW it’s dirty.  Of course that’s why I call it Rihanna, plus she sings to me, so there is that…

We’ll be back later to post some pix of anything in progress and or completed…if you ask nicely that is…