comparing 2 datasets in SSRS

Question

Hi

I have dataset 1 with Fromdate and ToDate.

I have dataset 2 with effectivedate.

Both datqasets are from 2 different sources.

In my report i have to show 1 row per Fromdate from dataset1 and side by i have to show count of number of rows from dateset 2 where effective date is between from date and todate. Look up functions works for only = operation and not < or > operations. How shall i achieve this?

Thanks
Ravi. S

---

Ravi.S Chennai, Tamil Nadu, India

Answer 1

Your best approach when you need to perform lookups, or any kind of join between two datasets in SSRS is to try to combine those datasets at the query/dataset level. 

The lookup functions in SSRS, as you mention, are limited compared to what can be achieved at the database/dataset level. And, they perform far worse.

You should be looking at presenting one single dataset to SSRS with lookups and joins already performed at the database level. 

---

Thanks! Josh Ash

Answer 2

Hi josh

Agreed that we have to try to bring it in single data set. But in my case 1 source is oracle and other source is SQL Server.

So i am not able to bring it as a single data set. Finally i decided to pull the oracle data to SQL and then borght my result set as Single data set now. But still wondering if i will be able to do it without bringing Oracle data to SQL Server?

---

Ravi.S Chennai, Tamil Nadu, India

Answer 3

You can, using a linked server

http://stackoverflow.com/questions/307636/how-do-you-setup-a-linked-server-to-an-oracle-database-on-sql-2000-2005

you can then do your merging in a single query, with no ETL at all. 

The pros and cons of 'staging' data are a big topic, and verge on a discussion of data warehousing.

But you can, if you want to,  merge data from all different types of sources in a single T-SQL SELECT statement with linked servers and OPENROWSET.

You'll notice there is some setup required to get the Oracle drivers working. 

---

Thanks! Josh Ash