BusinessObjects Board

Missing Data in the report


I have two data providers and did merged on Id and region column.
below Query provides correct results in SQL

Select distinct * from DP_1 A
left outer join DP_2 B on A.ItemID = B.ItemID and A.region = B.region

But when I try in report there few missing records from DP_2

To get left outer join added A.ItemID to the table and it is working

I got #Multivalue error so enabled Avoid duplicate row aggregation too

there few missing records
DP_1 - distinct Campaign, Itemid, region

DP_2 - ItemId, region, reward_year, reward_name

Item can get multiple reward on same year and different year

for each ItemID there multiple rewards in DP_2, for some item it is getting picked for some only one reward is picked.

Any help will be great, breaking my head last 4 hours, thanks

Have a look at this page: Key Intelligence

It should explain it for you.