Skip to Content
Jan 10, 2020 at 05:13 PM

Left Join across three queries


Hi all,

I'm working on WEBI Web Intelligence (SAP BusinessObjects BI Platform 4.2 Support Pack 6 Patch 6 Version: that is connected to a single database universe which contains very complex data about items we stock and sell. I only have access to report designs--not the universe design.

The data flow is such that when an item is manufactured it has a Built record created. Then some time later it arrives at our warehouse and has a Stock record created. Then when it is sold it has a Sold record created.

Unfortunately, because of the way the database/universe is designed, if you try to retrieve any of the Sold data for an item that hasn't been sold, it will not retrieve ANY data for that item (even Built or Stock data) in that query as if that item does not exist. So we must separately query Built, Stock, and Sold data.

Therefore, I am trying to create a single report that Left Joins across three separate queries:-

qLookup = Excel table containing only one column of [ItemID] (each individual item that we stock has a unique serial number)
qStock = Universe query that returns [ItemID], [StockDate] and is filtered to return all records that are currently in stock
qSales = Universe query that returns [ItemID], [SoldDate] and is filtered to return all records that have been sold

I want a report that will list ALL the [ItemID] from qLookup (regardless if they exist in qStock or qSales) and then detail [StockDate] (Null if no Stock record) and [SoldDate] (Null if no Sales record) for that [ItemID].

In SQL I'd take qLookup and left join the other two queries:-

SELECT qLookup.ItemID, qStock.StockDate, qSales.SoldDate FROM (qLookup LEFT JOIN qStock ON qLookup.ItemID = qStock.ItemID) LEFT JOIN qSales ON qLookup.ItemID = qSales.ItemID

However, in WEBI I can't seem to do this by merging the ItemID dimension across qLookup.ItemID, qStock.ItemID, and qSales.ItemID. As soon as either qStock or qSales returns no records for an [ItemID] the whole row is removed from the report as if it never even existed in qLookup.

Does anyone know if this is possible in WEBI?