My users have several reports they want to create which involve data which doesn’t exist in the system. For example a report showing products purchased in a given year. HOWEVER they want to see Years where no products were purchased and they want these rows to have counts of zero.
To do this I’ve created a YEARS table which just contains a list of years. I was wanting to cross join this to my products table.
However I don’t see how to do this in Business Objects Universe designer.
Getting somewhere with this (or so I thought). I just left the objects totally unconnected and when I use them in a report on my test universe the cross join works!
However to make matters worse, I have TWO universes setup (LIVE and TEST).
In TEST universe my Crossjoin works fine. If I select the Year and Product it does ONE query and does a cross join.
SQL(Select Product, Year FROM Products, Years))
In LIVE universe my crossjoin doesn’t happen. I get two queries and it won’t let me put the objects into the same report.
SQL( QUERY 1: Select Product from Products) (QUERY 2: Select Year from Years)
Does anyone know what could be causing this? The universes are setup nearly identically and the joins on the objects concerned are the same (Products is joined to many other things, years is not joined to anything).
In your live universe, run a query that gets products and years from the products table. Run another query that gets just the years from the years table. In the report, take out the years object from query 1 and replace it with the years object from query 2 and you should be good to go…
I’ve figured it out by myself. To Do cross joins you simply don’t connect the tables at all! You just leave them unrelated.
The reason my queries were happening differently in my TEST universe is because it didn’t have any contexts set. (The LIVE universe has two contexts set).
SOLUTION:
If you want a cross join in a universe which has contexts then do a SELF JOIN on the table you want cross joined and then add it into the relevant contexts!
Simple.
Also I discovered that I can remove most of my contexts and replace them with one large manually created context. My universe is looking clean and efficient now.
A->B->C
D->B->C
By default it creates two contexts for this, but I’ve replaced it with one context and just added all the joins in into it. Seems to work perfectly.
(BOB member since 2012-10-22)
(BOB member since 2005-03-01)