A range chart in SSRS can be used to produce a Gantt chart.of project. Below is a link to a great post that explains how to do it.
http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html
Unfortunately I have found the following limitations
- The width and height of the range chart does not grow dynamically when run.
- If too much data is displayed, the chart is unreadable
To counteract the above limitations, I have used the matrix control which can grow dynamically in terms of width and height to fit the data which remains readable.
The article describes how to display the timeline of projects with milestones using the Matrix control in SSRS. At the end of step by step guide you will end up with a report which looks like
The diamond shapes represent milestones while the blue color represents the duration of the project.
Create Milestones Timeline View Report
1. In your Web browser, type the URL for your report library in the address bar.
2. Click ‘New Document’> ‘Report Builder Report’. (Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).
(Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).
3. Click “Blank Report”
4. Add reference to a Shared Data Source.
To create a Shared Data Source , Click “New Document”> ‘Report Data Source’
Fill in the following details:
• Name
• Data Source Type: If source is SQL server database, select ‘Microsoft SQL Server’
Connection String: Example of connection string is
Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>
Replace the SERVERNAME tag with the database server name instance and DATABASENAME tag with the database name in the server.
The final connection string will look like
“Data Source=SUPPORTSQL2K8R2;Initial Catalog=VM470_PWA_Reporting”
3. Click ‘Test Connection’. If the connection to the data source is successful, the message ‘Connection created successfully’ appears below the ‘Test Connection’ button.
4. Click ‘Apply’ to save the data source.
5. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “MilestoneDataSet”.
Pick the Data Source created earlier.
Select “Text” Option under “Query type”
Under Query, copy and paste the following query
DECLARE @workingDate DateTime
SET @workingDate = @inputStartDate
-- create temporary table of unique days for given start date and end date
DECLARE @days TABLE (
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25)
)
-- calculate each day within number of week range
WHILE (@workingDate <= @inputEndDate)
BEGIN
INSERT INTO @days (
DayByDay,
MonthYr,
WeekMonthYr
) VALUES (
@workingDate
,convert(char(3),(@workingDate),0) + ' ' + convert(nvarchar(6), year(@workingDate))
,convert(nvarchar(3), DATEPART(wk, @workingDate)) + ' ' + convert(char(3),(@workingDate),0) + '-' + convert(nvarchar(6), year(@workingDate))
)
SET @workingDate = DateAdd(d, 1, @workingDate)
END
--Create temporary table to store ProjectUID
DECLARE @TProject TABLE(
ProjectUID uniqueidentifier
)
INSERT INTO @TProject
SELECT DISTINCT PROJECTUID
FROM dbo.MSP_EpmProject_UserView P
INNER JOIN @days D ON CONVERT(DATE,P.ProjectStartDate) <= D.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= D.DayByDay
AND P.ProjectUID in (@ProjectUId)
-- CREATE INDEX TmpDelProj ON @TProject(ProjectUID)
--select * from #TProject
-- create temporary table of unique days for given start date and end date
DECLARE @PMilestoneTmp TABLE (
ProjectUID uniqueidentifier,
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25),
MilestonesName nvarchar(max)
)
--in case there are two milestones finishing in same week
INSERT INTO @PMilestoneTmp
SELECT P.ProjectUID
, d.DayByDay
, d.[MonthYr]
, d.WeekMonthYr
,(STUFF((
SELECT ' ,' + convert(nvarchar(3), day(TaskFinishDate)) + ' ' +
+ convert(char(3), TaskFinishDate , 0) + ' ' +
+ convert(nvarchar(6), year(TaskFinishDate))
+ ' - ' + T.TaskName
FROM dbo.MSP_EpmTask_UserView T
WHERE T.ProjectUID = P.ProjectUID
AND convert(nvarchar(3), DATEPART(wk, T.TaskFinishDate)) + ' ' + convert(char(3),(T.TaskFinishDate),0) + '-' + convert(nvarchar(6), year(T.TaskFinishDate)) = d.WeekMonthYr
AND T.TaskIsMilestone = 1
ORDER BY T.TaskFinishDate ASC
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'),1,3,'')
) AS MilestonesName
FROM @TProject P
CROSS JOIN @days d
--create table for milestones ending per day per project
SELECT MP.DayByDay
, MP.MonthYr MonthYear
, 'Wk' + MP.WeekMonthYr WeekMonthYr
, MP.MilestonesName
,P.ProjectName
,P.ProjectUID
,P.ProjectFinishDate
,P.ProjectStartDate
,P.ProjectActualFinishDate
,P.ProjectBaseline0FinishDate
,(CASE WHEN CONVERT(DATE,P.ProjectStartDate) <= MP.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= MP.DayByDay THEN 1
ELSE 0 END ) ProjectInProgress
FROM @PMilestoneTmp MP
LEFT OUTER JOIN dbo.MSP_EpmProject_UserView P ON P.ProjectUID = MP.ProjectUID
ORDER BY MP.DayByDay ASC
6. Click on “Ok”.
Three parameters are created. Expand the Parameters folder to view @inputStartDate,@inputEndDate and @ProjectUId parameters
7. Right-click on @inputStartDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “Start Date”.
Update “Data type” to “Date/Time”.
8. Right-click on @inputEndDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “End Date”.
Update “Data type” to “Date/Time”.
9. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “Param_ProjectList”.
Select “Data Source”
Select “Text” under “Query type”
Copy and paste the SQL query under “Query”
SELECT
MSP_EpmProject.ProjectUID
,MSP_EpmProject.ProjectName
FROM
MSP_EpmProject
ORDER BY MSP_EpmProject.ProjectName
Click on Ok to create the dataset
10. Right-click on @ProjectUId parameter and select “Parameter Properties”.Update as follows
Click on “General” tab and check “Allow Multiple values”
Click on “Available Values” tab and select option “Get values from a query”. Pick the Dataset “Param_ProjectList” and Select “ProjectUID” in value field and “ProjectName” in Label field.
11. Add and edit the Matrix
• Right–Click on the body of the report and choose Insert>Matrix.
• Right-Click on the Matrix and select Tablix Properties.
• Select the “MilestoneDataSet” into the Dataset name.
• Select the “MilestoneDataSet” into the Dataset name and click “Ok”
• Right click on the “RowGroup” and select “Group Properties”. Select [PROJECT_UID] in “Group On:” field and update name to ProjectUID.
• Right click on the “ColumnGroup” and select “Group Properties”. Select [WeekMonthYr] in “Group On:” field and update name to WeekMonthYr.
• Right click on “WeekMonthYr” and select “Add Group” > “Parent Group”.
• Select [MonthYear] in “Group by” and click on Ok.
• Right click on the “MonthYear” and select “Group Properties”. Select ”Sorting” tab and click on “Add”. Select [DayByDay] in “Sort by” and Order “A to Z”. Click on Ok.
The Matrix will look like
• Select the second row and delete row by picking option “Delete rows only”.
• Select field [ProjectName] in first column. The header is automatically updated to “Project Name”.
• In the second column, right click and pick Text Box properties
Enter expression in Value
=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"t",nothing)
Enter [MilestonesName] into ToolTip.
Click on “Font” tab
Click on “fx” button under Font and enter
=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"Wingdings","Arial")
Click on “Fill” tab
Click on Fx Button under Fill Color and enter
=iif(Fields!ProjectInProgress.Value = 1,"LightBlue",nothing)
Click on OK.
• Decrease the width of the second column
12. Save and Run the report after selecting parameters. The diamond shape is displayed in cells where Milestone exist.
Hovering over the diamond shape displays information regarding the milestone
reshmeeauckloo 