SSRS Chart for Milestones

Question

I need to create a SSRS report to present actual start date vs. planned start dates for milestones of a project (selected as input parameter) The chart should look like this: I have created the table in a separate table. However, I don’t know which chart type should I use and how do I have to set up the chart? (Chart Data, Category Groups and Series Groups).

(Data comes from SQL Server, SSRS Version 14.0.1016.285; SSDT 15.6.4)

Many Thanks in advance

Answer 1

This might look a bit long winded but it's fairly simple so stick with it :)

To start with I did not know your data structure so I've just made some assumptions. You may have to rework some of this to get it to fit but I've tried to keep it simple.

The approach will be to use a subreport to plot the dots and a main report to show the overall table. With this in mind, as we will have more than one dataset referencing our data I added some tables to my sample database before I started wit the following.

The first is a simple table containing months and years, you could a use view over a date table if you have one but this will do for now.

CREATE TABLE prjYearMonth (Year int, Month int)

INSERT INTO prjYearMonth VALUES
(2018, 8),
(2018, 9),
(2018, 10),
(2018, 11),
(2018, 12),
(2019, 1),
(2019, 2)

Next is the project milestone table

CREATE TABLE prjMileStones (msID int, msLabel varchar(50), msPlannedStart date, msActualStart date)

INSERT INTO prjMileStones VALUES
(1, 'Milestone 1', '2018-10-30', '2018-12-13'),
(2, 'Milestone 2', '2018-11-12', '2018-12-10'),
(3, 'Milestone 3', '2018-10-21', '2018-12-25'),
(4, 'Milestone 4', '2018-10-18', '2018-11-28'),
(5, 'Milestone 6', '2019-01-08', '2019-01-29')

OK, Now let's start the report...

Create a new empty report then add a dataset with the following query

SELECT 
    * 
    FROM prjYearMonth d
    LEFT JOIN prjMileStones t on (d.Year = YEAR(t.msPlannedStart) AND d.Month = Month(t.msPlannedStart))
                or (d.Year = YEAR(t.msActualStart) AND d.Month = Month(t.msActualStart))

Now add a matrix item to the report. Add a Row Group that groups on msLabel.

Next add two Column Groups. First a group that groups on Month and then add a parent group that groups on Year.

Add columns on the row group so that you end up with 4 columns msID; msLabel; msPlannedStart; msActualStart.

Finally (for now) set the Expression of the Month field (the one in the column header) to be

= Format(DATESERIAL(2017, Fields!Month.Value, 1), "MMM")

This will just give us the month name rather than the number (the 2017 is irrelevant, any year will do). Now just format as required.

You report design should look something like this..

If we run the report now we will get this..

Now to plot the dots...

For this we will create a small subreport. The subreport will accept 3 parameters. Year, Month, msID (the milestone ID from your main table). We will need the data in a slightly different structure for this sub report but the work can be done in the dataset query so nothing new is required in the database itself.

So, create a new report, let's call it _subMonthChart.

Next add a dataset with the following query..

DECLARE @t TABLE(msID int, msLabel varchar(50), PlannedOrActual varchar(1), msStartDate date)

INSERT INTO @t 
    SELECT msId, mslabel, 'P', msPlannedStart FROM prjMileStones
    UNION ALL
    SELECT msId, mslabel, 'A', msActualStart FROM prjMileStones


SELECT 
    1 AS Y, Day(msStartDate) as Day, PlannedOrActual 
    FROM prjYearMonth d
    LEFT JOIN @t t on (d.Year = YEAR(t.msStartDate) AND d.Month = Month(t.msStartDate))
    WHERE [Year] = @Year and [Month] = @Month and msID = @msID

Your report should now have 3 parameters that were automatically created, edit all three to Allow Nulls.

Note: The Y in the dataset is just some arbitrary value to help plot on the chart,. I will set the Y axis to range from 0 - 2 so 1 will sit in the middle.

Next, add a line chart with markers. Don't worry about the size for now... Set the Values as Y Set the Category Groups as Day Set the Series Groups as PlannedOrActual

Right click the horizontal Axis, choose properties and set the Axis Type to Scalar, switch off 'Always include zero' then set Min = 1, Max = 31, Interval = 1, Interval Type = Default.

Note that for data in months that don't have 31 days the plots points will not be accurate but they will be close enough for your purposes.

Right click the Vertical Axis, choose properties and set the Mn=0, Max=2, Interval = 1, Interval Type = Default

Next, right click on one of the series lines and choose properties. Set the marker to Diamond, the marker size to 8pt and the Marker Color this expression =IIF(Fields!PlannedOrActual.Value = "P", "Blue", "Green")

The report design should look something like this... (check the highlighted bits in particular)

Now let's quickly test the subreport, based on my sample data I set the parameters to 2019, 1 and 5 and get the following results....

As we can see, our two dates that fall in January for this milestone were plotted in roughly the correct positions.

Nearly there... Next right click on both Axes and turn off 'Show Axis' so we hide them. Now resize the chart to something that will fit in the main report cell. In my example I set the size to 2cm, 1.2cm and moved it top left of the report. Then set the report to be the same size as the chart (2cm,1.2cm again in my case).

Save the sub report and go back to your main report...

For the 'Data' cell where the rows and columns intersect, set the size to match the subreport size (2cm, 1.2cm) then right click the cell and insert subreport.

Right click the newly inserted subreport item and choose properties.

Choose _subMonthChart as the subreport from the dropdown.

Click the parameters tab. Add an entry for each parameter (Year/Month/msID) and set its value to be the corresponding field from the dataset.

FINALLY !!!! Set the border on the cell containing the subreport to have borders all round, just so it matches your mock-up..

Your report design should now look like this...

Now when the report runs, it will pass in the month, year and milestone ID to the subreport in each cell which in turn will plot the dates as required.

When we run the report we should finally get this...

This may need some refining but hopefully you can get this going based on this. If you have trouble I suggest you recreate this example in its entirety, get it working and then swap out the database parts to fit your current database.