Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Gantt charting for the holidays Expand / Collapse
Posted Wednesday, June 17, 2009 8:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 3, 2010 4:26 AM
Points: 68, Visits: 116
Great article! I have this working but one problem still remains. I want to introduce a constant line in the Y-axis to display where "TODAY" is in the Gantt chart.

I have already tried manipulating the "Minor gridlines interval" on the Y-Axis and this works in some cases where for example "TODAY" is far greater than the start date of any task, but where this is not the case I get a repeated minor gridline interval indicating multiple "TODAY" points on the gantt chart which is clearly wrong.

I cannot seem to crack this problem. All of the grouping and data series stuff effectively alters the "category" elements of the report in the X-Axis so I am all out of ideas. Any of you bright sparks out there got any clues.

For those of you who haven't read the article we are dealing with a stacked bar chart here, SSRS and I am using the report viewer control to render an RDLC report (converted from the original RDL in the article).

Any help would be appreciated!!

Post #736656
Posted Tuesday, April 20, 2010 4:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 14, 2012 3:17 PM
Points: 1, Visits: 4
Hi David, first... this is an excellent article, really works for me. But I have a question. On reporting services I use dates parameters, and this are like:

start_datetime between @startdate and @enddate

but I have a problem with this. If my gantt bar start before my startdate I can't see my bar. I only see all the complete bars who start between this parameters. Do you know how to set or what can I do to could show in my gantt the partially part of bar just for the parameters that place.

I don't speak english so well, and I don't know if You could understand me. I attach a picture of my gantt.

Thanks for your time.



  Post Attachments 
gantt.bmp (19 views, 1.03 MB)
Post #907320
Posted Wednesday, April 21, 2010 9:08 AM


Group: General Forum Members
Last Login: Thursday, November 11, 2010 10:18 AM
Points: 10, Visits: 34
You can set your axis value to show a certain timeframe before your start date. Set the minimum value to

DATEADD(DateInterval.Month, -1, Parameters!StartDate.Value)

or however far back you need to go. That should work. :) Let me know how it goes.
Post #907812
Posted Friday, May 13, 2011 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 8, 2011 5:09 AM
Points: 2, Visits: 16
Hello everyone!!

I havea question to ask. This article is great, but i have an issue. My chart does not show the bars. It shows the start and end dates but i cannot make the bars appear. I am working in ssrs 2008 and it ts frustrating to find the correct properties in 2008. Can anyone help me??I am posting a screenshot of my chart. Any help would be much appreciated.

  Post Attachments 
Gantt.png (21 views, 12.33 KB)
Post #1108260
Posted Wednesday, June 1, 2011 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 1, 2011 7:50 AM
Points: 1, Visits: 2
Hi Guys,
I followed this guide to develop a report using RDLC's and the ReportViewer in .NET, and the methods described here seemed to work, but after tweaking my chart I have gained valuable insight into how it works within visual studio 2008 and reportviewer 9 (I am assuming it will be the same in this environment).

I made 2 parameters for the report, a “StartDate” and an “EndDate” with the format code of “dd MMM yyyy HH:mm”, and I retrieved all events which ran within those periods using my SQL statement. Once I had events, and the grouping the events belonged too, I was then ready to develop my Microsoft Report.

I then went about implementing the methods described within the article referenced by this forum "Gantt charting for the holidays" and it worked as described, however immediately was confronted with the shortcomings described in David's article, such as grid lines on the chart, and was determined to overcome them as it was requirement of the report I had to generate for the application I am developing for work, and with the axis showing dates I knew I could get this working. I then began changing the chart and tweaking it with several different calculations until I scratched it and started the chart from scratch again.

In the end I added the bar chart as described, reversed the x axis as described but did not create side margins, and instead of passing in a buffer parameter into the report, I passed in "StartDate" and "EndDate" parameters with the type of datetime (the same format code and values which were used to retrieve the charts events via the SQL statement), and used those as the min and max on the y axis. I then added my category grouping, and was ready to finalise my task by adding the "Values", so I decided to proceed cautiously with rigorous testing by displaying the data in a table above the chart.

I added the start date of the task, and it seemed as though there would be a bar from the start of the chart to the date I wanted appearing perfectly, however what I wanted was for that bar to be white with the event coming after it, and another white bar to the end. I then went about fiddling with the calculations in several different ways, and by the end of the day had a solution to make the chart appear perfectly, with an understanding of how to replicate it whenever I wanted. The first bar goes to the start of the event, and is in the format of date.

To accomplish this, I used the format code of “dd MMM yyyy HH:mm” for the start date Value (which is the first Value for the chart, and the only datetime Value which should be used).
Then the second parameter was
=DateDiff(DateInterval.Minutes, Fields!StartDate.Value, Fields!EndDate.Value)/1440
To get an accurate decimal representation of the amount of days the event went for (The tricky part of the chart, which I couldn’t find documented anywhere). Then I used the following for the third Value:
=DateDiff(DateInterval.Minutes, Fields!EndDate.Value, Parameters!EndDate.Value)/1440

I coloured the first and third Value white, so it appeared as though they did not even appear on the graph at all, and the chart finally worked with only the events being coloured, and the events lining up perfectly with the grid lines and their dates, bringing me much satisfaction in my job as I was able to solve this problem without any repulsive calculations which will bring pain to my colleagues when it comes time to edit it to suit business requirements.

I then was confronted with one final issue, if the start date of the task ran before the minimum Value on the y axis (the “StartDate” parameter), it would not be rendered into the chart. I managed to overcome this by modifying the SQL statement to return the “StartDate” parameter + 1 minute if the field was greater than or equal to the “StartDate” parameter, which immediately overcame that issue. This could also be accomplished using immediate if statements within the expression for the first Value in the chart as well via my testing. Hopefully this applies to SQL Server’s Microsoft Reporting, as what I read from your article successfully generated the chart you described.

Kind regards
Greg Goldberg
Post #1118037
Posted Friday, November 22, 2013 8:07 PM


Group: General Forum Members
Last Login: Tuesday, June 7, 2016 9:56 PM
Points: 11, Visits: 340
Hi There
I would like to develop the similar type of report as you did. What I would like to have to show estimated start date and estimated end date using another bar in the example given inside the article.
So my report will show estimated start date and estimated End date, Actual Start date and Actual End date on x-axis and % completion, Task on Y-axis.
Can you please send me your sample report
Post #1516990
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse