Gantt Chart in Webi

We’ve seen many developers/users asking how to create a Gantt Chart in Webi, here is a workaround, we can do it by overlapping a Bar Chart and a Horizontal Table (chart in the front & table in the background), it has many limitations though.

End product looks like this.

Disclaimer:

As it’s an overlap of Bar Chart and Horizontal table, I think it’s going to be really difficult to add complex interactivity to it. If you can build up on this and add some interactivity, great, if not, I’d suggest you all to have this as a standard non-interactive chart in the report.

Credit to Ayman Salem, his response (specifically formula for variable In) to this question, gave me the idea for variable Start at Nth day of Year, without which, I was struggling on this workaround.

Software Env: BO 4.1 SP6 Patch 8

Below is the sample data set used (related to Project Time Line), with excel as source.

Project Stage Start Date End Date Project A Design 1/4/2020 1/15/2020 Project A Development 1/16/2020 2/29/2020 Project A Testing 3/1/2020 3/15/2020 Project A Rollout 3/16/2020 3/18/2020 Project A Maintenance 3/19/2020 6/25/2020 Project B Design 1/5/2020 1/20/2020 Project B Development 1/21/2020 5/15/2020 Project B Testing 5/16/2020 6/16/2020 Project B Rollout 6/16/2020 6/22/2020 Project B Maintenance 6/23/2020 12/20/2020

Step 1: Create Time Dimensions for Start Date

If your BO version supports Time Dimension hierarchy, then right click on Start Date and click on Create Time Dimension, and select & update as below to get additional dimensions related to Year and Month as shown below.

These are the dimensions you should see for Start Date

Step 2: Create the required variables

Create the below variables as dimensions

• Leap Year Flag =If(Mod(ToNumber([Start Date Time Dimension].[Year of Start Date]);4)=0 Or Mod(ToNumber([Start Date Time Dimension].[Year of Start Date]);400)=0 Or Mod(ToNumber([Start Date Time Dimension].[Year of Start Date]);100)<>0) Then “Yes” Else “No”
• Max Duration in Chart =If([Leap Year Flag]=”Yes”) Then 366 Else 365
• Month Column Width =Fill(” “;[No of Days in Month]/1.5)

Create the below variables as measures

• Duration in Days =DaysBetween([Start Date];[End Date])
• Start at Nth day of Year =DayNumberOfYear([Start Date])
• No of Days in Month =If([Start Date Time Dimension].[Month Number] InList (1;3;5;7;8;10;12)) Then 31
  ElseIf([Start Date Time Dimension].[Month Number] InList (4;6;9;11)) Then 30
  ElseIf([Start Date Time Dimension].[Month Number]=2 And [Leap Year Flag]=”Yes”) Then 29
  ElseIf([Start Date Time Dimension].[Month Number]=2 And [Leap Year Flag]=”No”) Then 28

Step 3: Create the Stacked Bar Chart

Create a Stacked Bar Chart and Assign Data as below

Initial view of Chart is shown below

Step 4: Formatting the chart

Increase the width of Chart, remove/hide the Title, and bring the Legend to the bottom, updated chart is shown below

Let’s sort the Project and Stage values so they make sense. Right click on Chart -> click on  Sort -> Advanced -> click on Add -> add Project and Stage (in Sort 1) and sort the values manually as below, click Apply and OK.

As we see in the chart in Step 3 that the max value on Y-axis is 380 days, let’s update to make it 366 or 365 depending on Leap or Non Leap Year. Right click on chart, click Format Chart -> go to Value Axis -> Scaling -> in Maximum Value, choose Fixed value and enter the formula in Value to use variable =[Max Duration in Chart]

Updated chart is shown below, Project and Stage are sorted to make sense, and max duration/days shows 366 to display that we’re showing dates 1/1/2020 to 12/31/2020 on the chart.

Now, we want to Format Data Series for the values we see in the Legend that has “Start at Nth day of Year – ” prefix, as that’s when a specific Stage in a Project starts, and they should be displayed in white color.

Right click on the value Start at Nth day of Year – Maintenance in the legend and select Format Data Series, click on Custom and make it Transparent by selecting 0 beside the color, as shown below, and click OK.

Repeat this for the other 4 values in legend, updated chart is shown below

Let’s add the duration of each stage in the project, meaning how many days it took for Design, Development, Testing, Rollout and how long is the Maintenance going to last. Right click on the Chart, select Format Chart -> Global -> Data Values -> check Data Label Displaying Mode -> select Horizontal in Orientation and click OK.

Let’s hide/remove the Legend, and if we need, we can create a dummy table to use as a legend by copying the color codes of Stages from the chart, updated chart is shown below

Let’s remove the grid lines on Value axis showing 20, 40, 60, and so on, as we want to see grid line depending on the months. Right click on Chart, click Format Chart -> Plot Area -> Background -> in Category Axis Grid Color, bring the slider to 0, to make it transparent, as shown below.

Updated chart is shown below, ready to be overlapped with the Horizontal table.

Step 5: Create the Horizontal Table,

Create a Horizontal Table and Assign Data as below, initial view of table is shown below

Step 6: Formatting the Horizontal Table

Autofit the width of Month Column Width, to do so right click in the Month Column Width and select Autofit width to content, and click OK.

Format Table to remove alternate coloring of columns. To do that right click on Table -> Format Table -> Appearance -> in Alternate color, select Frequency as 1 and the Color White from dropdown, and click OK.

Create a Break on Year of Start Date as below and click OK

Change format of column names as Black font with White background, center align text, and remove column name for Month Column Width, update height of Month Column Width to 4.25 inch, updated table is shown below, please click on the pic to enlarge, then you’ll see it better.

Step 7: Overlapping Chart and Horizontal Table

Before overlapping the Chart and Horizontal Table, let’s set their positioning.

Position of Horizontal Table: The top left corner of the Table should be 0.5 inch from left edge of report and 0.5 inch from top edge of report. To do so, right click on Chart, select Format Table -> update the Layout options as shown below, and click OK.

Position of Table: The top left corner of the Chart should be 0.0 inch from left edge of Horizontal Table and 0.55 inch from top edge of Horizontal Table. To do so, right click on Chart, select Format Chart and update the Layout options as shown below, and click OK.

After the last 2 sub steps, we’ll see that the Chart and Horizontal Table now overlap each other, if the Horizontal Table is in the front and Chart is in background, right click on it -> Order -> click Send to back.

Updated report is shown below. Let’s increase the column width of column headers in Horizontal Table, to make sure that right border of the column headers (1) in Horizontal Table and Category Axis line (2) are aligned horizontally.

It worked for me when I set the width of column headers in the Horizontal Table as 1.41 inch, updated table is shown below

As the left overlapped corner of the chart & table look aligned and perfect, left work on the right corner. From the below pic, we see that the width of Chart needs to be increase so much that the line/border (1) on Value axis showing 366 (in chart) should horizontally align with the right border (2) of column in table showing the month December.

It worked for me when I set the Chart width to 13.07 inch, right click on Chart go to Format Chart -> Global -> General -> set Width to 13.07 inch, updated report is shown below, right corner looks aligned and perfect too.

A few final formatting tasks are left, we need to bring in grid lines in the Chart, depending on the Month columns in the Horizontal Table. Let’s just make the whole Chart transparent, so the column borders of Month columns in Horizontal Table are used as grid lines. Right click on Chart, click Format Chart -> Global -> Background -> drag the slider to 0 to make the Chart transparent, and click OK, as shown below.

Updated report is shown below.

One final piece, let’s hide the Value axis, so we don’t see 0, 20, 40, and so on at the bottom. Right click on Chart -> Format Chart -> Value Axis -> Design -> under General uncheck Visible, to hide the Value Axis, and click OK.

Updated report is shown below, Voila, we’re done (except for the Legend).

Feedback and comments are appreciated. Also, let me know if there are any typos or grammatical mistakes, I will correct them. Thanks for stopping by, and hope this helps at least a few developers/users out there.