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.

Scenario

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.

Setup

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.

Solution

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.

Caution

One thing to watch out for is if you are actually getting some duplicate rows for another reason, you’ll be counting those, too.

Leave a Reply

Welcome to this place

This is the home on the web of Ben Murden. Without it, he'd be a homeless web-hobo, which is both ridiculous and undesirable.

Who’s this guy?

Web developer and avid gamer. If I'm not writing about one, I'll be writing about the other, except on the occasion I'll be writing about something else, in which case you can expect just about anything!