How to link correctly 2 Data Providers in B.O. Reporter

This is the content of the FAILURE table:


OMC_NAME   DATE         LOG_RECORD_ID
========   ==========   =============
REGION_A   01/01/1980   1111111
REGION_A   02/01/1980   1111112
REGION_B   01/01/1980   2222222
REGION_C   02/01/1980   2222223

This is the content of the SUCCESS table:


OMC        DAY          LOG_RECORD
========   ==========   =============
REGION_A   02/01/1980   3959339
REGION_B   01/01/1980   2910315
REGION_C   03/01/1980   2223333

The LOVs for input-filters refer to distinct values of OMC_NAME and DATE in FAILURE table, so:
a) For OMC_NAME / OMC, you can select between REGION_A, REGION_B and REGION_C;
b) For DATE / DAY, you can select between 01/01/1980 and 02/01/1980 (because 03/01/1980 is not present in FAILURE table).

If I select 01/01/1980 and 02/01/1980 (multi-selection) in the input filter, selecting all OMC_NAMEs, I would like to yield the following report:


OMC_NAME   DATE         FAILURES(*)   SUCCESSES(**)
========   ==========   ===========   =============
REGION_A   01/01/1980   1             0
REGION_A   02/01/1980   1             1
REGION_B   01/01/1980   1             1
REGION_C   02/01/1980   1             0

(*) Count(LOG_RECORD_ID) in FAILURE table for OMC_NAME = REGION_A and DATE = 01/01/1980;
(**) Count(LOG_RECORD) in SUCCESS table for OMC_NAME = REGION_A and DATE = 01/01/1980.

I tried in BusinessObjects reporter with two queries and using the sincronization (LINK TO…), but because of a
master/detail section on OMC_NAME, if FAILURE table is empty (or doesn’t contain a certain table reperibile in SUCCESS
table) I obtain that OMC_NAME cell is empty in that section.
On the contrary, if I select OMC as master, I receive empty OMC cell if SUCCESS table is empty (or doesn’t contain a
certain date reperible in FAILURE table).

Any clues??


Donald Duck :mexico: (BOB member since 2004-07-29)

Not sure if I totally follow the question, but, I think that a UNION is what you are after in this case. If you add a literal to each individual SQL, to identify which records are from the success table and which are from the failure table, you should be able to count the records in both, grouping by the region and the date.

Hope this helps.


barbaran (BOB member since 2003-07-30)