How to build an Agile Inspired Project Management XRM Solution in less than a day with Microsoft Dynamics CRM

Regardless of where project management is applied, whether it is in consultancy or when managing internal projects and new initiatives, the core concept of managing project objectives from the conception of a project through to it’s conclusion is still the same. Those objectives can contain aims, those aims contain goals, the goals contain tasks and those tasks are taken ownership of by members of the project team. The key variables can vary from project to project but relatively speaking, in most projects there is an expected effort i.e. how long something is estimated to take, the actual effort spent i.e. how long something has actually taken in reality, and then throughout the project how much work is left to do at a given time (normally at time of asking or at key milestones.) Having this information available to you easily and with relatively little effort can be quite a challenge and what’s more keeping track of all this information can be costly, complicated and time consuming when all you want to be able to do is check if things are going to plan and re-align expectations if required as soon as possible.

This post centres around a small XRM Microsoft Dynamics CRM Solution which has the purpose of giving you the ability to track this information in a non-convoluted and non-problematic way that is adaptable to most styles of project management. It takes advantage of the strong reporting capabilities through Charts, Dashboards and query building using Advanced Find so that you can easily retrieve the information your looking for in next to no time. Whilst it does use some agile-esque elements, it is certainly not something that needs to be central to your business for you to use this solution and reap the benefits.

This post describes how to build an effective, easy to use, and more importantly, simple, Project Management XRM Solution in less than 7 hours using the resources I have put together for you. (Naturally, design time, customisation and configuration, documentation and writing the code would take you longer, as well as extending it to include more than one plugin, if required)

Scroll right to the bottom of this post and download all the documentation including XRMPM Component Document, XML and txt supporting files. Copy the Use Cases in the post into a separate word document.
Use the Videos hyperlinked into the blog next to certain sections to get better insight into how to do something
First check out how to use the solution using the documentation and the sections below “What is the purpose of this solution’, ‘How to use the solution’  and watch the introductory video at a minimum.
Then build the solution using the section ‘How to build the solution’ making any changes as you want to improve it and make it work for you if required.

The solution includes a plugin which is less than 100 lines of custom code which is featured as part of the supporting files, so if you want to build everything else but not the plugin, your free to use the code from this post. It uses late bound C#. The remaining functionality uses all out of the box business logic including roll-up fields, calculated fields, views, dashboards and charts (with some minor XML modifications).
What is the purpose of the solution and how does this solution meet that purpose?

The purpose of the solution is to provide simple, powerful and easy to use functionality that enables a project manager to keep track of outstanding effort on projects, make decisions quicker (not just at the deliverable stages) and have good visibility of numerous and sometimes parallel workloads in projects.

Lets break down this purpose and discuss how the solution aims to meet that purpose:

Keep track of outstanding effort

This can be done using Project Level Statistics – ‘Total Remaining Sprint Effort’ keeps track of all Sprints that are ‘In Progress’ and gives you an overview of the actual remaining effort required.
This can be done using Sprint Level Statistics – Comparing “Remaining Ideal Effort” with “Total Actual Remaining Effort” to see if these are in line with eachother, check the “Total Effort Currently Spent” to also compare against the “Total Capacity” of the project. If “Remaining Ideal Effort” goes into negative numbers, this indicates project overrun.

Make decisions quicker

Keep track of Potential Overrun with Sprint Level Statistics and know when things have overrun as soon as it happens
Use the Burn-down Chart of each Sprint to look at the ‘Ideal’ vs ‘Actual’s of the day breakdown of the Sprint and make decisions based on what you see immediately e.g. spikes of effort can be a warning sign of potential overrun, also steep drops could indicate something is not quite right, have your team been stretched to meet the commitment, has the work been consistent?

Have good visibility of projects 

This can be done using Project Level Statistics – ‘Total Sprint Ideal Effort’ and ‘Total Effort Used’ can be used in comparison to see if all associated project sprints are meeting the ideal effort instantly – this only takes into account completed sprints.

Why use Dynamics CRM?
You don’t have to use dynamics, and I’m not disputing the capabilities of some very effective purpose built project management software, however Dynamics CRM has some fantastic core functionality and some of these include reporting using charts, views and advanced find which are all ridiculously easy to use and maintain, and so by utilising the extensibility of CRM, it can be turned into an effective addition to whatever else you wish to use CRM for in your business.
Functionality used in building the solution
Out of the box functionality has been used as much as possible when building this solution. I do not claim this solution is perfect, it most certainly isn’t, however it can be used ‘as is’ without any modifications. I encourage you to make it better, enhanced it, rip it apart if you want to, hopefully it will inspire you and help you in some way. (I’ve added a bullet pointed list of ‘Ways to extend the solution’ near the bottom of this post). Whilst I have used out of the box functionality, including a fair number of calculated and rollup fields, you may see in the implementation some of the disadvantages of these (not instant, scheduled). There was some core functionality I was unable to implement out of the box, which was the creation of an unknown number of records for a Sprint (Day Breakdown). This was because by kicking off child workflows, it would create what CRM considers an infinite loop as there is a safety mechanism to prevent more than 8 cycles. However, it is less than 100 lines of non-complex custom code and 1 plugin, 1 step. You may use the entire code (changing the schema names if required) if you wish in your implementation featured here as the code is included in the supporting files.
How to use the solution
Before you build the solution, I recommend you check the section at the bottom called ‘Files’ and download the XRMPM Component breakdown as it includes screenshots etc out so you can see how it works, if you like it, if it suits your purpose basically. You may want to change parts of it and knowing that before you build it will help save you a lot of time.
There are some keywords (used in fields) and terminology used in the solution and these are defined below to avoid misunderstanding:

Effort Per Day – This is the amount of effort, normally chargeable, that 1 member of the team is due to spend each Day of the sprint.
No of Days – This is the number of days that the team are working on the Sprint – this does not have to correlate to the Date To and From – (it can) – however it does need to be applicable to all members of your team i.e. working on the same day.
Total Capacity – This is the full total amount of effort that the sprint is for and is calculated by No of Days * (Team Size * Effort Per Day) and marks the ‘Total Ideal Effort’
Burndown Rate – This is the rate of effort that is expected to take place each day and is calculated by Team Size * Effort Per Day. This is why your team must be working the same amount of time on each day. Note: There is a use case described below if you have a scenario of 2 or more people not working on the same day or for the same amount of time in a sprint. 
Burndown Chart – This is a Chart that documents the Ideal Effort burndown which is a slope basically of the ideal effort required going down as each day passes and the idea is if the people are doing the expected burndown rate workload, the work was correctly estimated and no hiccups happen, the secondary line would document the exact match to the ‘ideal’.
Ideal Effort – The amount of effort that is projected – this can be seen as an expectation.
Actual Remaining Effort – Actual Remaining effort needs to be recalculated at the end of each day and therefore Actual and Ideal are not always in sync with eachother – and this is where a unique burndown chart is normally drawn. Actual Effort is the effort that in reality, the amount of effort left to complete a task.
Effort Spent – How much effort has been spent on the task in that day – this is different to the actual remaining effort in so many ways and should not be confused. It technically should match the burndown rate per person e.g. burndown rate of 14 for 2 people, so that is 7 hours should be the effort spent.
Sprint – A sprint is a term used to document a section of work, an iteration, this could be one piece of work, or a specific piece of functionality for example – this is up to you.
Date To and From – Date to and from are only used for reporting and visibility in the solution – you could technically have a Sprint of 5 chargeable days over an entire month if you wish.

Core Use Cases
This solution has been built for two core use cases. One for a more agile implementation where you have a Project Team, you have a set amount of days, amount of effort and that team is working on nothing else but that workload. The second is where you can use it for grouped sections of work e.g. 1 person, 5 chargeable days in a month at 7 hours a day and also that person has 10 chargeable days at 3.5 hours a day on a different sprint – the important thing to note is the solution doesn’t manage the person’s workload i.e. assign tasks, or have any knowledge as to ‘who’ is doing the work, that is not it’s purpose.

(“”) indicate the data entered in the use-case examples, I’ve talked too much and added descriptions in these use cases for extra context to assist.

Introductory Video
Use Cases Core (1 & 2)
Use Cases 3 & 4

Use case #1 – Agile Implementation of a Project – Document XYZ – Creating a Project & Associated Sprint

Navigate to ‘Sales’ and then to ‘Projects’
Click ‘New’
Enter the Name of the project (“CRMCAT”, associated Account and Contact (“crmcatcorp” “Sarah Critchley”), Description is necessary (“Cats”) and Save the project.
To gain access to the rollup fields, you need to exit the Project and reopen it again (or refresh) (This step is not required to progress)
Click the ‘…’ on the ribbon bar and select ‘Run Dialog’
Select ‘Create Sprint’ in the ‘Look up Record’ box and click ‘Add’
The Create Sprint Dialog box will open – Following the prompts, enter the No of working days allocated for the Sprint (“20”) the amount of effort per person per day (“7”) – so if your working day is normally 7 hours, this would be 7 and the Team size, for example 5 people are in the project team all working 7 hours for the 20 days. (“5”).
Enter the name of the Sprint and the Dates when the Sprint would start and end  (“Sprint 1”), (“01/09/2015”, “28/09/2015”)
Once all boxes are completed, click ‘Next’ and wait
The Screen should change to the end of the Dialog where you click ‘Finish’
Click on the associated dropdown arrow next to your Project Name on the navigation bar and Select ‘Sprints’
Click the ‘Refresh’ icon to display the newly created Sprint
Select the name of the Sprint to open the record
Select the manual refresh on the field ‘No of Active Days Remaining’ if it contains 0.00
<This is the end of the use case for creating a Project & an associated Sprint which has not yet started, continue to Use Case 3 & 4 for further steps)

You may click around at this point and investigate – You’ll see the number of days have been created for what you entered plus an additional ‘Day 0’ this is your starting Day and is not a real day – it is so your burndown chart has a starting point. How to use Day 0 is detailed in UseCase #3 Starting a Sprint.

Use case #2 – Any Implementation of a Project – Document XYZ – Creating a Project & Associated Sprint

Navigate to ‘Sales’ and then to ‘Projects’
Click ‘New’
Enter the Name of the project (“Cat World Domination”) associated Account and Contact (“CRMCAT”, “Sarah Critchley”) Description is necessary “(<Enter Plan>”)and Save the project.
To gain access to the rollup fields, you need to exit the Project and reopen it again (or refresh) (This step is not required to progress)
Click the ‘…’ on the ribbon bar and select ‘Run Dialog’
Select ‘Create Sprint’ in the ‘Look up Record’ box and click ‘Add’
The Create Sprint Dialog box will open – Following the prompts, enter the No of working days allocated for the Sprint (“5”), The amount of effort a day (“3.5”), The Team Size  (“1”)
Note: The is the number of days the user plans to work on the project, so this could be 5 full days, 4 half days, 3 3/4 days, it doesn’t matter. The amount of real days being spent on the project doesn’t have to correlate with the number of chargeable hours as these can be split in whatever fraction over those days e.g. 1 hour. The amount of effort a day – as discussed earlier, this is what has been agreed so if the person working on the project is working on 2 sprints the same day, this could be 3.5 hours to accommodate this. The Team size – this must be the number of people who are committed to working the same hours – and also working the same number of days.  (2 people can’t work different hours or different number of days, if they do, you would need to create separate sprints for them and do the team size as 1 and then enter their specific information). 
Enter the name of the Sprint (“Main Sprint”) and the Dates when the Sprint would start and end (“14/08/2015”, “15/09/2015″).
Once all boxes are completed, click ‘Next’ and wait
The Screen should change to the end of the Dialog where you click ‘Finish’
Click on the associated drop-down arrow next to your Project Name on the navigation bar and Select ‘Sprints’
Click the ‘Refresh’ icon to display the newly created Sprint
Select the name of the Sprint to open the record
Select the manual refresh on the field ‘No of Active Days Remaining’ if it contains 0.00
<This is the end of the use case for creating a Project & an associated Sprint which has not yet started, continue to Use Case 3 & 4 for further steps)

Additional Use Cases
Use Case 3# Starting a Sprint

Open a Sprint
Click the manual rollup refresh for ‘No of Active Days Remaining’
Click ‘Sprint Started’ so it says ‘Yes’
‘Status Reason’ should automatically change to ‘In Progress’
Save the record
Open Day 0 by double clicking on the record in the subgrid
Enter the ‘Effort Spent’ as 0.00
Status Reason should automatically change to ‘Day Completed’
Save the record manually or exit back to the Sprint record by selecting the Sprint lookup hyperlink
<The Sprint is now considered as started and in progress, continue to Use Case 4 on how to Manage the sprint effort>

Use Case 4# Managing Sprints (All Implementation Methods)

To manage a day of a sprint, you need to know: How much effort is remaining on the Sprint (It gets re-addressed every Day of the Sprint), and how much effort has been spent on that day. Those are the only two pieces of information you need per Day of the Sprint. 

Open a sprint
Navigate to the appropriate Day record and opening the record (if following from Use Case 3, this would be Day 1)
Enter how much the individual completing the task believes is remaining in the ‘Actual Effort Remaining’ – This is automatically populated with the same as ‘Ideal’ and is overwritten if different
Enter the amount of effort actually spent – this should be the same as the burndown rate which is the target amount of effort spent per day per team size (so divide burndown by team size for per person effort)
The Status reason should automatically change to ‘Day Completed’
Navigate back to the Sprint record by selecting the Sprint lookup hyperlink (Autosave executes)
Click the dropdown arrow on the navigation next to the Sprint name and select ‘Days’
Click ‘Chart Pane’ and select ‘Right’
Ensure Burndown Chart is selected as the Chart
Analysis the ideal vs actual remaining effort on the Sprint using the blue (ideal) vs red (actual) lines and how far you are in the Sprint.

It’s worth noting if you have implemented a non-agile approach where your 5 Day Sprint could be split over 1 month – you don’t have to update the Days every day obviously and only update when they are scheduled to be completed.
Build the solution
I’ve written specification documents including component breakdowns.I recommend you review this before going any further to gain full context and information of the steps included in this section. You will need the document titled ‘XRMPM Component Breakdown’ available from dropbox here, to complete this section.

Create a new solution for your work.

Plugin Overview Video
Creating your Burndown chart

Setup your entities and fields
This solution uses 3 custom entities:

Project – The project is an individual Project this could be something that is a year long project for example, or something that lasts for 1 month and has 1 sprint. It is a ‘container’ for possibly multiple iterations of work, known as sprints in this solution/
Sprint – One iteration of work which may include a team or an individual person and is linked to one project. The sprints have associated Days with them depending on the No of Days specified at creation.
Day – a Day is a specific Day of work, the days do not have to be in literal order e.g. Monday, Tuesday, Wednesday and could be all the Monday’s in a month for example.

Using the documentation provided, I suggest creating the entities first, creating the fields only, leaving the rollup & calculated business logic for the next section.
Adding in field business logic
Now add in your field business logic for your rollup and calculated fields, there are 6 in total across the 3 entities. To add this business logic, navigate to the field and select ‘Edit’ next to the ‘Type’. This business logic is in the right hand side of the table in the documentation.
Create your Processes: Dialog, Workflows

The ‘Create Sprint’ dialog is core functionality and I suggest creating this first. It is very simple & all you need to do is follow the steps below:

Navigate to your solution
Select Processes
Select New
Select Dialog and name your dialog, selecting the Project Entity as the Primary Entity
Create 1 Page which has 7 ‘Prompt and Responses’ relating to the No of Days, Effort per Days, Team Size, Sprint Name, Date From and Date To
For each Prompt and Response, enter a Statement Label that is descriptive (e.g. field name), Prompt Text which is the field name on the dialog itself and any Tip Text to include which appears on the right hand side of the dialog
In Response Details, select the Response Type which is Single Line for all but the Date fields which are ‘Date Only’, Always Log Response.
After Page and the Prompt and Response sections, add a Check Condition (If No of Days in Response Text is > 0)
Add a Create Entity (Sprint) and associate all the response texts from the local values in the workflow
Add an Update Entity (Sprint) and add the now calculated values from the Create Sprint local value in the workflow for the fields ‘Remaining Ideal Effort’ and ‘Total Actual Effort Remaining’ as we need these to not be blank when the Sprint is first created, but as they are based on Total Capacity, which is a calculated field, this cannot be done at ‘Create’

There are then two supporting real-time workflows:

Update Actual and Ideal Effort Remaining

This functionality uses a real-time workflow on the Day entity to update the Actual and Ideal efforts from the Day record that has been modified last to the parent Sprint, so the two fields on the Sprint (Ideal effort remaining and Actual Effort Remaining) should always be based on the latest Day record updated.

It triggers when the Status Reason changes on the Day record – and updates the parent Sprint from the Day record with the ideal effort value and the actual remaining effort value. It overwrites whatever was in their previously.

Rollup to Parent Project

This functionality transfers some key figures over to the Project when the Sprint is completed. It triggers from the Sprint entity when ‘All Days Completed’ is set to Yes. It then updates the parent project by incrementing the field ‘Total Sprint Ideal Effort’ and ‘Total Effort Used’ fields by the values on the Sprint record of Total Capacity (Ideal Effort) and ‘Total Effort Spent” respectively. This then will give an incrementing value regardless of the number of sprints per project so can always be used as a way of tracking the ideal vs actual per project and it’s associated Sprints.
Create your Plugin [Video]
The plugin uses less than 100 lines of codes however the full code can be found in the txt file called ‘CreateDaysAutomation.txt’  and then paste the meat of it into your empty plugin solution.

Open Visual Studio, using the developers toolkit create a new dynamics project and then after connecting to an organisation, right click the entity on the CRM Explorer and select ‘Create Plugin’ – This plugin is on the Create of a Sprint and is on Post Operation.

Check out the video to watch this in action and I briefly explain what is going on in the code.
Create your Burndown Chart [Video]
The Burndown chart is fairly simple to make, the video explains it better, but if you don’t want to watch it download the xml from dropbox and follow the bullet pointed list:

Create a chart with two series (Ideal Effort, Actual Effort) on the Day entity – Both are line charts and both should be ‘Sum’.
Your Category should be ‘Day Number’
Export your Chart XML by saving your chart then clicking ‘…’ > Export Chart in the Chart Designer
Open your XML document
Now add in the Colour “Color=”205, 92, 92″ into the second series, remove the secondary axis label at the right hand side
Change the marker size in the Series to 5
Change the show as label to ‘False’ in both series to remove the label annotation from the line to make it clearer.
Add the annotation at the bottom after </Legends>:

<TextAnnotation X=”50″ Y=”0″ Text=”Burndown Chart: Ideal vs Actual Progress” TextStyle=”Default” Font=”Verdana, 13px” ForeColor=”59, 59, 59″ ToolTip=”This burndown chart documents the expected ideal effort vs the actual effort documented on the Day breakdown per Sprint” />

Massive thanks to CRM Chart Guy’s blog which is absolutely amazing, check it out here. 
Do your housekeeping: Views, Dashboard, Additional Charts, Icons
The remaining tasks left to do in your new solution are more housekeeping activities now. Add icons to make it look more professional, customise your views using the documentation provided in the Component list under ‘Views’ for each entity – there are table breakdowns detailing the query and columnset.

The additional Chart is available in the document ‘Total Capacity vs Total Effort Spend.xml’ and is on the Sprint entity. This is a very simple chart of two series (Total Capacity) and (Total Effort Spent (Hours)) both Sum and both bar Chart. The Category is Sprint, and the customisations include the same red colour used in the burndown chart on the second series, legend angle customisation addition on Axis X (LabelAutoFitStyle=”LabelsAngleStep30″) placed just after TitleForeColor and removal of the secondary Y axis so they only use one. This chart looks at the ideal effort vs actual effort on a sprint and can be used to easily identify over capacity.

Now is the best time to create your dashboards, you can put them together how you wish – I have included my setup in the Component list so feel free to use that, or use your own. (Screenshots below).

Feel free to then change anything you want to in the solution as now would be a good time to do so if you wanted to build or tweak it if you have not yet done so and wanted to change anything. (You don’t have to).
Unit Testing
I fully recommend unit testing your new application – when I initially created this application I found a few nuances in it where I had to then create a few business rules and tweak some of the fields. Also you may find that the roll-up fields non-instant aggregation is irritating so you may wish to replace these with something like small plugins as these can affect views sometimes if they are not up to date and you want literally, instant information.

I recommend to unit test follow the four use case descriptions above.
Ways to expand the solution
There are various ways to expand on this solution and ones that I may look at in the future are:

Statuses/Status reasons
Dashboards, Views
Kanban board using Sparkle XRM
Editable Grid using Sparkle XRM


TotalCapacity vs Total Effort Spent.xml
XRMPM Component Breakdown.doc
Managed Zip File (Contact me)
Unmanaged Zip File (Contact me)

I hope this has been helpful and of course, if you have any questions please just let me know 🙂

You Might Also Like

Leave a Reply