Since a long time I’ve had this itchy urge to do something more graphical in Cognos. Tables and crosstabs is all nice, but it seems so old fashioned. Today, if you want to get your message across to colleagues and customers there are much better ways to do this, things like infographics. The problem is that there is a large gap between repeatable and consistent reporting in Cognos BI and one-off fancy creations like infographics.
So I decided to give it a go and see how easy it would be to get a seating plan into Report Studio. You should be able to select a flight and see a dynamically generated seating plan. And each seat should indicate if it is booked for that flight or not and each seat should be clickable to show passenger info. Sounds like something easy, no?
The weapon that I’ve chosen for this attempt is a workhorse Boeing 737-800 from KLM. Here you can see the non-dynamic seating plan on the website of KLM.
So how to start with this?
My first thought was to make use of IBM Cognos Map Manager. The reasoning being that a seating plan is nothing more than a map layout of an airplane where the seats are geographically referenced rectangle regions. This way you just have a single map for each type of plane with different layers for different seating arrangement.
So I started to investigate on how to create my own “map”. Turned out this is not an easy task. Apparently the only thing you can import into IBM Cognos Map Manager are *.gis files accompanied with a number of files from a tool called MapInfo. MapInfo is a commercial mapping and geographical analysis tool. MapInfo saves its work in a number of proprietary files and you can generate a descriptive *.GIS file to accompany those files. Cognos Map Manager then picks up the *.GIS file and the other files and turns it into a *.CMF file, which again is proprietary (sigh).
But after digging a bit deeper in the geospatial analytics world I found an open source tool GRASS GIS, that allows you to do the same and much more. And it turns out that GRASS GIS allows you to save your work in the MapInfo format and writing a *.GIS file yourself is not that hard.
Hours and hours later this was my conclusion about turning a plane into a map using GRASS GIS:
- GRASS GIS looks complicated if you have never worked with a geographic style tool, but it is not impossible.
- You can do everything you need to do and much more.
- You can create your own coordinate system, load pictures, georeference pictures, create regions and everything, but it is obvious that these tools are intended for using earthly geographical data, not seating plans.
- It takes time ..
So all was going relatively well .. untill.
Untill the point I had my MapInfo export and written a *.gis file and imported it into IBM Cognos Map Manager. Turns out that this thing strips almost everything from my original seating plan only keeping lines, regions and points. So I ended up with this (one of my tests where everything is a region):
I mean this is 2013 right (pinch arm), not the 70ties? So this looked like crap, my carefully georeferenced image was gone, only my regions where still included, lines gone, coloured layers gone. Also turns out you cannot dynamically adapt coordinates. Once it’s in, it’s in. All that work and I ended up with something that could have come out of a 1980 matrix printer.
Ok so the basic result is that I now know a lot more about GIS and related software.
Plan B “Serious Crosstab Abuse“
My second attempt is not elegant in any way, but it is simple.
In Report Studio I created a crosstab, seat column numbers in the columns and seat row numbers in the rows of the crosstab. The measure inside the crosstab is just a unique number that I assigned to each seat and based on the row-column combination. So it has no meaning and is definitely not a measure, but by making it a measure it works in the crosstab.
So, what you see in the crosstab is basically the seating plan of the Boeing from KLM. You have the seat rows A to F (CC is a dummy for the alley) and seat columns 1 to 30.
Second step is starting to cut up the initial picture of the seating plan of the Boeing 737-800. Basically I made separate pictures of the nose, the tail, the wings and each individual seat type. Then I made some small color coded squares for each seat status (reserved = red, free = green and grey = blocked).
Finally I created a bunch of nested tables in combination with the crosstab and fiddled around with the crosstab properties, so that everything except the crosstab cells became invisible. Then I pasted the cut up pictures across and seat pictures as background of the crosstab cells and inserted a separate picture for the seat color coding on top. The picture URL is then set as a report expression based on the seat status in the data source. So this is how the actual report looks in Report Studio:
And this is how the final result looks:
So each seat gets a colored rectangle on top of each seat indicating the seat status. You can select different flights and click the seats for more information.
Maps are not the way to go, it could be, but Map Manager is not up for the job. And yes the old ways still work best combined with some trickery. But I’m not satisfied because this means that next time with some completely different graphic request, I again have to figure something out to make it work. What with a flying saucer seating plan?