Post

2 followers Follow
0
Avatar

Report Showing Projects Without Milestones

How do I create a report that shows a list of projects that don't have any milestones?

Gina Cargile Answered

Please sign in to leave a comment.

8 comments

0
Avatar

I currently have it listing the projects with a count beside them, I'm just not sure how to say - where the Milestone count is > 0, or if there is a milestone count at all.

Project(1)

Milestone(2)

 

Here is my setup:

Report on:  Work Item

Include related:  Project

Filter:

Workflow->Item Type  -  is one of -  Milestone or Project

Columns

Work Item->ProjectName

Work Item->ItemType

Group By

Work Item->ProjectName

Work Item->ItemType

Show Summary - Checked

Gina Cargile 0 votes
Comment actions Permalink
0
Avatar

First you will need a field to count milestones and then you can easily filter on that field.  You can do a roll-up summary that will only count if Work Item Type = Milestone.

Josh Santos 0 votes
Comment actions Permalink
0
Avatar

I think I got it working - I created a custom field at the "Work Item" level called MilestoneCount and put the following for Default Value [Based on Formula]: 

IF($EntityType = 'Milestone',1,0)

Then checked "This field is rolled up into parent work items"  Rollup Type:[Sum]

Set Rollup criteria:

$EntityType='Milestone'

 

 

Thank you!

Gina Cargile 0 votes
Comment actions Permalink
0
Avatar

Never mind, it doesn't seem to be working.  Do you see anything I did wrong?  The report is still showing projects that have milestones in the list.

Filter:

Work Item.MilestoneCount <= 0

The milestonecount shows 0... but shouldn't be on several listed.

Gina Cargile 0 votes
Comment actions Permalink
0
Avatar

Can you provide a screenshot of the roll-up summary field settings?

Josh Santos 0 votes
Comment actions Permalink
0
Avatar

Ah, the problem is that you set default value instead of making it a formulaic field. The default value is not retroactive and will only apply to new work items. I would clear the default value and make it a formulaic field up at the top.

Josh Santos 0 votes
Comment actions Permalink
0
Avatar

Ok- I changed it, but it is still pulling the ones with milestones in:  (Does it know to calculate it without modifying the projects?)



Gina Cargile 0 votes
Comment actions Permalink