By Tim Dexter-Oracle on Jan 15, 2008
I spent some of today trying to solve a none too common (that I have seen) problem, that is to add a gantt chart to your BIP or BIEE outputs. BIBeans, the charting package we use does not support them - at least not in a way we can use them, they have a solution for EBS OAF pages but not for a report.
For the uninitiated a gantt chart ( I dont know why but my brain wants to call it a 'gnat chart'- no disrespect Mr Gantt) shows a timeline for say a project and all of its components, when will one sub project end so another can start - the best known and the bane of my life a few years back is/was MSProject - we had a monster of a project plan that stretched into the distance and over the horizon at times, were the developers even going to be here in 12 months time? This was back in the dot com boom - 'interesting times'as they say in Ankh Morpork!
The example above it pretty simple but you get the idea. I sat and thought for a while and came up with a couple of options.
1. Get 'noodling' (non-technical term for hacking) with other bibeans charts and try and combine a few together to get the desired result.
2. Have a bash with the MSWord shapes to create a gantt output.
Option 1 was going to be lots of work as the BIBeans XML chart defintion is not well documented - I dont want to wade through that DTD again. I have multiple chart entries on this blog to try and clear the mist but my glasses are still fogged up. There is some good news on the horizon on that front - the new version of the template builder has taken a huge stride forward when it comes to inserting charts. It even supports inserting the bubble charts I wrote about a while back.
Option 2 was much more palatable for me and ought to be some fun. I have not touched the shape support for a while and I have been looking for a good business requirement for them ever since we released them other than the gauge I built last year. Should just take a bit of calculation to get the shapes to behave and we would be good to go.
For those of you that dont know, you can create drawings in Word, using predefined or custom shapes, you can combine them together to form more complex objects, such as the gauge. Now Publisher, gives you the ability to then manipulate the shapes or sub shapes - you can duplicate, add text, skew, move, even write text along a line. If you take another look see at that gantt chart up there and break it down, its nothing more than a rectangle thats moved, stretched and duplicated down and across the page.
So we dont get bogged down in what the data is I thought I come up with a calendar report of someones weekly meeting schedule - its a nice straightforward example.
Thats the output from Publisher - not bad, needs a little tidying but for a first effort and as a mean to convey how to do it, its great.
How did I do it? - well all of our reports need some data so I knocked up some XML.
<MEETINGS> <DAY> <NAME>Monday</NAME> <MEETING> <START>9</START> <DURATION>1</DURATION> <DESCRIPTION>Team Meeting</DESCRIPTION> </MEETING> <MEETING> <START>11</START> <DURATION>2</DURATION> <DESCRIPTION>Discuss BIP</DESCRIPTION> </MEETING> <MEETING> <START>14</START> <DURATION>1</DURATION> <DESCRIPTION>Customer Call</DESCRIPTION> </MEETING> </DAY> ...
Of course I built it simple but there are 3 pieces of information I need about the meeting, the day, the time and the duration - I added the description cos I was getting 'fancy' - a non technical term for 'showing off'
The template could not be simpler in its layout.
So my time slots were fixed, we could have made them dynamic but I want to focus on the shape manipulation. We have a simple for-each loop in the first cell to get the days of the week in rows down the page. Then there is that yellow box - if you check our docs you'll see that to manipulate the shape we embed some commands into the Web tab of the object's properties. In this case I had:
<?for-each@shape:.//MEETING?> <?shape-offset-x:((number(START)-8) * 65)?> <?shape-size-x:DURATION?> <?end for-each?>
The for-each@shape tells our parser that we want to generate shape instances for this loop.
The shape-offset-x - defines how much to offset each shape in the loop. The actual offset value '((number(START)-8) * 65)' is not that tough to work out. Our starting hour of the morning is 8am and the shape is sitting on the border of the 8am cell so we need the shape to be offset from that point - remember we know when the meeting is so our offset is going to be 'start time - 8'. Need to be careful here to use the 24 hour clock a meeting at 1pm using the 12 hour clock yields some interesting results. Finally, the 65 is the point width of the columns in the table row. To get this value, just flip Word to measure in Points rather than Inches or CMs - Tools > Options > General. Then get into the table properties and into the Column tab.
The shape-size-x:DURATION defines how big the rectangle should be - we have the duration of the meeting. So a 2 hour meeting will result in a block twice the size of the original to fill the 2 hour slot.
And thats it to get the blocks repeating across and down the page.
To get 'fancy' and add the description we need to build up a composite shape. You could just use the Add Text command on the shape but you get some funky font stretching when the shape has to be enlarged. So go with the composite - you need the basic rectangle and a text box.
In the rectangle you need -
<?shape-size-x:DURATION?>- to get the right size
In the text box you need -
<?shape-text:DESCRIPTION?>- to get the description
Now Group them together by selecting both, right clicking > Grouping > Group. Then add the following to the composite:
<?for-each@shape:.//MEETING?> <?shape-offset-x:((number(START)-8) * 65)?> <?end for-each?>
This gets the whole thing to repeat across the page as needed, et voila!
I should point out that when you see the template you'll see that the shape is not exactly at the 8am cell edge - its slightly offset. Its a feature, we want you to play with your creations until they are perfect. Seriously, Im not sure why it needs it, something funky in Word maybe - you just need to nudge it around to get it perfect. You can get the data, template and output here.
From here, Im moving on to a more complex output with dynamic rows and columns and then onto the fabled org chart which, with a little jiggery pokery I think I can tease out of our shape engine.