Sometimes Business Objects can make the simplest thing super difficult—if you’re used to using SQL. In this particular case, I just needed an outer join in which I can count the number of rows that have something on the left of the join, and those that don’t.
We have data about rooms in a hotel, and we have data about bookings. Bookings are associated to a room by the room ID, and they have a begin and end date, so we know who is in what room at any given time. We have a data cube in which all rooms are shown with booking details if there’s a booking, and a blank space if there isn’t. We want to show a count of occupied and unoccupied rooms off to the side with a single dimension and single measure, so we can do some nice charting.
We need a variable that shows whether a room is occupied or vacant. This can be achieved simply enough with a dimension that has the following code.
=If IsNull([Booking ID]) Then "Vacant" Else "Occupied"
If you put this next to your list of rooms and bookings, it should show when a room is occupied or vacant. That’s not very interesting by itself, so let’s start counting how many there are.
You can create multiple measures counting all rooms, occupied rooms, then available rooms by subtracting one from the other, but this leaves you with multiple measures and isn’t suitable for certain charts.
To have a block with the two possible dimensions counted, I started with the following.
=Count([All Rooms].[Room ID])
Create a block that has the room occupancy variable dimension and the count. Make sure it is also set to Show rows with empty dimension values, and what do you get? A count of one for both? Yeah, I had that problem too.
It isn’t enough just to count, you have to count without row aggregation. That means we need to count every row in which “Occupied” or “Vacant” occurs, even if they are the same (which is kind of the point).
The mistake I made was in the counting variable, so let’s fix that.
=Count([All Rooms].[Room ID]; All)
That little “All” as the second argument makes all the difference! Ugh…
You should now see a count of all room states as expected, and can now make fancy charts all you like.
One thing to watch out for is if you are actually getting some duplicate rows for another reason, you’ll be counting those, too.