No Nulls with linked Data providers

building reports desktopintelligence
1

I have two data providers which have the same objects but with different conditions.

Query 1


Name             Designation          Date
========================================================
Sam               Consultant          Jun 5, 2004

Query 2


Name             Designation          Date
========================================================
Sam              Lead Architect     July 5, 2004

All the dimensions are linked.
When I combine the data from two DPs in a single report, I expected the following output


Name             Designation (Query 1)     Designation (Query2)  Date (Q1)    Date(Q2)
======================================================================================
Sam                    Consultant               NULL             June 5 ,2004     NULL
Sam                         NULL        Lead Architect           NULL             July 5, 2004

But what I am getting is


Name             Designation (Query 1)     Designation (Query2)  Date (Q1)    Date(Q2)
======================================================================================
Sam                    Consultant            Consultant            June 5 ,2004      July 5, 2004
Sam                   Lead Architect       Lead Architect          June 5 ,2004      July 5, 2004

I am not able to figure out, when was the designation changed for Sam?
What z wrong here?

mkumar (BOB member since 2002-08-26)

2

A) You might be better off with a Union query

B) When you use multiple data providers try unlinking Date and Designation and make them detail objects of Name (using local report variables)

Andreas :de: (BOB member since 2002-06-20)

3

We have self joins on tables which prompt for the date.
So we have to go for two DPs for two different time frames as the prompt name is same. So I think this is not an option here.

I tried that. If I unlink Designation and make it a detail of Name I get following output.


Name       Designation (Query 1)     Designation (Query2)  Date (Q1)    Date(Q2) 
====================================================================================== 
Sam              Consultant            Lead Architect      June 5 ,2004  July 5, 2004

It tells designation is changed, but I can’t figure out when it was Consultant and when it was Lead Architect.

Sorry I forgot to mention, Date is a report level variable built on prompt value.
Date in Q1 = UserResponse(‘Pick Date’,“Query 1”)
Date in Q2 = UserResponse(‘Pick Date’,“Query 2”)

Can you tell me why I am getting incorrect result?
Thanks!

mkumar (BOB member since 2002-08-26)

4

Another note on this: The users run two DPs separately As that is the only way that can have different values for date prompt in two DPs.

mkumar (BOB member since 2002-08-26)