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
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".
As you can see both Major Grid Lines and Minor Grid Lines has same fields. Here is how we can use it.
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.
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 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!!!
After adding the chart to report the chart looks like this:
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:
- 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.
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
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
