Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmohammed
Active Contributor
3,461
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.

(Please vote up on this Idea for Gantt Charts in Webi.)

End product using the workaround 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. 🙂
8 Comments
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.

 

 
ritchi
Explorer
0 Kudos
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


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 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 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 is changed


 

Any solutions or comments are appreciated

 

Thanks,

 

Richard
ritchi
Explorer
0 Kudos
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
ritchi
Explorer
0 Kudos
Hi Mahboob,

I was wondering if you did see my reaction and questions about your and mine elaboration. I am still open for your advise/solutions.

Thanks,

Richard
mhmohammed
Active Contributor
0 Kudos

Hi Richard,

Sorry about the delayed response, as I've been spending a lot of time on Power BI and Tableau lately.

I'm glad that you agree there are many limitations to the workaround, and as I said it in the post, ideally, we should just use it as a standard non interactive chart. Nevertheless, let me try and respond to few of your questions.

1. Please try to and restrict the Description labels to specific number of characters, say 30-40 characters.
2. As you got the week number already, instead of a Bar chart, how about using a Dual Axis chart and add dots for Current Date, as shown in the below blog.
https://blogs.sap.com/2021/04/07/dynamic-reference-line-for-date-in-webi-report/
3. This is not possible, how about you use input controls to filter the data, instead of scroll bar.
4. Don't know if there is a way.
5. My idea for new feature called Bar Width was implemented in BO 4.2 SP4 (below list shows it), please use that feature, you'll see it Format Chart options.
https://blogs.sap.com/2017/04/11/sap-bi-4.2-sp4-preview-of-whats-new-in-web-intelligence-and-semanti...

Thanks,
Mahboob Mohammed

mhmohammed
Active Contributor

Hi Richard,

If your client can sponsor a 3rd party tool (extension) for GANTT and/or others, then I'd look at the Custom Elements done by Need4Viz, we see something in the What's new Blog of BO 4.3 SP2. Scroll down to Custom Elements in this blog, and it has a GANTT chart.

https://blogs.sap.com/2021/12/08/sap-bi-4.3-sp2-whats-new-in-web-intelligence-and-semantic-layer/#EX...

You can try it for free as well, using a business email.

https://www.need4viz.com/freemium/

Also, checkout the live demo/reports they have, in the below BO env open for public to look at.

http://demo-public.need4viz.com/BOE/BI

Thanks,

Mahboob Mohammed

mhmohammed
Active Contributor
Please vote up on this Idea for Gantt Charts in Webi.
0 Kudos
Hi, another great blog from gregory.botticchio regarding Need4Viz (a freemium offering is available)
Labels in this area