Muller Jannie

About myself and everyone else.

Build a SQL & Reporting services Gantt chart.

Unless you are able to buy a stack of reporting gadgets or a reporting suite you are probably stuck with using SQL server and the freebies such as reporting services. I’ve been toying with the idea of creating a Gantt in SQL using the bar chart in report services but I’ve never really decided to actually try it. Untill recently , I had to come up with a date in the future and aside from using formulae in excel with a pretty straight forward date generation (which is pretty straight forward) I decided to use SQL to get my brain some exercise plus up my skill in reporting services.

People tend to shy away from plans however I think if I don’t plan, not even a little I still end up with the same result but I get side track more often with cool things I pick up along the way. When I want to achieve a new learning such as the pivot command or recurring CTE I pick one new item, such as recurring CTE and stick with it, I won’t combine it using other new items. This is because if you hit a problem you don’t know in which new part it is. Maybe you are gifted enough but most of us learn through repetition or understanding, at least syntactical you may not have issues it helps to rewrite the code because you may not ever need to use it in the same manner.

Assumptions, in order to draw a Gantt, I’m going to use excel pivot, why ? I’ve used excel for too many things to put down in a post. The beauty of it is the ease of aggregation and the functions that comes along with it. What do I need for a Gantt.

I need an anchor. In project terms it will be the first project step. Using the Prince2 standard, I’ll be using a house building sample.

The first take would be to build a foundation, lets not get technical into the acquiring of the land, building permits, conveyances, bribes to neighbours etc.

One I have the anchor  I’ll need a start date and end date or start date and duration. This will be used to generate the offset for the dependant step, i.e. building a wall. The anchor also won’t have any dependencies. I’m going to break my Gantt into a measure suited for building and thus my timeframe is days, you can make this hours , minutes it really depends on your scale of the tasks.

Some terms, task – is the objective. Start Date – When the taks commence. End Date- when the task completes. Dependant – Is there any tasks that this tasks requires i.e. roof requires a wall unless you live in a tent. Then you just need a foundation or ground sail.

So where to begin,

I’ve generated a table with my tasks and their dependants. You can manually do this and if you are into .NET you should be able to put in a nice little GUI in no time with dropdowns etc. For now I create a list that looks something like this. **See App. 1

In my example I’ve create 3 time frames, I’ve built-in the code to accommodate months, weeks and days into the timeframe of my project. You can supply either one of them and leave the others NULL.

Once the data is in my table variable I loop through each task and loop through each day in the task. I work out if it’s a work day or not (only when you supply days). When you supply a week I assume it’s 7 days .i.e. 5 working days and 30 days i.e. 20 working days in a month. THen I loop through the dates and move onto the next task. I have test that this works for recurring tasks aswell that may have the same name but occurs at different parts of the process, I used building inspection for my recurring task.

Once you have the dataset you can connect to it from reporting services. You can use the tablix to do the aggregation, NOTE: If you are unsure of your dataset use copy + paste into excel for now, create a pivot and use the count aggregation. NOTE: Thinking of it now I’m not sure if I did the work allowing for gaps between tasks it will generate them but it might not indicate them properly. I just thought I’d mention it before some smarty comes and complain that he can’t copy and paste the code and it doesn’t work!

So test the data in excel using pivot and the count function. It will draw the pivot with dates scrolling at the top, and the tasks as the items and their count as the value. You basically want to see a whole bunch of 0’s and 1’s , we will use this to code reporting services for the pretty stuff.

Once in reporting services.

Setup your tablix, with the task name running down vertically and the dates running across horizontal. You will use a count(Fields!task) in order to get the 0′ and 1’s . Once you have this you can use conditional formatting to change the 0’s to white in order that it appears blank and change the 1’s background and text to blue in order to create a Gantt line. As the dates are running across the top you can see when the task start and stop and when the next start.

**App 1

Insert into @Tasks Select‘Foundation’, 0,null,2,null

insert into @Tasks Select‘Roof’, 4,null,1,null

Insert into @Tasks Select‘Plumbing’, 1,10,null,null

into @Tasks Select‘Building Inspection’, 1,4,null,null

I used

Advertisements

2 comments on “Build a SQL & Reporting services Gantt chart.

  1. viniston
    June 8, 2013

    Can you please give us development guide for this report?….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on August 15, 2011 by in SQL Server and tagged , , .
%d bloggers like this: