Tag Archives: Burndown

Agile: Creating an SSRS Burndown Chart Part 3

In the previous 2 parts (see Part 1 and Part 2) of this series I showed you how to get your data ready, and how to get your report started and your Datasets and parameters where you need them. In this part, we will get the graph functional, and in the next part, we will make it pretty.

Start by adding title to your report “Agile Burndown”, then add a Line Chart to your report. Make it somewhat big, delete the Chart Title and Axis Titles,  and remove the “Details” from the Category Groups. You should have something that looks like this:

 

image_thumb15

Now to get the data on and finish it off!

Drag your values over to your Chart Data Values area like this:

image_thumb[17]

One thing we need to tweak, and this is on the PointsLeft Value. Right click on the PointsLeft series and go to “Series Properties”. To the right of the Value field, click the Fx button (for Expression Functions).

We need to change this series to not write out anything to the graph if there are no points greater than today. Why? If you don’t do this, your graph line for PointsLeft will drop off to zero for dates in your sprint after the current day, and we don’t want this. This is what the expression should be:

 

=IIF(Sum(Fields!PointsLeft.Value)=0 And Fields!Date.Value > DateTime.Now,Nothing,Sum(Fields!PointsLeft.Value))

 

Pretty cool, your graph should actually work now and function as a working burndown chart. But of course we need to pretty it up! Look for the next and final post soon.



Agile: Creating an SSRS Burndown Chart Part 2

In the previous post in this series, Agile: Creating an SSRS Burndown Chart Part 1, I explained what data you would need to prepare to create an SSRS Burndown Chart (Sprint_Dates, Stories, Story_History). In this part of the series I will explain how to get a basic burndown report in SSRS.

First, fire up Report Builder 3.0 and create a new report (if the wizard pops up, just pick “Blank Report”). You need to add a Data Source to your report. In my example, I am just using a database on my localhost called Agile, so I connect to that and create a report Data Source.

image

 

We then need to add 3 Datasets to the report. (Burndown, Sprints, and CurrentSprint), and one parameter (Sprint) and we can then format our report.

 

Sprints (this will be a dropdown of Sprints for a user to choose from)

image

CurrentSprint (this will get the current sprint based on what day we view the report, default param for the Sprint parameter we will create)

image

 

For the Burndown, do the same thing, but since the query is so large, no screenshot, just the query:

;WITH DayHistory AS 
(
SELECT 
	 bd.[DATE]
	,bd.PointsScheduled
	,bd.PointsLeft
	,bd.PointsScheduled - ((ROW_NUMBER() OVER (ORDER BY bd.[DATE]) - 1) * (CAST(bd.PointsScheduled AS DECIMAL(15,6))/10.0)) AS 'Goal'
	,ROW_NUMBER() OVER (ORDER BY bd.[DATE]) AS [DayNumber]
FROM (
	SELECT tot.Sprint,tot.LogDate AS [DATE], 
		CASE WHEN SUM(tot.PointsScheduled) = 0 THEN (SELECT SUM(Points)
		FROM dbo.Stories st 
		WHERE Sprint = 'Sprint01') ELSE SUM(tot.PointsScheduled) END AS 'PointsScheduled', 
		SUM(tot.PointsLeft) AS 'PointsLeft'
	FROM (		
			-- Get History for the Current Sprint
			SELECT Sprint,LogDate,SUM(Points) AS 'PointsScheduled', SUM(PointsLeft) AS 'PointsLeft'
			 FROM 
				 dbo.Story_History st 
				WHERE Sprint = @Sprint
			GROUP BY Sprint,LogDate
			UNION
			-- Get the Current Day		
			SELECT	Sprint AS 'Sprint',CAST(GETDATE() AS DATE) AS 'LogDate',
				SUM(Points) AS 'PointsScheduled',
				SUM(PointsLeft) AS 'PointsLeft'
				FROM dbo.Stories
				WHERE Sprint = @Sprint
			GROUP BY Sprint
			UNION
			-- Get zero's for all days in sprint to round out our dataset
			SELECT 'Sprint01' AS 'Sprint',WorkDate,0,0 
			FROM dbo.Sprint_Dates
			WHERE Sprint = @Sprint
		) tot
	GROUP BY tot.Sprint,tot.LogDate			
) bd
)
SELECT
	 a.[DATE]
	,ISNULL(b.PointsScheduled, a.PointsScheduled) AS [PointsScheduled]
	,ISNULL(b.PointsScheduled, a.[PointsLeft]) AS [PointsLeft]
	,ISNULL(b.PointsScheduled, a.[Goal]) AS [Goal]
FROM DayHistory a
	LEFT OUTER JOIN DayHistory b
		ON a.DayNumber = b.DayNumber - 1
			AND b.DayNumber = 2
ORDER BY DATE

 

This query is where all the magic happens. First, you need to get your story point values for the days, from your history, and also from the current day, you also need to get all days for that sprint with zero’s so that your graph will have all days and not just days with burndown. The CTE around the main query calculates the burndown by day so you end up with 4 columns, Date, PointsScheduled, PointsLeft, Goal

Now that you have your Datasets, we need to create a parameter, and then the graph!

Create a new parameter called “Sprint”, and set up the available values. Remember the Dataset we created to get all the sprints? Here is where you use it, like this:

image

Next, we want to setup the default values. Remember the query to get the “Current Sprint” – that is used to set our default.

image

Once you have that all setup, it is time to build the graph!

We are really close to having a working report here, and check back for part 3 of the series to get the graph working correctly, and part 4 for beautification!



Agile: Creating an SSRS Burndown Chart Part 1

The burndown chart. A must have for any ScrumMaster and Agile team. What it should show you is the rate at which you are “burning” down story points.

image

As you can see from the chart above, 3 lines. Red is your “points scheduled”, Green is the “goal” and blue is “points left”. While it is easy enough to create this chart and track the burndown manually in Excel, many teams after using Excel turn towards other systems to track their points and sprints. Right now I have one team using Unfuddle, one team using TFS, there are others that use this chart that use Footprints and really you can use whatever, and this chart can be built off of any database as long as it has the right data.

First, you need a table with your stories in it. You need to have some key columns – Sprint, Points and PointsLeft.

CREATE TABLE [dbo].[Stories](
	[Sprint] [VARCHAR](50) NULL,
	[Points] [INT] NULL,
	[PointsLeft] [INT] NULL,
	[StoryId] [INT] NOT NULL,
	[StoryText] [VARCHAR](MAX) NULL
) ON [PRIMARY]

Now you may have others, like StoryId, StoryText, Assignee, etc but we aren’t concerned about those for this chart.

You then need at least 2 or tables, and a SQL job. 1 table to hold your Sprint and Dates and one to hold your “Story History”

 

CREATE TABLE [dbo].[Sprint_Dates](
	[Sprint] [VARCHAR](50) NOT NULL,
	[WorkDate] [DATE] NOT NULL
) ON [PRIMARY]
 
CREATE TABLE [dbo].[Story_History](
	[LogDate] [DATE] NOT NULL,
	[Sprint] [VARCHAR](50) NOT NULL,
	[Points] [INT] NULL,
	[PointsLeft] [INT] NULL
) ON [PRIMARY]

 

You will need a SQL Agent Job to run at 11:55 PM to capture the history, which should run this query:

 

INSERT INTO dbo.Story_History (LogDate,Sprint,Points,PointsLeft)
SELECT CAST(GETDATE() AS DATE),Sprint,SUM(Points),SUM(PointsLeft)
FROM dbo.Stories
GROUP BY Sprint

 

Remember you might not need all 3 tables, just the history and dates. You can get your actual stories off of wherever your stories are stored in the database. Now that you have your data in place, you can get ready to write the actual report! Look for the next part in this series.



Agile: Scoring

Once you have your stories defined and you are ready to “score” them, there are some things you need to keep in mind.

What scale are you going to use for scoring?

I use 1,2,3,5,8,11, and 13. Why? Well, that is just what we used when I started doing Agile, and it seems to work :) Fibonacci + 11. I know I probably should change it so it kind of goes along with the more mainstream numbers that some 3rd party tools and other people recommend. Maybe, someday :)

1 is the lowest, 13 the highest.

At first, the team has no idea what a 1 or 2 or 8 or anything is. It takes time. Over time the team gets a gut feel for what a 1 is, or a 3, or whatever.

Don’t equate points to time! No, a 1 isn’t an hour, or a day, or X. It is a 1.

What are points?

Well, I try to explain it as time, effort, complexity, outside factorness, craziness, etc, etc. You kind of have to bundle all those things up and say, yea, that is a 2. It is more than a 1, less than a 3. Each team is going to have a different meaning for what a 3 is.

During your sprint planning, what you want to do is give everybody a pack of “planning poker” cards so they can score as you go through stories. (There are also web apps for virtual teams, iPhone apps instead of using planning poker cards). As you get done reading the story, and answering any questions, the team should “score” – show their cards. If they are all the same – great! You have a winner.

If some are different, then you discuss or come to an agreement on why some do’ers think it should be higher or lower, and get a score at the end. Example? 6 devs, 4 say it is a 2, 1 says a 3 and 1 says 1, well, most say it is 2, do the other 2 devs agree? if so, you can mark it a 2. If not, you might discuss why. The one dev saying it is a 3 might say, “what about this an that” and then everyone realizes, oh yeah, it is more than 2, and you go with a 3.

What happens if you see 13′s?

What I try to do is say – if you score it a 13, then the story is too big, you should break it up into smaller stories. With that logic, you should never have a 13 on your board.

The biggest thing to remember is that the team is going to have to take a few sprints to get used to the scoring, and understand how stories fit into points. You ARE going to over and under score – it is just reality. But don’t adjust the scores during the sprint. Things just tend to work themselves out over time. What you can do is capture actuals at your retrospective and compare to what the original scores were. The team might get a better idea on what they should be scoring things then. Another thing to try is a scoring guide. After some time, you can say “every story we do that involves making a new button on a form is usually a 2″ so then if you have a story like that, and the team scores it a 8, or they don’t know what to score it, you can look back at your guide and say -hey, we usually score these 2′s, so is a 2 ok?

Who scores?

This is a good question, who should score? My opinion? Just the do’ers. The scrum master shouldn’t score, and product owner/analysts shouldn’t score. Just the do’ers.

What do you do with the points/scores?

You track your daily burndown, and your “velocity” per sprint. More on that later.

Just remember, think of stories as being scored by points. Or bananas. Or stars. Just not hours :)