Skip to Content
Technical Articles
Author's profile photo M Mohammed

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 vertically.

 

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 vertically 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. 🙂

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lateef Mohammed
      Lateef Mohammed

      Great work brother Mahboob. As usual you contribute lot of workaround for developers who are tasked with such requests by the users. As it is workaround definitely it comes with limitations and hopefully SAP provides a gantt chart option in their releases.

       

       

      Author's profile photo Richard van den Broek
      Richard van den Broek

      Hi Mahboob,

       

      I love your solution, but it’s true that there are many limitations with creating a (workaround for a) Gantt chart. I have found a few and want to present them to you, maybe you have some solutions for them.

       

      1)

      As far as I can see, it seems impossible to get the alignment from the columns in the Stacked Bar Chart “Project” and “Stage” (both Category Axis) to the left. In your and in my elaboration the alignment is always in the middle. If the description is short, it doesn't matter. But if it’s a longer description, it looks ugly.  Can this be changed?

      alignment

      alignment

      2)

      In my elaboration I didn’t only use Year and Month, but I also used Quarter, Month and Week. But if you use “Time Dimensions” for Start Date, you can only make certain combinations. For example you can’t combine “Week” in the same Time Dimension with Month, Quarter and Semester because it will never match. This means that you have to make an extra Time Dimension (making a variable for Start Date) and combining Year, Week and Day. What I want to achieve is a timeline (based on the current date) presented with a vertical line into the Stacked Bar Chart. In the picture below you can see that the weeknumber (and month) is yellow, presenting the current month and week. I created this using conditional formatting. So is it somehow possible to integrate another chart (presenting a visual timeline that presents the current week) into the main Stacked Bar Chart? And how do I do this?

      request%20for%20vertical%20timeline%20that%20shows%20current%20week

      request for vertical timeline that shows current week

       

      3)

      If the Stacked Bar Chart presents a lot of data lines, you have to scroll down to see them. But then the text/data (what you actually see as header) disappears from the horizontal table. Normally you achieve this by using reading mode in “Freeze Headers Rows”. But because the top part of the page is a horizontal table, there is no header. This really is a pitty, unless there is some solution….

      4)

      I want to integrate a link in the Stacked Bar Chart (that brings the user back into the primary system), but it’s only half succesfull. The link appears but doesn’t work because the link is not in a table, but it is in the Chart.

      request%20for%20working%20link

      request for working link

      5)

      In design mode you can create a simple report filter in the Analysis tab, by selecting the Interact subtab and click "Filter Bar". In my report the left column shows the name of employees. But if you filter on one name, the report changes the height.

      height%20is%20changed

      height is changed

       

      Any solutions or comments are appreciated

       

      Thanks,

       

      Richard

      Author's profile photo Richard van den Broek
      Richard van den Broek

      Hi Mahboob,

      Feedback and comments are appreciated you said. So I felt free to give you some issues in where I'm having problems. Have you had a chance to look at them?

      Thanks,

      Richard