SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Gantt charting for the holidays


Gantt charting for the holidays

Author
Message
SimonD-978595
SimonD-978595
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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!!

Regards,
Si
resh99
resh99
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.

Regards.

Roberto
Attachments
gantt.bmp (31 views, 1.00 MB)
balicea-1005146
balicea-1005146
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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. Smile Let me know how it goes.
mskaterina
mskaterina
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Attachments
Gantt.png (29 views, 12.00 KB)
shadowgreg
shadowgreg
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Syed Qazafi
Syed Qazafi
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
Thanks
Qazafi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search