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.

Answer 2

One option would be to use a ReportViewer control in local mode.

The data is loaded into ASP-NET, you can the pre-process it, then pass it to the control for presentation.

I'm not sure of how SSRS would deal with custom code/DLL when you want to process all data before thinking about presentation (as in the =Code.ReverseGeocode solution mentioned). RBAR feels wrong at the text box or cell level.

Answer 3

I guess I don't understand why you just don't write the SQl to provide the data the way you want it. Can't you calculate the field through a function or use a where clause to filter the data further. You can do quite extensive data manipulation in a stored proc or query data source.

Answer 4

You can also use the results of an SSIS package as a data source (the package is executed on demand when the report runs).

This can be very helpful if you are trying to solve the problem of joining data from multiple data sources into a single dataset for example: a geocode web service and a relational result set.

I don't think a lot of people realize that this exists and how useful it is for handling EII type reports