Ben Murden

Business Objects: Room Occupancy

by on Mar.06, 2012, under Development

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.

:

2 Comments for this entry

  • quibids bid tracker

    I am grateful to have discovered this site. Hold up the excellent postings.

  • James Halligan

    You could create a second query that lists all your rooms; then merge the room ID dimension and simulate your outer join

    Other choices:

    create outer join in universe
    modify SQL on the report to change join to outer

Leave a Reply

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!