I have data that shows a bunch of spaces and a bunch of bookings. Think of it like a hotel having rooms and bookings as separate tables.
I’d like to create a pie chart that shows current occupancy–that is to say, count rows that are null on the right side of the join, and those that aren’t. While I’m able to get two measures that give me the correct figures, I can’t put those into a pie chart; it doesn’t take 2 measures.
I thought I might be able to create 2 dimension objects, one that returns 1 when the room is occupied, and one that returns 1 when it’s unoccupied, then sum those independently. That won’t seem to work either, because then there wouldn’t be just one measure.
As I write this, I’m thinking of another possibility in which I have a control statement that returns “Occupied” if the right side is not null and “Unoccupied” if it is, then count those as one measure. I’ll report back if it’s good, for search indexing! If anyone has other suggestions in the meantime, I’d love to hear them.
But man, imagine if you could have TWO pies in a plate!
OK, so, maybe what I want is like a pie that’s made up of part of one flavor and part of another? Does that make sense? No, right?
I realize I need to trick BO into presenting these figures in a pie chart somehow. I’ve been thinking the problem through, and although the idea of having a variable that shows either “Occupied” or “Unoccupied” seemed to be the way to go, it doesn’t aggregate that way I want; it doesn’t count the occurrences of each, it’ll either aggregate down to 1 on each side, or all separate rows for each room.
I’m beginning to think that pie charts don’t work so well with row counts. I can use a single stacked bar, but it looks kinda silly and would just be better represented by a pie.
Aim for a two column table - one with dim the other with measure for your figures. You might need to create a new dim to work across the two types of figures.
Needs to be Greenhalgh’s or Poole’s though - Holland’s are over-rated.
I think I’ve come to the conclusion that this is not possible with the type of query I’m using.
I attempted the 2 column approach, creating a table that had Occupied and Unoccupied fields, then a count as the measure in the second column. Numbers were almost there, and the occupied count looked right, but unoccupied rows were missing. Of course, I need to enable “Show rows with empty dimension values,” so I go ahead and do that and BOOM, counts go down to 1 each. This occurs when counting all rooms on the right query, the one that contains ALL rooms, unfiltered.
If you were to use the eFashion universe, it would be the equivalent of wanting to show a pie chart of all lines that had promotions, versus those that didn’t. When you include the promotions, you would normally lose a bunch of lines from the result set, so to get the equivalent of an outer join, you would make 2 queries. The first would have your promotions and the second would just be all lines. On the report, you would include lines from the second query, and all other details from the first. Then in that block enable “Show rows with empty dimension values.”
In a union query all the fields must be the same, right? This will limit the records and no longer show rooms that don’t currently have a booking, because it’s an inner join in the universe.
Is there some other way to create a union query so you can achieve the same results as an outer join?
Would be a nice enhancement request to be able to provide separate meaure objects (all of the same Unit of Measure, e.g. count) for a pie chart instead of one measure broken down by a dimension. See: ideas.SAP.com
But even better, just drop pie charts all together
It does, as long as there are rows from BOTH sides of a join to bring. In this case, I need to include objects that exist on two different tables, and they’re joined with an inner join.
Put another way, I want to return ALL rooms, but also include objects that show booking details. The way inner joins work means that the resultant list will be limited to show only those rooms that currently have bookings; not what I want. I would like to be able to show empty fields (nulls) next to rooms that don’t have an associated booking at the time the report is run. Then count those that are null and those that are not, and present those in a pie chart.
And anyway, pies have always had more than one dimension! Diameter, depth, crust-ratio…
Some pretty compelling anti-pie arguments there. Since this problem can’t be resolved, I’m going to declare pie charts meaningless and go with something else!
Besides the stacked bar, is there a better way of representing this type of data i.e. occupied vs. unoccupied? If not, I’ll just stick with the good ol’ bar.
Thanks for the enlightenment! I only liked apple pie anyway.
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)