Getting Gantty

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!


GanttChart:


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.


GanttChart2:


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.


GanttChart3:


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.


Happy Gantting!

Comments:

Hi Manny

Those arrows are going to be tough - Im not sure right now how you would design the logic to allow all the boxes to fit in and then have the arrow stay connected to the specific point in time it needs.

I took a look at the site samples, http://www.timelinemaker.com/index.php?p=products_pro_charts - the types without the arrows ie more gantt like are going to be doable with the MS Shape support we have. The others are going to be tough if not impossible.

Tim 

Posted by Tim Dexter on February 01, 2008 at 07:49 AM MST #

Hi Tim, wondering if you could give me some pointers. I am a newbie at obip and have been asked to create a timeline chart similar to the one on this webpage: http://www.timelinemaker.com/product-samplecharts-insuranceinvestigation.html Having read this blog (gantt), i would assume that a timeline chart is possible. Am I correct? And, have you seen charts like this rendered with bip? thanks Manny

Posted by Manny on February 01, 2008 at 07:49 AM MST #

Tim, thanks for your response! Ill give it a go and fallback on the Gantt representation if I hit a dead end. Manny

Posted by Manny on February 04, 2008 at 12:43 AM MST #

Tim, Did you ever do an example organization chart? Can't see anything from searching the blog. I need to construct one of these for the position hierarchy and would appreciate some pointers to get me started. Many thanks, Dave

Posted by David Barnes on October 19, 2008 at 03:24 AM MDT #

I tried using your RTF and XML data but PDF and EXCEL did not show shapes. What might be the reason.

Posted by Vib on February 06, 2009 at 05:05 AM MST #

Hi Tim, I have the same question which Dave asked. Do you have any example with Organization Chart? Thanks, Amit

Posted by AmitD on February 23, 2009 at 03:13 PM MST #

Hi Tim, I'd also like some pointers for building an org chart. The BI Pub documentation makes it sound possible: "Use the organization chart functionality in your templates and the chart will be rendered in the output." but other than that... nadda. Any help would be very appreciated. Thanks, Mariko

Posted by Mariko on July 29, 2009 at 12:11 AM MDT #

Tim, It seems with the weekly schedule, meetings with the same time and duration will overlap. Do you have any pointers on determining if a shape already occupies the space already? Thanks!

Posted by Dustin on May 04, 2010 at 02:22 AM MDT #

Hi Dustin There is no way for the shape to be aware. The only way to handle what you need to do is to check the osition of the shape prior to inserting it. If its going to overlap then either change color so you can see the other shape underneath. Or move the shape down so it does not overlap. Its not going to be simple but its doable. Regards Tim

Posted by Tim Dexter on May 04, 2010 at 02:36 AM MDT #

i would like to know, how can i display the output horizontally instead of vertically. The output will be displayed based on line items below : Parent item –>child line item 1 –>child line item 2 –>child line item 3 So in the rtf/report it will display such as – Code : child line item 1 child line item 2 child line item 3 But i need to do in this format – Code :child line item 1,child line item 2,child line item 3 Please advice and email me..Thank you

Posted by eja on June 06, 2010 at 01:21 PM MDT #

Does anyone figure out to get the url for Gantt chart in People code for BIP?

Posted by bjyu on October 04, 2010 at 10:53 AM MDT #

Hi, Does anybody created a template for building org chart for position hierarchy report? Please do let me know, Dev

Posted by devendar on October 11, 2010 at 06:11 AM MDT #

Hi,

screenshots/images in this tutorial are not visible to me. Still this post exist ?

-- Shanmukh

Posted by shanmukh on February 06, 2012 at 01:32 AM MST #

Hi Tim,

I have a similar business requirement as your blog post, and I appreciate you going into as much detail as you did. However, as others have commented, the images from this blog are missing and I'd like to use them to compare against my results. If they still exist, can you send them to me, or edit this blog so they are visible again? Thxs!

Posted by Randy on September 06, 2012 at 10:33 AM MDT #

Hi Tim. Excellent post and the images render for me. Question! is it possible to change the background color at runtime based on a value in the XML? Thanks Simon

Posted by Simon on January 29, 2013 at 08:37 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today