Wednesday, September 1, 2010

Range Bar Chart (Gantt Chart) using SSRS 2008

SSRS 2008 – Range Bar Chart (GANTT CHART)– Step By Step
Hi Folks,
This is my first post on SSRS 2008. I have been working on this SSRS 2008 for almost 6 months from now. I have started using the range bar chart which is very interesting. I searched over internet for more details on this but of now use. With my experience I prepared a step-by-step introduction to GANTT chart so that developers will get a good starting point for developing GANTT Chart using SSRS 2008.
Main Criteria for Range Bar Chart is the data that is used to display in the graph.
The dataset that is used should return at least three details.
·         Task Name
·         Start Date,
·         Duration or End Date for the task.
Assuming that my dataset returns all the three details, I am going ahead to create the Gantt Chart.
My dataset returns  TaskName, StartDate, EndDate.
From Tool window, drop the Chart control in report body and you will see the below window, and select Range Bar chart as heighted below.

After clicking OK, this what you see in the report body.
Let’s discuss something about what we see here.
Category Fields:
This is the area where you will drop your fields to group the data, here I dropped the TaskName. So data is now grouped by TaskName. You can have multiple fields in category fields. I tried with 4 categories, and it worked like a champ.
Right click on the dropped category fields, select Category Group Properties.
Here you can control the label text.
Data Fields:
This is the section where we specify our start and end dates for the tasks. The fields you drop here has High and Low values (like a pair High, Low values).  It’s highlighted in the image below.

Now Right Click on the dropped, and field and select Series Properties. This is the very important information that needs to be updated.
Here Top value can be EndDate or (Duration + StartDate) and the Low value is StartDate.  You can click on Fill and specify a color for this series Bar.
Now Click on the dropped field, and it will select the series in the graph. Right click on this and select Show Data Labels.
Interestingly SSRS 2008 is very flexible in showing the labels, and colors for the bar dynamically based on expressions you define.  This is screen you see after selecting the Show Data Fields.
Customizing the series bar:
SSRS 2008 provides wide range of options to control the data series bar. I am going to discuss a few here and you can explore more.
Let me start with the label text. By default it shows the date as the label text. But in most of the cases we may need to show the Task Name or Subtask Name. Here what you need to do. Select the data series by clicking on the graph data series bar or clicking on the data fields section and open the Properties window (not the right click series properties) (Ctrl W P).

If you observe there is Boolean field UseValueAsLabel with value True. Because of this, it shows the date as label text by default. If you want custom label text, then change this to false and change the Label field here. I changed it to display TaskName by changing the Label value to “Fields!TaskName.Value”. You can have custom expression to populate the label. You can also customize the font colors, font widths, formatting borders for this label in this label section.
Now we move to the CustomAttributes section. This is very important part for the Gantt Chart for customization. Now let’s see what is there in it and how you can modify according to our requirements. I am discussing only the options I have explored till now.
BarLabelStyle:
This controls the label text alignment. Available values or Center,Left,Right,Outside. Selecting outside will show the label, just before the data series bar. You can select any value, and the default value is Center.
DrawingStyle:
This controls the drawing style like cylinder type bar or regular bar. Here is the list:
DrawSideBySide:
This is very interesting attribute. Here is example where we can use it.
I have a task with multiple subtasks and all my sub tasks are sequential (like first task end date is second task start date). In this case I want to show all the bars in same line, and with different colors. By setting value to “False”, this will allow us to show all data series bars related same grouping category to be displayed in the same line like shown below.

To view all the sub task data series group in one line, you need to set this DrawSideBySide to False for all the sub task data series.
PointWidth:
This is the field that controls the data series bar width. Maximum value is 1, and minimum value is 0. As you can see the default value is 0.8. If you want see like a line, then you can give like 0.05 or 0.1
In the same properties window, you can customize any field like Color with custom expressions.
Customizing the Axis Properties:
Right click on the Value Axis (X-Axis), select Value Axis Properties.
Under Axis Options, you have various options to customize the labels to be shown. By default it will show the date in short date format.
If you run the report, by default it will show only alternate labels both in X and Y axis. To view all the labels you need to check the check box Enable Variable interval. This is very common thing we may tend to forget.
You can give a specific Minimum, and Maximum values. You can specify an expression like Minmum is MIN(Fields!Start.Value) and Maximum is MAX(Fields!EndDate.Value).
Interval, Interval Type: I guess the name says it all. What is the interval (digit) and what is the interval type (Hours, Minutes, Weeks, Days, ..) you want to show in the value axis.  Accordingly you can change the display format in Number section. If you want to show the dates in HH:MM format, you can change it like given below.
Or you can give any specific format you want.  You can check out the options available for Labels, Label font sections here.
If you select the value axis (x-axis) and go to properties window you will see a wide range of fields you can customize. You can specify minor, major grid intervals, with specific colors, widths. You can always hide the axis (both X and Y) and you can hide the minor major grids and also minor major grid ticks.
Major, Minor grids applicable to chart area and major, minor grid ticks applicable to the labels section of the grid
Most of the properties are applicable to Y Axis as well (Series Axis Properties).
And here is the final Report out I got with Interval type Hours.
You can always customize the legends for the chart.
Here are some Gantt Charts report outputs I developed using SSRS 2008 Range Bar Chart:

Another example:

The above gantt charts are developed using multiple GANTT Charts with the same set of data.
Hope this is helpful
Hi All I just posted another blog on this topic which as video tutorials of implementing the GANTT chart:
GANTT Chart using SSRS 2008 R2

Swamy

37 comments:

  1. Really liked your website really got your point acrosss. Found it while going through google so wanted to tell you good job and you have a bookmark from me.

    ReplyDelete
  2. Good work.

    Is there any way that you can please share the
    http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNlunZKNI/AAAAAAAADUk/MQaI9d1Ckn0/s1600/18.JPG chart that how you did that.

    Thanks

    ReplyDelete
  3. @Marcus Tarrant - Thanks for your support

    @vam
    I will be posting another blog with all the details for this gantt chart soon.

    ReplyDelete
  4. Thanks.

    I will be waiting for that.

    ReplyDelete
  5. Hello Narayana Swamy,

    You have done it good work :).I also have followed your step mentioned above for Range bar chart and able to generate chart but now how I can set the bar colour based on start and end, how much are completed and how much are pending with different colour.Kindly explain how you generate these image "http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNhHkjH5I/AAAAAAAADUc/8uxzl9MXcew/s320/17.JPG" setps.

    Thanks
    Rajesh Thakur

    ReplyDelete
  6. Swamy, I like your article very much and I have a similar problem. But the Range Bar solution doesn't seem to work for me. Could you please see my comment today for the Question: "How to create Range bar chart in SSRS 2008" that you answered last year? Thanks.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hey Narayana,

    That was very useful information. However I want to know how do we create a RDL report like the one you showed above, titled as "Here are some Gantt Charts report outputs I developed using SSRS 2008 Range Bar Chart:".

    Basically I want to create a Gantt Chart using Range Bar chart. That is a report with a tablix like structure on left side and rangebar chart on right side so that it looks like a Gantt Chart.
    Any inputs over this will be appreciated.

    ReplyDelete
  9. it is very much useful to me ...thanks for posting such a nice article...pls keep on update some useful things in BI..

    ReplyDelete
  10. anyone figure out how he did this one?
    http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNhHkjH5I/AAAAAAAADUc/8uxzl9MXcew/s1600/17.JPG

    ReplyDelete
  11. How do I get more columns or categories on the Vertical Axis wihtout grouping them and rotating them? Like you have in you number 17 image...
    Thanks
    SJ

    ReplyDelete
  12. Never mind, I used Table and embedded the range report in a column...worked like a charm

    ReplyDelete
    Replies
    1. Hi SJ
      Can you please tell me how can i do that?
      embedding of table with range chart??
      Please

      Delete
  13. Hey Narayana,
    I am working on a gantt chart, bases on contracts for departments where a department has many contracts with a start and end date. I tried what you did but I am not able to visualize all the contracts, I just get the first one for each department. The main idea is that I should be able to see the contract period and the gap that there is between them.
    department name, start date, end date
    A 01/01/12 31/01/12
    A 14/05/12 22/09/12
    B 02/02/12 30/04/12
    B 06/03/12 15/06/12

    ReplyDelete
  14. Hi Swamy,

    I am lloking for a chart that has line and range chart together.
    What I want to implement with Range chart is Target values
    My dataset looks like
    Actual actualdate targetQ1 targetQ2 EffectDate Expirydate
    23 Mar12,2010 21 30 jan1,2010 June1,2010
    30 Aug12,2010 35 jul1,2010 Oct31,2010
    12 Mar12,2010 10 10 Oct2,2010 Dec1,2010

    I want show both actual and targets in one chart
    Actual in Line Chart and Targets in range or ? some chart q1 and q2 values at appropriate date and period.

    ReplyDelete
  15. Hi Narayana,

    This is really helpful and save me a lot of time. I followed your steps exactly but i'm having a problem. I've 21 categories, but the graph is showing only 4 of them. However, I could see 21 bars on the chart. I wonder what i'm missing here.

    ReplyDelete
  16. Hi Narayana,

    Your Gantt Charts report are great. Can you please share it?

    ReplyDelete
  17. Hi Narayana,

    The blog is very informative. Thank you. I tried creating the range graph but I am not getting all the datapoint values. my table looks like this,

    task start end
    ==== ====== ====
    A 2011-12-25 07:00:00.000 2011-12-25 07:50:00.000
    B 2011-12-25 07:50:00.000 2011-12-25 08:20:00.000
    A 2011-12-25 08:20:00.000 2011-12-25 09:10:00.000
    B 2011-12-25 09:10:00.000 2011-12-25 09:40:00.000
    A 2011-12-25 09:40:00.000 2011-12-25 10:20:00.000
    B 2011-12-25 10:20:00.000 2011-12-25 11:10:00.000

    I added start for TopValue, end for bottomvalue and task for categories. I tried everything that is in this blog like enable variable interval, draw side by side etc. but nothing resolved my problem.

    I am getting a graph like

    |
    |
    B| ----
    |
    A| ----
    |-----------------------------------------
    07:00 08:00 09:00 10:00 11:00 12:00


    I am not getting the values after first two rows. How to resolve this, please give me some suggestions

    ReplyDelete
  18. Hi ... I was wondering if you could help me figure out why the chart won't size for the whole amount of the data?

    I basically have done what you said and I have it displaying what it can from about 07:00 - 09:00AM but I can't get it to display the rest of the day.

    ReplyDelete
  19. How did you create the vertical marker to show the current date?

    ReplyDelete
  20. Please check my other blog post for more details on implementation of GANTT chart using SSRS 2008 R2. I have added the RDL file for download.

    Here is the link:
    http://pnarayanaswamy.blogspot.com/2012/12/ssrs-2008-r2-range-bar-chart-gantt.html

    Swamy

    ReplyDelete
    Replies
    1. Hi swamy this is siva from Guntur, CCE Batch mate can you send me your contact no or email to my id siva.muddana@gmail.com

      Delete
  21. I updated the latest blog with video tutorials of how to implement the gantt chart using ssrs 2008 R2.

    Swamy

    ReplyDelete
  22. can you please help me how to add dates on the header where now you are showing month name in this article?
    like you are showing one output in this image http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNhHkjH5I/AAAAAAAADUc/8uxzl9MXcew/s1600/17.JPG. Exactly like this i want to develop please help me.....

    ReplyDelete
  23. Can you please share the article about this http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNhHkjH5I/AAAAAAAADUc/8uxzl9MXcew/s1600/17.JPG

    ReplyDelete
  24. Step by step description is really helpful. Can you please provide the details about the charts that you have developed? I'm exactly looking for the same type of charts.

    ReplyDelete
  25. Hello Narayan swami,

    Can you please provide the details of creating the range bar chart with multiple coulmn on the left?

    ReplyDelete
  26. can you please add the RDL file of the Gantt chart that has multiple columns on the left and on the right it has the range bars?
    How did you do that? is it just with one report or did u do with sub report?
    Please help me out

    ReplyDelete
  27. Hi Narayana Swamy Palla,

    Do you have any idea on how to achieve solve the below issue:

    https://community.dynamics.com/ax/f/33/t/128774.aspx

    ReplyDelete
  28. Hi, I have the same problem that someone has mentioned above. When I group on TaskName series, the chart shows just the first occurrence of that task. I would want to see the same task as many times as in the table. Please help.

    ReplyDelete
  29. Great post, are there any gantt chart templates to be used here. If you can point out a good gantt chart software then I will be able to find some good templates. Thanks

    ReplyDelete
  30. Hi thank you so much for publishing this and also your You tube videos which are great. I would like to be able to group by a period - say year - for tasks that have a duration of over a year - so that users can scroll along a long chart. Can you suggest how to group and add a page break where tasks are of a long duration?

    ReplyDelete
  31. PPC Expert For Tech Support |inbound calls for tech support, pop up calls for tech support Call at: +91 981-142-0083 ppc management experts ppc call provide by osiel web
    expertppc

    ReplyDelete
  32. Extremely helpful, Swamy! Excellent videos, I have learned all I needed to configure Range Bar Charts in SSRS and achieved what i wanted. I had to watch the video twice but everything is explained in a very simple and sensible way! Thanks!

    ReplyDelete
  33. Great work! Helped me alot and saved me from going insane while trying to create a report with split ranges. Thanks!!!

    ReplyDelete
  34. Article was nice, It reminds me to remember things I know, but tend to forget during my busy day! Thanks! Gantt Chart views allow you see, at a glance, task information in a columns and rows with corresponding bars along a timeline. You can customize the chart part of these views in Project to better suit your needs. Primavera P6 tool really usefull for this process Primavera Course

    ReplyDelete