Is it possible to compare 2 data sources in Reporting Services?

Question

Post reply

Is it possible to compare 2 data sources in Reporting Services?

AlanLSmith

SSC Enthusiast

Points: 192
More actions
March 17, 2009 at 8:11 am

#200985

I have two inventory systems that I need to compare and display any discrepancies.
For example:
System Item Balance
A 1 100
A 2 20
B 1 100
B 2 21
I need a report that returns "Item 2, A=20, B=21"
Can I do this in Reporting Services? Or do I need to use something like SSIS?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I don't believe you can do this in SSRS. I know I don't know how to do it.

Are the inventory systems on the same server?

Are the inventory systems both in SQL Server?

Have you considered using a linked server and then using T-SQL to do the comparison?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

AlanLSmith SSC Enthusiast Points: 192 More actions · Answer 2

Are the inventory systems on the same server?

No - different servers, different environments

system A is JDE on an AS400

system B is oracle

Are the inventory systems both in SQL Server?

Neither

Have you considered using a linked server and then using T-SQL to do the comparison?

No - haven't used either of these before

I have been using SSRS for some basic reporting because it has a simple interface to our internal web. I can use SSRS to connect and report on either of these systems individually. But now I need to do some comparison between the 2 systems. I can do this in MS Access with a few queries to look for the differences, but I'd like a solution like SSRS that makes the report easy to access from the internal web for the end users.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I'm not saying that this is the best solution, but couldn't you do the comparisons in Access and use the Access database and final query as the datasource for the SSRS report?

Another option might be to use an ADO.NET dataset as the source. I have never done this, but here are some articles that may help:

http://msdn.microsoft.com/en-us/library/aa902651.aspx

http://prologika.com/CS/blogs/blog/archive/2005/11/20/695.aspx

http://appdevchronicles.blogspot.com/2007/09/sql-server-reporting-services-2005-xml.html

Apparently you can also use an SSIS package as a datasource, but that has to be enabled in SSRS. Here are some links I found:

http://dineshpriyankara.spaces.live.com/Blog/cns!40FF1FAA28D7B217!771.entry?sa=187038810

http://blogs.msdn.com/bimusings/archive/2006/07/19/671743.aspx I think this is the most useful.

http://msdn.microsoft.com/en-us/library/ms345250(SQL.90).aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Joe Pattison SSC-Addicted Points: 431 More actions · Answer 4

This can be done - at least we did it but we are using SQL servers. We did it through an SSIS package pulling data from three different server clusters. From the new derived table, similar to the Access solution mentioned, we run the report from SSRS. Good luck.