Tag Archives: Report

SharePoint Report – Missing Managers

Trying to roll out SharePoint at an org can have it’s hard times. One is trying to get User Profile sync working well and making sure the Org Browser works well goes along with that. You can sync over the manager from AD and everything falls into place, but their might be users you are pulling that don’t have a manager set. Now of course you can query AD for this, but you would have to already know the filters and OU’s you are pulling into SharePoint. Another way to do this (and of course, disclaimer here, don’t try this at home if you are scared of querying SharePoint databases, and yes, it probably isn’t recommend, but I am doing it anyways). Here is a query you can use to get the User’s with no manager, and also join it back to get some other attributes such as department, office, and title so you can figure out where they are and who their manager might be (helpful in a larger org). You can easily throw this in an SSRS report, and have it email whomever maintains the managers in AD or in your organization. (Note, SP2010_ProfileDB might not be the name of your actual profile DB, you would have to change that in the query below)

SELECT up.RecordId,PreferredName,NTName,Email, office.Office, titles.JobTitle, dept.Department
	FROM dbo.UserProfile_Full up
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'Office'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 11) office ON up.RecordId = office.RecordId
	
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'JobTitle'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 13) titles ON up.RecordId = titles.RecordId
	
	LEFT OUTER JOIN (SELECT [RecordID]
      ,[PropertyVal] AS 'Department'
  FROM [SP2010_ProfileDB].[dbo].[UserProfileValue]
  WHERE PropertyID = 14)dept ON up.RecordId = dept.RecordId
	
	WHERE
	Manager IS NULL 
	ORDER BY Office

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.