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

Posted on 2011-05-06
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
    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
    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

    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 ...
    LVL 25

    Accepted Solution

    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
    <<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 ( 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.
    LVL 1

    Author Closing Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    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.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now