How to report across large enterprise systems

Question

Some time ago my company was evaluating different reporting solutions. We settled on MS SSRS particularly because it's capable of connecting to various types of data stores, including MS SQL, Oracle and SAP Netweaver BI. It has proven the test of time pretty well, however, we're now under fire from management because SSRS is not capable of mixing data sources into the same data set.

So, I searched long and hard for a reporting solution that can "inner join" data from separate systems, but I came up short. I am about to propose that we custom write reports (ASP.NET) for these cross-system report requests, but I wanted to ping the internet first for any advice.

How do you "inner join" across your massive enterprise systems for reporting purposes?

Answer 1

Take a look at BIRT from Actuate. BIRT comes in open-source and commercial flavors and I believe it allows joins across data sources.

Answer 2

Perhaps a linked server in SQL Server? Watch out for performance issues and I'm not sure if SSRS has limitations against them - I don't think that it does. You can reference a table like this - MYSERVER01.DATABASE1.dbo.TABLE. More info from the source.

For best performance you would be pulling all your disparate data in to a data warehouse, but that is a major undertaking that management may not be willing to fund.

Answer 3

One way to join across data sources in SSRS is to use subreports - see http://msdn.microsoft.com/en-us/library/ms159837.aspx .

Peformance is unlikely to be good using this method, however - Sam's suggestion of a linked server is likely to be more practical.

(According to BIRT's documentation it does enable joins between datasets, as DMKing suggested - I haven't tried using this feature yet.)

Answer 4

this limitation was removed in SQl Server 2008 R2. there is a work around in previous versions

see my full response here: http://stackoverflow.com/questions/3163393/how-can-i-add-a-field-to-dataset-from-another-dataset-in-ssrs/3163546#3163546

Answer 5

I was going to suggest using SSIS as a possibility until I read this http://blogs.msdn.com/b/jenss/archive/2009/04/23/consuming-ssis-package-data-in-reporting-services-and-using-web-services-in-addition-part-1.aspx

I have used linked servers to combine data from Oracle/SQL Server, not nice but it worked. Failing that I'd go with subreports.

Failing that, point out to management how expensive SAP/Oracle etc are and they'll soon stop moaning. :)