How do I get non-Table data into SQL Server Reporting Services?

Question

Given: A C# calculation engine that loads an object model, crunches huge amounts of numbers, and saves the results to a couple of gigantic mega-indexed database tables in SQL Server. Those tables provide data to web interfaces, other software modules, and SQL Server Reporting Services 2005 reports.

I managed to make the engine a lot faster in the latest version of the software, fast enough now that it can provide the data on request - sometimes even faster than the time it takes to query the database for the pre-calculated numbers. I am very happy about this.

That breakthrough means that we can generate data on request for the web interfaces and other software modules. But the cache tables cannot die yet, because they're consumed by the SSRS reports (or more specifically, by stored procedures that query the tables and provide the data to SSRS.)

The cache tables are a pain, in much the same way that any cache is a pain in the world of software. Without going into too much detail, they have sync'ing problems, locking problems, etc etc. The software would work so much more nicely if I didn't have to worry about keeping those darned tables up to date.

But how else can I get the data into SSRS? I've done a fair bit of research and nothing looks too promising:

• We could provide the data via a web service and use the SSRS XML DPE. But that looks kind of hideous - am I right that you have to parse your SOAP envelope yourself?! And it doesn't support XPath, but a proprietary XPath-y dialect?? Our report writers know T-SQL, and that's what they're best at.
• Using the SQL CLR to host our API is not desirable - it's a big app and you can't do anything without creating an application object and logging in, etc.
• Using the SQL CLR to contact a web service on the web application - this is the most promising so far (this article was helpful http://www.simple-talk.com/sql/sql-server-2005/practical-sql-server-2005-clr-assemblies/.) Has anybody tried this approach? Does it perform okay, can it provide large data sets? OTOH I'm turned off by the extra setup we would have to do on client DB servers.
• Any other suggestions would be greatly appreciated.

Answer 1

If I understand you correctly, you're building a report on non-SQL data. You're storing the data in tables for the specific purpose of making them reportable.

I can think of two solutions. The first is to extend your C# calculation engine with a reporting part. The Microsoft.Reporting.WinForms and Microsoft.Reporting.WebForms namespaces can be used to build reports on any data source, not just SQL Server. If the end users use your app as a client, you can generate the data and the reports on the fly.

The second is to use the SQL CLR. You can use a CLR stored procedure as the basis for a report (enter "exec mysp" as the datasource.) This CLR procedure is C# code, and could include your calculation engine as a library. This would allow you to generate reports on the fly, while still using the Report Server user interface.

Interesting question and I'm hoping that more knowledgeable people can provide a better answer :)

Answer 2

I've been in a similar situation previously and tried both the SSRS XML data source and the reporting extension that Andomar mentions. My recommendation is to use the SSRS XML data source feature. If the data structure returned by the web service is simple then the XPath is also simple. I also found it easier to debug. Main thing to watch out for is the timeouts.

The good thing with using web services is that they are easily consumed by many different technologies so they can become very handy to have anyway.

Also, it's a personal choice but despite the SQL CLR features I still don't feel comfortable with putting code in the database. By using an XML data source in SSRS there is no need to involve custom CLR code at all.