Can I pre-process data before passing it off to SSRS 2005?

Question

We are considering migrating to Sql Server 2005 Reporting Services. Many of our existing reports require pre-processing of the data before rendering it.

For example, we have a query for a report that returns GPS coordinates (Latitude and Longitude) from a stored procedure, but before passing off the DataSet to our reporting engine (currently Crystal) we call out to a Web Service to reverse geocode the coordinates and get an address string. We push this into the DataSet object.

I've read a bit about Data Processing extensions but I'm not sure that's what I want, as then (if I understand correctly) I would need to implement the entire processing flow (including retrieving the data from the stored proc) just to massage it a bit at the end.

How can I interject and pre-process the dataset after retrieving it from the data source, but before passing it on to the renderer?

Answer 1

You could create a .NET assembly with a method in it that processes an individual record. Then, include that assembly in the report, and call that method when each row is being rendered. This would do the processing and display the result.

For instance, after creating the assembly and adding it to the report, you could have a table where one of the cell expressions looks like this:

=Code.ReverseGeocode(Fields!Latitude, Fields!Longitude)

See http://msdn.microsoft.com/en-us/library/ms155798.aspx for some guidance.