how to make a comparison query in report server 2003 using 2 seperate datasources?

Posted on 2011-05-06
Medium Priority
Last Modified: 2012-05-11
I want to compare the same 2 tables in 2 different databases - 1 access DB and 1 SQL DB

How  do I create a report using report server 2008 /.NET studio 2008 with 2 different DB backends?

Question by:HudsonMarine
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35711132
I think your problem is discussed here:
It is about 2005, but the same applies to 2008. This doesn't work for Express because Express can only connect to the local sql server. I think linked servers is also a good option. That way you can query both sources in the same query.
I never tried it, but it might also be possible to create an Access database that links to both the SQL Server and the Access data, and than base your reports on that database.
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35711531
I tested it and you can add two datasources to your report. Than create two datasets each of them using another datasource. And finally you can ad tow tablix/table/matrix controls on your reports each of them using the other dataset.
But this results in two isolated tables. If you have two tables that should contain the same data and you want to show the differences in the report this is probably not the best way to do it. You could create custom code in your report that does some of the comparison. But if that is what you need I think it is much easier to create a linked server in SQL Server and create a query that does the comparison.
Maybe you can tell a bit more about the structure of your table and what exactly you mean by 'compare'.
LVL 13

Expert Comment

ID: 35714574
Mostly what you would need to do is figure out how you want to compare... Getting two datasets in 1 report is easy. Getting two in one table is impossible. What you might want to look at is importing the access data in to the SQL db...

Also if its just a couple of values and not a table with multiple rows you could use hidden fields and fields!fieldname.value as your references ...
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 25

Accepted Solution

TempDBA earned 2000 total points
ID: 35718194
You can create two datasets that fetch data from both the sources and then link the datasets with differnt table to show the data. But for comparison its better you fetch the data from access db to sql server using link server call or importing data via SSIS package. Then create a procedure that gives the output as the data you want and then create a dataset with the data fetched by the stored procedure. This will be a easier solution.
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35718199
<<Getting two in one table is impossible.>>
I think it might be possible using custom code or subreports in SSRS 2005 and 2008. But in 2008 R2 is is actually quite easy. There is a new lookup function (http://msdn.microsoft.com/en-us/library/ee210531.aspx) that you can use to retrieve a value from another dataset. I used this in this example.
compareThe dataset SQLServer retrieves sales data from SQL Server. The dataset Access retrieves complaint data from a MS Access database. The third table in the report uses the SQL Server dataset but has this expression:
=Lookup(Fields!Product.Value,Fields!Product.Value,Fields!Complaints.Value,"Access") / Fields!Sales.Value * 1000

Open in new window

The Lookup function retrieves the number of complaints for the product from the Access dataset and divides it by the Sales, giving the complaint rate for the various products.

Author Closing Comment

ID: 35748564
This was the solution i used - and it worked but i had some problems with data type mismatch issues.

I am working on these now.

I appreciate everones response!

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question