Create a TimeLine View for Milestones and Project Duration using Matrix in SSRS

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

  1. The width and height of the range chart does not grow dynamically when run.
  2. 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

MilestonesTimeLineView_Report

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).

ReportBuilderReport

(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’

NewDataSource

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.

DB_Conn_Success

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”

DataSetProperties

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

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”.

inputStartDateProp

8. Right-click on @inputEndDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “End Date”.
Update “Data type” to “Date/Time”.

inputEndDateProp

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

Project_Dataset_Prop

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”

Project_Multi_Select

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.

ProjectParamProp_AvailableValues

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.

Matrix_Properties

• 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.

Group_On_Project

• Right click on the “ColumnGroup” and select “Group Properties”. Select [WeekMonthYr] in “Group On:” field and update name to WeekMonthYr.

WeekMonthYr

• Right click on “WeekMonthYr” and select “Add Group” > “Parent Group”.

WeekMonthYr_ParentGroup

• Select [MonthYear] in “Group by” and click on Ok.

MonthYear_Group

• 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.

Group_Sort_DayByDay

The Matrix will look like

Matrix_Display

• Select the second row and delete row by picking option “Delete rows only”.

DeleteGroup

• 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

TextBox_Prop

 

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

TextBox_Font_Prop

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

Fill

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

MilestonesTimeLineView

12. Save and Run the report after selecting parameters. The diamond shape is displayed in cells where Milestone exist.

MilestonesTimeLineView_Report

Hovering over the diamond shape displays information regarding the milestone

Capture

 

Advertisements

2 thoughts on “Create a TimeLine View for Milestones and Project Duration using Matrix in SSRS

  1. Is it possible to insert a Linebreak after each Milestone? So if you have a bunch of Milestones that you can read it more easily.

    Like

    • It’s quit easy to add a Linebreak in Tooltipp. Just edited the Statement as below:

      ,(STUFF( (
      SELECT ‘ ‘ + convert(nvarchar(3), day(TaskFinishDate)) + ‘ ‘ +
      + convert(char(3), TaskFinishDate , 0) + ‘ ‘ +
      + convert(nvarchar(6), year(TaskFinishDate))
      + ‘ – ‘ + T.TaskName +@NewLineChar
      FROM rpt_MS_Task T

      At the beginning of the Statement i Declare @NewLineChar as follwos:
      DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

      This works well.

      Like

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