This view contains all the reservations data that have occurred over time, with start and end datetimes. It’s the most important table for reporting on reservation data.
For reporting purposes it should be joined with other views for more info:
- reservables_v1 (contains the list of reservable objects)
- locations_v1 (contains the list of locations linked to reservable objects, if any)
Data structure
Column | Meaning | Comment |
|---|---|---|
reservation_reference | reservation identifier for the user (booking number) | for the end user |
reservation_unique_reference | globally unique id of reservation | for integrations and automations |
reservation_title | reservation title | |
reservation_remarks | reservation remarks | |
reservation_result | result of the reservation process: RESERVED / CANCELLED / NO_SHOW | |
reservation_participants_total | total nr of participants | |
reservation_start_datetime | start date+time in local time zone | cannot be NULL |
reservation_end_datetime | end date+time in local time zone | cannot be NULL |
reservation_creation_datetime | date+time of creation in local time zone | cannot be NULL |
reservation_cancel_datetime | date+time of cancellation in local time zone | is NULL except when cancelled |
reservation_time_zone | time zone name | |
reservation_creator_organization_name | name of organization of the person who created a reservation | |
reservation_organizer_organization_name | name of organization of the persons for who the reservation is for (accountable) | |
reservation_building_name | name of building | |
reservation_building_reference | reference of building | |
reservation_floor_name | name of floor | |
reservation_floor_reference | reference of floor | |
item_number | sequential number of item in a reservation | items are numbered 01, 02, 03 etc |
item_name | name of reserved item | copy of reservable object name at the time of reservation |
item_description | description of reserved item | copy of reservable object description at the time of reservation |
item_unique_reference | unique reference of reserved item | |
item_type | type of reserved item | Enum: |
item_category_name | category name of reserved item | |
item_category_reference | category reference of reserved item | |
item_quantity | quantity of reserved item | |
item_capacity | capacity (nr of seats) of the reserved item | |
reservation_item_id | unique identifier of a reservation item | primary key in this view |
reservation_id | unique identifier of a reservation | |
reservable_id | unique identifier of a reservable object | foreign key to reservables_v1 |
building_id | unique identifier of a building | foreign key to locations_v1 |
floor_id | unique identifier of a floor | foreign key to locations_v1 |
dwh_modified_timestamp | timestamp (in UTC timezone) when this row was modified in the DWH) |
Concepts
In Workplace, these object relations are important:
- each Location can contain 0, 1 or more reservable objects
this makes sense for meeting rooms and reservable desks
- however, not all locations are reservable
- each reservable object can be reserved
this lead to a reservation item
- each reservation may contain 1 or more reservation items
For example, someone can book a meeting room with extra catering services. In such case, the reservation will appear in the view as multiple rows, one per reserved item.
Examples
Desk reservation for 1 person
Location | Reference | Item Nr | Reserved Item |
|---|---|---|---|
Building ABC | RES-0000001234 | 01 | Desk 1234-0015 |
Group desk reservation for 4 persons
Location | Reference | Item Nr | Reserved Item |
|---|---|---|---|
Building ABC | RES-0000001234 | 01 | Desk ABC-1.015 |
Building ABC | RES_0000001234 | 02 | Desk ABC-1.016 |
Building ABC | RES_0000001234 | 03 | Desk ABC-1.022 |
Building ABC | RES_0000001234 | 04 | Desk ABC-1.023 |
Meeting room reservation for 6 persons
Location | Reference | Item Nr | Reserved Item |
|---|---|---|---|
Building ABC | RES-0000001234 | 01 | Room “Barcelona” (6p) |
Meeting room reservation for 6 persons with catering
Location | Reference | Item Nr | Reserved Item |
|---|---|---|---|
Building ABC | RES-0000001234 | 01 | Room “Barcelona” (6p) |
Building ABC | RES-0000001234 | 02 | Morning coffee (4p) |
Building ABC | RES-0000001234 | 03 | Sandwich Lunch (6p) |
These example illustrate that reservations are always master-detail objects:
- master = the reservation number ('reference'), title, start and end date, etc
- detail = each reservation item added to the reservation (room, equipment, services, etc)
Reservation result (reporting on cancellations and no-shows)
One of the important metrics when analyzing reservations, is understanding how end users deal with their reservations afterwards. In case where a planned meeting doesn’t take place, will they be so polite to cancel a planned meeting up-front, or will they simply leave things as they are, leading to a so-called “no-show”?
The “reservation_result” column reflects the use cases illustrated:
In the beginning, when a user books a meeting room in the near future, column reservation_result contains value ‘RESERVED’:
As time goes by, the reservation will not change automatically. When nobody is acting and when there are no automations in place for detecting no-shows, this leads to:
Nothing has changed to the data set, except that the reservation is now in the past. Column reservation_result still contains value ‘RESERVED’.
When a planned meeting cannot take place, most users are notified ahead of time. Best practice is that the meeting organizer cancels the reserved objects before the meeting starts:
Cancelling a reservation before its start time is considered GOOD behaviour, because the reservable object then becomes available again for other people.
When this happens, column reservation_result will obtain value ‘CANCELLED’.
Sometimes a planned meeting gets cancelled after the meeting starttime. This may happen by manual action (someone doing a manual cancellation via a Workplace touchpoint) or via an automation rule (for example, a rule that will automatically cancel the reservation when no presence/headcount is detected by sensors in the room).
When this happens, column reservation_result will obtain value ‘NO-SHOW’.
Summarized behaviour for column ‘reservation_result’
When reporting on future reservations, values will always be ‘RESERVED’.
When reporting on past reservations:
- value = ‘RESERVED’ when no cancellation took place (reservation_cancel_datetime is NULL)
- value = ‘CANCELLED’ when a cancellation took place and reservation_cancel_datetime is BEFORE the reservation start_timestamp
- value = ‘NO_SHOW’ when a cancellation took place and reservation_cancel_timedate is AFTER the reservation start_timestamp
Single or multi-day reservations
In Workplace it is possible to book a reservable object for multiple days.
This is not recommended as best practice because it means that the reserved object appear sas booked for multiple days during 24x7 hours.
It may be relevant in specific cases like equipment or vehicles that are reserved during multiple days.
However, for meeting rooms and desks, we don’t recommend this approach because it means that if someone books a desk or room from Monday to Friday, throughout the week, that means 24 hours per day around the clock. In reality, these resources are not occupied during the night. Such behaviour falsifies the normal daily reporting on reservations.
Nevertheless it is a configuration possibility to use multi-day reservations in Workplace, leading to reservations which span over multiple calendar days (i.e. reservation_start_datetime and reservation_end_datetime columns showing different date parts).