Saturday, December 29, 2012

SSRS 2008 R2 - Range Bar Chart (GANTT) - updated



Hi All,

Sorry for not posting any updates on my blog. 

Anyways I just updated the Range bar chart (GANTT) using SSRS 2008 R2 Business Intelligent Studio. And here is the actual output of the report:



Here are the video tutorials of implementing this report:

Part 1

Part 2


Click HERE to download the RDL file (File -> Download) for this Range Bar Chart Report.

Below given details are references for you guys:

How to implement this Report:
The below given image has all regions identified to implement this report:





In the above picture, all marked regions (1, 2, 3, and 4) are individual Range Bar Charts with updated properties and properly aligned for the look and feel. 



SELECT TaskId, TaskName, StartDate, EndDate, SubTaskId,
      CONVERT(datetime, '1/1/2012') AS TempStart, CONVERT(datetime, '12/12/2012') AS TempEnd
FROM
(
      SELECT TaskId, TaskName, StartDate, EndDate, 0 AS SubTaskId FROM Task
      UNION
      SELECT TaskId, TaskName, StartDate, EndDate, SubTaskId FROM SubTask
) AS tasks
ORDER BY TaskId, SubTaskId


The query is returning the following fields:



  • TaskId
  • TaskName
  • StartDate
  • EndDate
  • SubTaskId
  • TempStart
  • TempEnd
For time being ignore the fields TempStart, and TempEnd. Let's start with chart. Right click on the Report design area and insert a Range Bar Chart to report.



After adding the chart to report the chart looks like this:
As you can observe, this part is little different than in SSRS 2008.  Values is where we select the date range fields, and Category Groups is where we add columns to group data. I added EndDate to Values. Select the EndDate and right click select "Series Properties" and update the series as given below.


As you can see I also added TaskId and SubTaskId to the Categories Groups. You can right click on each of the Category Group select "Category Group Properies" to change the sort options. As I added integer fields, I am changing my sort direction to Z - A form A-Z. Here is how the properties window looks like:

Repeat the same for SubTaskId Category Group As well. Once this is done, let us get rid of all the titles, legends, and Vertical Axis. For titles you can simply select and delete. For Vertical Axis, select the Axis and uncheck "Show Axis". Now the report should look like this:
And the report output should look like:
Now Go back to Design mode and select the Horizontal Axis, right click select "Horizontal Axis Properties".


  • Select Labels -> then check "Hide axis lables" checkbox.
  • Select Major Tick Marks -> then check "Hide major tcik marks" checkbox




Now when you view the report it should look like this:
As you can see except the margins, rest is looking good. You can update the label for the Series in the chart as explained in my previous post. Before discussing how to remove the margins, let me explain some customization you can do with the Major/Minor Grid Lines for Horizontal Axis. Select the Horizontal Axis, and go to the properties window. Here you see two important sections that controls Major and Minor Grid Lines. 


As you can see both Major Grid Lines and Minor Grid Lines has same fields. Here is how we can use it.


  • Enabled : This has values of Auto, False, True. False will make them not to be shown in the chart, and True does show the lines on the chart. I will explain later about Auto option, for time being you can change the value to True.
  • Interval Type:  This is very important fields which controls where to show the lines. It has the following values. 
  • Interval : This is an integer field. You set the interval between the lines.


If you set Interval Type as Months and Interval as 2, then for each two months in the chart you can see grid line. And the remaining fields under this group are to control the line style and color. I let you guys to play around it. You can set different colors for Major and Minor grids. You can hard-code the Interval and Interval Tyoe. Or a better options would be to have two Report Parameters MyInterval and MyIntervalType  and use these in the formula expression. I will leave it to you guys to play with these parameters.

Now we go back to removing the margins for the chart area. I am saving this for last because once the margins are removed it is difficult to select individual parts of chart in design areas. Go to design mode, and click on the chart. Now click again to select the chart area. On the properties window ensure Chart Area is selected. It has the following two properties.

  • CustomInnerPlotPosition
  • CustomPosition

By default both properties has Enabled property set to False. You make it to True to change the Height and Width of chart area. Both Height and Width are set to value from 0 - 100, which means it is a percentage. CustomInnerPlotPostion will let you control the Height and Width of the chart area. And CustomPosition will let you control the area of the chart within the selected chart area. Here are some exaples:



I set the CustomPosition and CustomInnerPlotPosition to 100.

Now we go to the Months bar chart. Here is how you can do that. Copy the current chart and paste it in the report. Now we modify the copy to look like the the Months bar.
Select the copies chart. Go to properties window, select Chart Areas collection. This is another way you can go to the CustomInnerPlotPosition and CustomPosition properties. I changed the CustomInnerPlotPostion as given below.


Now Select the Horizontal Axis, and go to properties window. Set LabelsFormat to '="MMM"' and MajorTickMarks -> Enabled to True.  You can check other parameters like LabelInterval, and LabelIntervalType to control at what interval to show the labels.
Now Chart Data -> Select EndDate, Go to Properties Window. Now update the CustomProperties -> PointWidth to 0.
Now click on the report, and click again to select the chart area. Go to Properties window -> CustomInnerPlotPosition. Set Height to 0.01
Then adjust the height of the report, and align with the original report like shown below. You can also view the output of the report given below.
Now we are almost done with the Months bar. You can copy and paste this Months chart and modify the Lables format to '="yyyy"' to get years and Change the Interval to 1, and IntervalType to "Years".

Now lets make another copy of the original chart and update some properties to show the task name. Select the copied chart and Chart Data -> Right click on EndDate select Series Properties and change the to TempEnd and TempStart instead of EndDate and StartDate.


Select the TempEnd/TempStart Series. Go to Properties Window, set Lable to =Fields!TaskName.Value



Now Select the Major Grid Lines on this report, right click -> Un-check the check box - Show GridLines

Adjust the width of the chart so that it looks like another column. Here is the how it looks like.


We are almost there .. Now lets start with finishing touches.
First let us give different color for main task and sub task in the Task Name chart. Select the Date Series added to Chart Data. Go to Properties Window -> 
Custom Attributes section:
          Bar Label Style - Set it to Outside
Appearance section:
          Label - set the value to =iif(Fields!SubTaskId.Value = 0, "Silver", "Beige") . This will give Silver color to Main Task and Beige Color to sub tasks (because for main task row, subtaskid will be 0).



And the report output is:

Now we do the same to Main chart.
Select the Chart Data -> EndDate and Go to Properties Window.

Custom Attributes section: 
     BarLabelStyle - set to Outside
     DrawingStyle - set to Cylinder

Appearance section:
     Color - set to expression  =iif(Fields!SubTaskId.Value = 0, "Aqua", "Khaki") . This will give Aqua color to Main task and Khaki color to sub tasks.

Label section:
     Label - set to expression  =DateDiff("D", Fields!StartDate.Value, Fields!EndDate.Value).ToString() + " days" . This will display the task duration in days as label.
      Visible - set this to True



Finally add a Textbox just above the Task Bar chart so that it looks like a Column Header. VerticalAlign the text to Middle, textalign to Center, and give a border and your GANTT chart is ready. Here is the final report output is:

You might be wondering what is the purpose of the database fields TempStart, TempEnd. Here is the reason: Range Bar Chart doesn't show the report if there is no date range. Main task may have a start and end date, but sub-tasks may be having these values as null. And our requirement is to show all the tasks in the chart irrespective of date range. So, I added the columns TempStart, TempEnd which always has default values and I can use this to show the Task Name chart. You can add multiple charts to show other data as well (like Task Name).

You can use formula expressions to controls appearance of the report in many ways. I just explained a couple. 

Please go through the video tutorial added.


Enjoy folks!!!
Cheers,
Swamy



55 comments:

  1. Hi Swamy - this is really helpful as I try to make this work. Unfortunately the link is coming up as broken. Could you double check it?
    Thanks

    ReplyDelete
  2. Updated the link.. Let me know if this works.

    Thanks.
    Swamy

    ReplyDelete
    Replies
    1. Hi Swamy,

      You blog is superb! It's really helped me to design some of my range chart reports. I was wondering whether you can help me to create calendar view range chart. I have done a report as a matrix view in SSRS how ever I would like to create a visual calendar view. The data set as below...

      Course ActivityStart ActivityEnd
      CON11004.4.c1 08/09/2014 15:30 08/09/2014 16:30
      CON11004.4.c1 08/09/2014 16:30 08/09/2014 17:30
      CON11086.4.c1 09/09/2014 15:30 09/09/2014 16:30
      CON11086.4.c1 09/09/2014 16:30 09/09/2014 17:30
      CON11160.4.c1 09/09/2014 16:00 09/09/2014 17:00
      CON11160.4.c1 08/09/2014 16:30 08/09/2014 17:30
      CON11286.4.c1 11/09/2014 15:30 11/09/2014 16:30
      CON11286.4.c1 11/09/2014 16:30 11/09/2014 17:30
      CON14270.4.c1 08/09/2014 15:30 08/09/2014 16:30
      CON14270.4.c1 08/09/2014 16:30 08/09/2014 17:30
      CON14270.4.c2 12/09/2014 15:30 12/09/2014 16:30
      CON14270.4.c2 12/09/2014 16:30 12/09/2014 17:30
      CON15420.4.c1 11/09/2014 16:30 11/09/2014 17:30
      CON15420.4.c1 11/09/2014 17:30 11/09/2014 18:30
      CON15424.4.c1 11/09/2014 16:30 11/09/2014 17:30
      CON15424.4.c1 11/09/2014 17:30 11/09/2014 18:30
      CON15426.4.c1 09/09/2014 15:30 09/09/2014 16:30
      CON15426.4.c1 09/09/2014 16:30 09/09/2014 17:30
      CON30148.4.c1 09/09/2014 16:30 09/09/2014 17:30
      CON30148.4.c1 09/09/2014 17:30 09/09/2014 18:30
      CON30148.4.c2 10/09/2014 13:00 10/09/2014 14:00
      CON30148.4.c2 12/09/2014 16:00 12/09/2014 17:00
      CON41598.4.c1 08/09/2014 16:00 08/09/2014 17:00
      CON41598.4.c1 09/09/2014 16:00 09/09/2014 17:00
      CON42003.4.c1 27/11/2014 17:00 27/11/2014 18:00
      CON44950.4.b1 09/09/2014 15:30 09/09/2014 16:30
      CON44950.4.b1 09/09/2014 16:30 09/09/2014 17:30

      and I want display the data set as following calendar view style....

      Course Monday Tuesday Wednesday Thursday Friday
      CON11004.4.c1 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - - - - - - - -
      CON11086.4.c1 - - 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - - - -
      CON11160.4.c1 - 16:30:00 - 17:30:00 16:00:00 - 17:00:00 - - - - - -
      CON11286.4.c1 - - - - - - 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - -
      CON14270.4.c1 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - - - - - - - -
      CON14270.4.c2 - - - - - - - - 15:30:00 - 16:30:00 16:30:00 - 17:30:00
      CON15420.4.c1 - - - - - - 16:30:00 - 17:30:00 17:30:00 - 18:30:00 - -
      CON15424.4.c1 - - - - - - 16:30:00 - 17:30:00 17:30:00 - 18:30:00 - -
      CON15426.4.c1 - - 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - - - - - -
      CON30148.4.c1 - - 16:30:00 - 17:30:00 17:30:00 - 18:30:00 - - - - - -
      CON30148.4.c2 - - - - 13:00:00 - 14:00:00 - - - - 16:00:00 - 17:00:00
      CON41598.4.c1 16:00:00 - 17:00:00 - 16:00:00 - 17:00:00 - - - - - -
      CON42003.4.c1 - - - - - - 17:00:00 - 18:00:00 - - -
      CON44950.4.b1 - - 15:30:00 - 16:30:00 16:30:00 - 17:30:00 - - - - - -


      Is there any chances to do that will be highly appreciated......

      Regards,
      Zafor.

      Delete
  3. Good Morning,
    You have another blog post that has a chart towards the bottom. 3rd from the bottom actually. I was wondering if you had documentation on that chart. How did you do it? I've posted the link below.

    http://pnarayanaswamy.blogspot.com/

    Is there any way I could get you to Email me at Dave.Burkett@Jacobs.com? I have no idea of knowing when/if you'll update this. Thanks!

    ReplyDelete
  4. If possible we could also talk by phone. I see by your profile that you're here in Houston. I look forward to a response either way.

    Thanks for your time.

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

    ReplyDelete
  6. Hi Gavindra,

    You need to add this rdl file as Add Existing Item to the SSRS project. Then you will be able to see the chart instead of xml version.

    Cheers,
    Swamy

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

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

    ReplyDelete
  9. Select Horizontal Axis -> Go to Properties Window and update the LabelsFormat to ="MMM"

    ReplyDelete
  10. Swamy, you have a Gantt Chart on another page that has a status bar going through the center. Can you tell me how get that status bar in there along with the task bar?

    Thanks

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

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

    ReplyDelete
  13. Please go to the beginning of this Blog. You will see 2 videos for the tutorial.

    Thanks,
    Swamy

    ReplyDelete
  14. can you please help me how to add month name and corresponding dates in the month to show in the header instead of show only month name exactly like in this image http://3.bp.blogspot.com/_B35uCnDNR4M/TJFNhHkjH5I/AAAAAAAADUc/8uxzl9MXcew/s1600/17.JPG

    ReplyDelete
  15. Hi Viniston,

    You need to give Interval=1, IntervalType=Days, and LabelFormat as "dd" or "DD" for the chart (Horizontal Axis Properties) to show dates. You can play with the LabelFormat to control what to show as label. Please check date formats.

    Swamy

    ReplyDelete
    Replies
    1. Thanks Swamy .... you are doing nice.....please kepp it up.....

      Delete
  16. Hi Swamy,

    Thank you for this very interesting post ! Very usefull and very easy to understand thanks to your detailled explanations.
    Could you try to show a task with start and end at the exactly same timestamp ?
    It won't display, do you have an idea to fix this ?

    Kind regards,
    Vincent.

    ReplyDelete
    Replies
    1. Hi Vincent,
      I think if start and end time have same timestamp, there is nothing to show as bar. But you can always show task name as label with some status saying start and end time are same or finished. Or if you have to show a bar, you can always add a buffer days/months to end date if start and end date is same. Hope this helps.

      Swamy

      Delete
    2. Hi Swamy,

      Thanks a bunch. I fixed this directly in the DataSet :
      CASE
      WHEN ScheduledStart=ScheduledEnd THEN DATEADD(hh,2,ScheduledEnd)
      ELSE ScheduledEnd
      END AS ScheduledEnd

      Vincent.

      Delete
  17. Hi Swamy,
    First of all thank for your useful information. I am stuck somewhere, When I try to create a drill down gantt chart in ssrs 2008 r2. First, it shows the information of the projects. When i click the project then it shows the information of the tasks and sub tasks. Could it be possible on SSRS? Could you please give me some information how do i accomplish this report?

    ReplyDelete
    Replies
    1. Hi Shiva,

      I think it may be possible, I am not sure. Let me try it myself. Please do not expect any responses soon as it takes time.

      Thanks
      Swamy

      Delete
    2. Hi,

      I did this, very easy ! On the Chart Series (the bar) right click for the series properties. There you have Action, select "Go to report" select the correct report and add the requested parameters.

      Vincent.

      Delete
  18. Swamy,

    do you have any idea how to set the height of the bar as fixed ?

    Kind regards,
    Vincent.

    ReplyDelete
    Replies
    1. Hi Vincent,

      You cannot really set a fixed height for the bar. It actually calculates automatically based on rows for the page. One thing you could do is use DynamicHeight. Set the DynamicHeight to a formula that gives the height based on number of rows retrieved from database. Now as the height is fixed, you will always see fixed height for the bar. This should work.

      Thanks,
      Swamy

      Delete
    2. Hi Narayana, it should be better to increment automatically the height of the chart to maintain almost fixed the height of a single bar of the range bar chart. Is it to possible to do it?

      Delete
  19. Swamy,
    Great documentation and tutorial. Wondering if its possible to have a range bar chart show activity during a time range which has breaks in it. For example, I am attemmpting to plot the weeks a project was worked on, however its possible the work wasn't done continuously. A project may have been worked on weeks 15, 16, 17 and 19...with no work being done on the project during week 18. I would like to show the activity on the project as a single bar going from week 15 to week 19, with a blank or break in the bar at week 18. Hope this makes sense. Thanks in advance for your time and response. Jim

    ReplyDelete
  20. Swami,
    What I would hope to achieve is having the subtasks on the same row as consecutive phases of the main task. I would even show only the subtasks on the same row, and display the tasks only as "legends" in the left chart. How could this be possible in SRSS? (It should look like I tried to show below)

    |Task 1| | Subtask1-1 | Subtask1-2 |
    |Task 2| | Subtask2-1 | Subtask2-2 |Subtask2-3|

    ReplyDelete
  21. Well, the spaces were not displayed correctly, but you can assume each task is starting/ending in different moments, not all together at the same time. The chart would be like a Grantt, but instead of bars I would like to have "stacked" bars:)

    ReplyDelete
  22. Hi Swamy,
    Do you have any idea how to make an arrow that go from the end of the bar to the beginning of the next one when there is a dependency between tasks?

    ReplyDelete
  23. hi swamy...
    any idea if the two sub tasks started parallel...how to show them horizontally...

    ReplyDelete
  24. Thanks for this blog. Saved me countless hours during my report design

    Hammad
    http://epmadvice.blogspot.com.au

    ReplyDelete
  25. Hi, Swamy, i want put a line were today has located, is possible?, thanks in advance!

    ReplyDelete
  26. 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
  27. Hi Swamy, I was wondering if you have any suggestions for flagging certain tasks (using Reporting IDs) within a Project 2010 schedule that would then show up on the Gantt. Any help would be appreciated!!

    ReplyDelete
  28. I would like to do 2 tasks.
    1. Group the left chart by only main tasks, which has to cover till its sub tasks positions. Is it possible? I tried with category group of main tasks but the positions are not matching between tasks chart and main chart?
    2. Format month label to Quarter. I tried interval type is month and interval=3, but I could not format the label as Q1,Q2 etc. Can you please help me with this?

    ReplyDelete
  29. Hi Swamy, is it to possible to enlarge the bars containing the task name in order to show long description? Thanks

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

    ReplyDelete
  31. Hello, Swamy. Thanks for your video and tutorial. Great tricks!

    I am, however, stuck at the task bars. Mine are a copy/paste as yours are, but the task bars are mimicking the data bar and are not filling the chart area from left to right - as yours are. I didn't see any spot that you changed anything in that area other than the gap between the bars top to bottom. Can you advise how your are filling?

    Thanks!

    ReplyDelete
  32. Dear Swamy,

    Thanks for writing such a useful post.

    But, I do have an issue which is irritating me and is as follows:

    In my case, I have many tasks (say 150) and in this case, Gantt is messing up.

    So, I have increased the Area according to number of tasks. But in other case, I have less no. of tasks (say 15-20) and in this case, each and every Gantt Bar size is becoming too huge.
    So, Is there any way to fix the size of Gantt Bars irrespective of the selection of No. of Tasks???

    Thanks & Regards,
    Shravan Dubey
    MS EPM Consultant

    ReplyDelete
  33. Hi Swamy,

    Thats a wonderful post. can we set up a pagination when there are too many records ?

    Thanks in Advance
    Mansura Shaik

    ReplyDelete
  34. Hi Swamy, Thank you for a great post. How Do I change the years to Quarters?

    ReplyDelete
  35. Hi, thank you for this post. It's very helpful.

    One concern is that if there are many records, how does the chart look like?
    Can we do paging?

    ReplyDelete
  36. Amazing article mate.
    I am trying the same with XML web Service data source I getting no where.

    --
    I have a data source of XML web services in SSRS 2008 R2

    I got the following warning

    "unknown collection member for dataset field""

    SO I tried to use CDate() that got the following error.

    [rsRuntimeErrorInExpression] The High expression for the chart ‘Chart1’ contains an error: Conversion from string "" to type 'Date' is not valid.

    But never had data shown in range chart in preview or while running the report. Any help will be great.

    ReplyDelete
  37. Hi,

    is there a way to display axis label as quarters?

    For example : 2015 Q1, 2015 Q2, etc

    Thanks

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

    ReplyDelete
  39. Wonderful article. currently i prepared a gantt chart that lists out activities based on start date and end date. my requirement is within each bar based on time, i should show it in different colors. Like any event starting between 8am till 2 pm should in blue, 2pm till 8pm in green and rest in any other color. something like below:
    Activity A: [*****Blue*****][******Green*****]
    Activity B: [**Blue***][******Green*****][****Yellow***]
    Activity C: [**'Green**][****Yellow***][***Blue***]
    Please help.

    ReplyDelete
    Replies
    1. hi shrinivas...
      have you done that report?
      I need to create similar report...

      Delete
  40. Hello Sir,

    Thanks for this great article.

    I have implemented the schedule report as per your guidance. I am facing an issue. i am getting an error when the dataset size increasing from 1735.

    please suggest an approach to overcome it.

    ReplyDelete
  41. Very informative, Thanks! Can we add Quarter interval type as well along with Years and Months?

    ReplyDelete
  42. I want to share with you all here on how I get my loan from Mr Benjamin who help me with a loan of 400,000.00 Euro to improve my business, It was easy and swift when i apply for the loan when things was getting rough with my business Mr Benjamin grant me loan without delay. here is Mr Benjamin email/whatsapp contact: +1 989-394-3740, 247officedept@gmail.com / 

    ReplyDelete
  43. Thanks Swamy. This is very informative.
    Can we also have quarters along with months and years?

    ReplyDelete