Gantt charting for the holidays

Question

Post reply

Gantt charting for the holidays

David Leibowitz

SSC Veteran

Points: 244
More actions
December 21, 2007 at 12:03 am

#184963

Comments posted to this topic are about the item
Link for the RDL Example can be found at http://jetlounge.net/blogs/teched/archive/2007/12/21/gantt-charting-with-reporting-services.aspx
My blog: jetlounge.net

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Terry Saunders, Jr. Grasshopper Points: 17 More actions · Answer 1

I liked the article, this would have come in handy in the past.

abair34 Ten Centuries Points: 1076 More actions · Answer 2

Great article. I'm planning on using this method to create a gantt chart for all of our sql server agent jobs based on the MSDB metadata. I wish the RDL was supplied on the webpage. Did I miss the link somewhere? When I finish the report I'll try to send it on to you guys.

David Leibowitz SSC Veteran Points: 244 More actions · Answer 3

^^

I don't know if they posted the RDL with this article yet. If you want to download the example you can get it from my blog at :

http://jetlounge.net/blogs/teched/archive/2007/12/21/gantt-charting-with-reporting-services.aspx

My blog: jetlounge.net

back_of_the_fleet Say Hey Kid Points: 683 More actions · Answer 4

This is a great little article which I've used as the basis for a report linking information from a Project Management database (Planned Start Date, End Date, Estimated Effort) with a Time Sheet database (Actual Start Date, Actual Effort). Fundamentally the report is the same as the example except I've coded so the gantt bars are coloured depending on a couple of (self explanatory) factors:

On the Data tab for the Completed value:

=IIf(Fields!ActualEffort.Value > Fields!EstimatedEffort.Value, "Red", "YellowGreen")

On the Data tab for the Remaining value:

=IIf(Fields!EndDate.Value < Parameters!CurrentDate.Value, "Tomato","Gainsboro")

I've also added an action to all of the Data values to link to the project management application. The key value passed (Fields!ProjectKey.Value) is from the same dataset supporting the chart:

="javascrpt:void(window.open(" & Chr(34) & "http://www.abc.com/project/ProjectManagerUpdate.asp?key=" & Fields!ProjectKey.Value & Chr(34) & "))"

The brownie points are already accumulating - so thanks very much!

John Rogerson
BI Technical Lead
Clear Channel International

cvitacek Grasshopper Points: 23 More actions · Answer 5

Fantastic! I just got this working and your right the first thing I wanted to do was turn on the vertical grid lines. It would seem to me this would work if the SSRS chart objects would let you use "Transparent" as a color option on the appearance tab. What is further frustrating is "Transparent" is a working option for things like Chart Area Style hatch color fill, very inconsistent.

Thanks Again!

clement.ratel Old Hand Points: 319 More actions · Answer 6

Hi,

This article is interesting for me because I want to create a Gantt chart of SQL Server's Job Activity.

Just noticed this very little error :

[...]INSERT INTOProjectStatus[...]

should be

[...]INSERT INTO ProjectStatus[...]

for the first INSERT to work.

a+, =)

-=Clement=-

Configuration :

SQL Server 2005

balicea-1005146 Valued Member Points: 66 More actions · Answer 7

balicea-1005146

Valued Member

Points: 66

February 3, 2009 at 2:32 pm

#937573

Great article - thanks! 😉

maria.liana.p.buro SSC Rookie Points: 29 More actions · Answer 8

Hey guys, how do you do this?

So, create your label buffer parameter called LabelBuffer, set it to Integer and Hidden with a default value of =10.

balicea-1005146 Valued Member Points: 66 More actions · Answer 9

Well, the technicalities depend on what version of SSRS you're using but if you know your way around ...

Just go into your report parameters.

Create a parameter named LabelBuffer

Set the type to Int

Set the default value to 10

🙂 It kind of confused me at first too, I think I read it wrong. But it works.

SimonD-978595 SSChasing Mays Points: 618 More actions · Answer 10

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 Grasshopper Points: 17 More actions · Answer 11

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

balicea-1005146 Valued Member Points: 66 More actions · Answer 12

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.

mskaterina SSC Rookie Points: 36 More actions · Answer 13

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.

shadowgreg Grasshopper Points: 15 More actions · Answer 14

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