Opportunity Pipeline Stage Time Stamp

I am interested in reporting how long an opportunity takes to close (number 
of days) as it moves through the sales pipeline. Ideally we would like to see 
the number of days between each pipeline phase. Then we can analyze how long 
opportunities stay at each phase. Has anyone done anything like this before?
kenny (34)
7/28/2010 4:10:32 PM
crm 35858 articles. 1 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 11

Hi Kenny,

You could do simple jscript behind a checkbox that will do a timestamp
when checked. When that phase is complete click the checkbox.

Create a nvarchar field to store the timestamp. Calling this FIELD1 in
Create a bit field and under properties change it to checkbox. Calling
this FIELD2 in code

Onload place this code

crmForm.all.FIELD1.onclick = function()
    if (crmForm.all.FIELD1.DataValue == true)

var myDate=new Date()
crmForm.all.FIELD2.disabled = true;

crmForm.all.FIELD2.DataValue = null;

if you want it to look clean you could not show the label for the
field that holds the timestamp and put this code onload:
crmForm.all.FILED1.style.backgroundColor = "eaf3ff";
crmForm.all.FIELD1.style.border = "0px";

This will make it where when you click the checkbox only the timestamp
will show, not the white text area or the box outline.

Hope this helps!

Jarrett Coleman
Dynamics Four
7/30/2010 1:46:29 AM
Yes, I've done this many times before.  The solution proposed by
Jarrett may get that one timeframe, but as you mentioned, you really
want to know how long opportunities stay in EACH portion of the
pipeline.  What you need is auditing.  (I also personally don't like
solutions such as those proposed by Jarrett that requires users to
remember to do something to make an important process work correctly,
particularly when you can add automation behind the scenes that will
accomplish the same thing, and even more, without user intervention.)

In CRM 4.0, you can accomplish the basic data capture task using
Workflow, or an add-on from c360. (There may be a simpler, more
elegant solution coming in CRM 5.0). But in any event, if you want to
get this done today, the fastest cheapest method would be to create a
workflow based auditing solution.  (Note: there is a workflow solution
somewhere up on codeplex, I believe, but that just creates one table
for auditing all entities, which isn't as nice as one for each
entity.) Try this:

1) Create a custom entity (Opportunity Audit).
2) Create an attribute in the Opportuity Audit entity for each
attribute you want to track, or audit. For most of my clients, they
have a lot of custom attributes, so I just re-create the entire
schema, so I can audit changes to any and all fields.  (To do this, I
will export the custom opportunity audit entity after creating it, and
export the opportunity customizations, and do copy and paste in the
customizations.xml to duplicate the fields from one entity to
another.  Just the attribute section is usually sufficient, and only
copy the custom attributes so you don't duplicate status, e.g..)
-- One caveat: you cannot copy a picklist from one entity to another
within workflow, so make all picklists text attributes in the audit
entity, and just move the value of the currently selected picklist to
the corresponding text field within the workflow.
3) Throw the fields onto the audit form, and publish your custom audit
4) Create a workflow against Opportunity.  Check the "Record status
changes" box.  (For a full audit, click the "Record attributes change"
box, and select all fields.)
5) Click Add Step, and 'Create Record", and select "Opportunity Audit"
as the record type to create.
6) Click on Set Properties.  Then, move from field to field, and
select the field from the opportunity that should populate the
opportunity audit record.  (Each field on the opp audit form should
have a yellow field that has the corresponding opportunity field value
appearing in each.) These fields should all be named the same, so this
is a pretty simple task.  Again, note the caveat about picklists, you
have to move picklist values to a corresponding audit text field.
(Just move the picklist field in the set properties section of this
create step to the text field...  The current value of the picklist
will move into that text field.)
7) Publish your workflow.  Now, every time you save an opportunity
record, you will create an Opportunity Audit record.
8) If you create a 1:M link from Oppy to Oppy Audit, you can see all
of the changes made to the opportunity on the left hand nav bar. You
can also grant or restrict this view to users via normal role
9) Create an SSRS report that loops through all Opportunity Audit
records (sorted by modified date within opportunity guid), and
calculates the number of days between status changes...  This is not a
trivial report to write, and is best done using a SQL stored
procedure, and then using the output of that sproc as your SSRS report
source.  (You may need to account for cases where the opportunity goes
"backward", and probably need business to make the call whether or not
that factors in to the equation.)  I typically create a permanent
table (in another DB), and run a sproc to populate that table.  I
usually add one date field and one integer field for each status.
Status1Date, DaysInStatus1, Status2Date, DaysinStatus2, etc...  Then,
it becomes a simple select from that table to create the report.

But when you have finished all this, you can create all kinds of nice
reports that show (by sales rep, product mix on the oppy, or deal
size, e.g.) how long it takes to move from the first status code to
the second, from the second to the third, etc.


David L. Carr, President
Visionary Software Consulting, Inc.
Certified Microsoft CRM 4.0 Consultant
Cell: 503-351-4207
Email: davidlcarr@earthlink.net
davidlcarr (94)
8/2/2010 9:41:35 PM

Similar Artilces:

Insert date and time into cell with existing text
Question: I maintain an issues list in Excel. One column contains notes. I'd like to somehow enter today's date (or today's date and time) into a cell with existing text. The eventual output would look like this (all in one cell) 08/20/07: some update about the issue. 08/18/07: some earlier update about the issue Look here: http://www.mcgimpsey.com/excel/timestamp.html -- Kind regards, Niek Otten Microsoft MVP - Excel "bam" <bartmacl@yahoo.com> wrote in message news:u4J%23MG26HHA.5360@TK2MSFTNGP03.phx.gbl... | Question: I maintain an issues list in Exc...

Averaging Time
Hello: I have data that looks like the following: 3:09:15 22:45:07 17:39:10 17:39:10 18:52:47 8:39:35 I would like to learn a formula that will round the time to the top and bottom of the hour using the 15 and 45 as the break point. For example 3:09:15 to 3:00. 17:39:10 to 17:30. Thanks for the hedlp. Jim =MROUND(A18,TIME(,30,)) and format the cell accordingly. If you have problems with the MROUND function, look it up in Excel help. -- David Biddulph "Jim" <Jim@discussions.microsoft.com> wrote in message news:E5FACE1B-C1BF-412B-8CB6-7AE1C24F6...

Convert number in time
Hello, I would like to convert numbers in time but I don't know how. Ex. in cell A1 I have 107.74 - and I would like to appear this in time, precisly 00:01:38. The 107.74 I got with this formula:*****=((HOUR(B9)*3600+MINUTE(B9)*60+SECOND(B9))/B6)****** The obtained value 107.74, I would like to retransform in time, which is the formula, or what I have to do? Thank you. Megadata :confused: -- Megadata ------------------------------------------------------------------------ Megadata's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14879 View this threa...

Any quick key to insert current Date and Time?
Thx Hi Philip, Use Ctrl+; for the date and Ctrl+: for the time. Cheers, JF Thx a lot! <excelmunkey@dsl.pipex.com> ???????:1137082440.501483.254530@o13g2000cwo.googlegroups.com... > Hi Philip, > > Use Ctrl+; for the date and Ctrl+: for the time. > > Cheers, > JF > ...

calc difference in times in different time zones
Is there a way to calculate (subtract) times in different time zones? eg leave baltimore @ 10:35, arrive Dallas @ 13:05 and yeild a time of 3:30 Steve <anonymous@discussions.microsoft.com> wrote: > Is there a way to calculate (subtract) times in different time zones? > eg leave baltimore @ 10:35, arrive Dallas @ 13:05 and yeild a time of > 3:30 Sure. One way is to convert the times to UTC and subtract. -- Brian Tillman ...

Opportunities and email in BCM
Hi, I am trying to find a way to link emails with opportunities. Is there a way to do this? Also, I read that there is a newsgroup dedicated to BCM, but I don't see it when I search for it through my NG reader. Also, BCM slows down Outlook. Any way to address this? Thanks! m The BCM newsgroup is microsoft.public.outlook.bcm. Do a search in Outlook Express. If you don't see it, it's possible that your ISP doesn't carry that group. You can try to just use the news server msnews.microsoft.com and see if it apears then. You can't link emails with Opportunities directly...

Removing Dialog Item at Run Time
Is it possible to remove a Dialog item from a Dialog at run time? This code does not work, but I don't know why: BOOL CMyDialog::OnInitDialog() { HWND hWndCtrl; CWnd pl_wnd; GetDlgItem(IDC_REMOVE_THIS_CHECKBOX, &hWndCtrl); pl_wnd.Attach( hWndCtrl ); pl_wnd.Detach(); pl_wnd.DestroyWindow(); return TRUE; } AVee wrote: > Is it possible to remove a Dialog item from a Dialog at run time? > This code does not work, but I don't know why: > > BOOL CMyDialog::OnInitDialog() > { > HWND hWndCtrl; > CWnd pl_wnd; > > GetDlgItem(IDC_REMOVE_THIS_CHECK...

RMS Time Clock and Departments
Is anyone aware of a third party addition to Store Operations to add departments to the cashier and also the timeclock feature? This company would find the timeclock by department very beneficial. Thanks! If your cashiers don't move around a lot, you could work around the limitation by assigning cashiers numbers based on departments? That way you could sort or filter reports based on department. for example: sporting goods cashiers 2001, 2002 housewares cashiers 3001, 3002 Then filter a report for sporting goods cashier > 2000 and <3000 Marc "Kathy" <...

time-sheet record with over 24 hours
I want ot create a time sheet for my employees where I can have the first column be the start time, and the second be the end time, with the ability to start at 10pm and finish the shift at 8am, which is over the 24 hour mark. Then have the hours totalled in the third column, with a subtotal for each week, and total for two weeks. My current dilema is where the hours go over the 24hour mark in the same day and getting the total of hours worked for that day. You might get some help from here: http://www.cpearson.com/excel/overtime.htm tj "kkwaters" wrote: > I want ot ...

closed opportunities
want to include closed opportunities in pipeline and forecast reports. any ideas? thanks ...

I am getting error after i am adding new opportunity. Is someone has any idea why I get this error ? MSCRM Application Error Report: ---------------------------------------------------------- ---------------------------------------------- Error Message: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code ...

Solved! (This time for sure!
Wait... Bullwinkle? Boris? Hmmm.... Boris, I owe you. I never in a million years would have guessed it was the icon, but that's exactly what it was. I just got an e-mail from the client. The system now works 100%. If by chance you are on facebook, please add me as a friend, or just send me an e-mail sometime - discgolfdad@cox.net And say "Hi!" to Natasha for me! I don't know Bullwinkle, and Boris is not my real name, but I'm glad it works now! Cheers!! On Sat, 17 Apr 2010 14:06:40 +0200, Boris Pauljev <nordiccoder@hotmail.com> wrote...

Coordinated Universal Time
I need to use coordinated universal time to calibrate timers at work and would like to use an excel spreadsheet to record the times. Is there a way to have excel go out on the web and download the coordinated universal time from a web site? I can not use the computer time as it is not traceable to NIST. I don't care if it has to be done via a macro. Thanks for any help you can provide. Hi David, See posts from David Peterson And Dana DeLouis (posts 10 & 12) in the following thread: http://tinyurl.com/adcqf --- Regards, Norman "David M. Alexander"...

Anomally in the real time behavior
I use MFC and VS ver. 6.0. Eveything works fine until at a certain moment I add more variables to controls in a dialog box. Any more variable will cause anomally in the real time behavior of the application (it uses real-time multimedia functions). Some more information: 1. Its a big application - the exeutable relaease is around 2 mbytes and has many resources. 2. In the debug version there are no problems. Can someone advise please? Thanks in advance Vinter What do you mean saying "I add more variables to controls in a dialog box"? And what kind of anomally does occur? > ...

Navigate back from Order to Quote to Opportunity
CRM3.0 stores emails in the History of the related entity (Opp, Quote, Order). When viewing the emails under e.g. Quote or Order xyz we often need to view the emails under the corresponding Opportunity xyz. Currently there is no possbility to jump 'back' from Quote or Order to the Opportunity. You have to go to Sales/Opportunities, type xyz in the Lookup box, click on Opportunity xyz and then on History. Navigating 'forward' from Opportunity to Quote or Order is no problem; these entities are shown in the left hand pane. Any reason why Opp and Quote cannot be shown in th...

Adding up if over/less than a time
I am really new to excel and starting to love it, but I have come stuc and would really like some guidence. I have made this sheet for work where it keeps track of all of ou hours worked and the basic idea is you put in the time started an finished and at the end of the week it will add up how many hours yo have worked and after that, if the figure add's upto more/less than th time speicfied then it will say owed/owing and the amount. I have asked on other places and nothing has caught my eye appart fro this one guy but it's causing me some problems and I don't like how h has done...

Royal Cruise Matrix Is Now In Pre-Launch, Ground Floor Opportunity!
A New Program has just Pre-Launched - Royal Cruise Matrix This is Your Opportunity to Get In at the Beginning of Something that will be BIG! Things you need to know - - Why Royal Cruise Matrix will EXPLODE in Growth - and draw the masses better than any other similar kind of program - Affiliated with Royal Caribbean Cruise Lines -> MAJOR Name Brand Affiliation = MAJOR Credibility Lowest Price Point of all the "Board" Programs - Only $100 Highest Pay Out of all of the Board-System Programs - - - - The ONLY 3-Board System - The 3rd Board Pays out $25,000.00 $10,000 Matching B...

How to delete an opportunity
Hi there After testing our new CRM we want to go live with it. While cleaning all entries and deleting all testopportunities i figured out, that some opportunities can't be deleted. It seems that these are the ones where a contract is linked to. But I neither can delete a contract nor a opportunity. Does anyone has an idea how to do that? For statistics and reports this would be helpfull. Greetings ------=_NextPart_0001_17916810 Content-Type: text/plain Content-Transfer-Encoding: 7bit <Marc@discussions.microsoft.com> wrote: > After testing our new CRM we want to go live ...

Plse help....! Incoming eMails connect to the wrong opportunity
Can somebody explain the logic behind the eMail tags, and how the system connects incoming email? We have many incoming eMails that connect to the wrong entity. Consider the following scenario I see happening here: 1) User1 creates new mail regarding Opportunity ABC and changes the sender to the queue Presales and it gets tag CRM:1234 2) A reply with tag CRM:1234 arrives in the queue Presales and is automatically connected to Opportunity ABC So far so good.... 3) User1 creates new mail regarding Opportunity XYZ and sends from her own username and it gets tag CRM:1234 (same tag as before!) ...

calculate swim times
Hi, How can I track swim event times - mm:ss.ms format? I need to go two digits deep in the MS format. This is to be used to track swimmer's times and chart progress. Thank you all for your time and help. Format the cells with Format/Cells/Number/Custom mm:ss.00 In article <46dGc.9460$Bv.1107548@twister.tampabay.rr.com>, "saladbar" <larrysalazar@yahoo.com> wrote: > Hi, > How can I track swim event times - mm:ss.ms format? I need to go two digits > deep in the MS format. This is to be used to track swimmer's times and > chart progre...

Time Problem
My Emails are appearing in my Inbox one hour later than the actual time. I have tested this by sedning myself a message from an external account, and it arrived saying it was received one hour before it was sent. Paul Smth Done that and both are set correctly..... Anyone from Microsoft got any answer as to why my Emails are showing up in advance. The Email headers look okay. Paul Smith "DL" <address@invalid> wrote in message news:eX7wHo$oIHA.3408@TK2MSFTNGP03.phx.gbl... > Ensure your PC time is set correctly, then check in Outlook, under > calendar options ...

Exception "The operation has timed out" cuming when inserting data from XML file
Hi, I am facing a problem when inserting data into database from a remote XML, i am using XMLTEXTREADER.... System.Net.WebException was caught Message="The operation has timed out." Source="System" StackTrace: at System.Net.ConnectStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Xml.XmlRegisteredNonCachedStream.Read(Byte[] buffer, Int32 offset, Int32 count) at System.Xml.XmlTextReaderImpl.ReadData() at System.Xml.XmlTextReaderImpl.ParseText(Int32& startPos, Int32& endPos, Int32& outOrChars) at System.Xm...

Help, How can I deduct time for a timesheet?
I am currently working on a timesheet and its urgent that I can sort something out today. we work on a flexi basis this lady has a contracted hours of 14:48 hours a work and she worked 12.21 hours last week but it doesn't seem to like to deduct time I think it should be -2.27 (2 hours 27 minutes) but it just comes up ############# I don't think it likes the minutes????? It works ok if she has worked over her contracted hours. Can anyone help me please. Thanks Jo, The # signs indicate that the column in which the cell with your result displays is formatted too narrowly to s...

Date and time calculations
Hello, I have a problem with a formula in wich I like to add up an amount of hours to a date/time value. Please find below an example of what I am trying to do. Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm) Value A2: 5 (hours) In cel A3 I would like to get the value of A1+A2 (result 16/03/2005 03:00) Can someone suggest what formula I must use? Thanx very much! Robert =a1+(a2/24) Format the way you like. (5 hours is 5/24ths of a day.) Robert wrote: > > Hello, > > I have a problem with a formula in wich I like to add up an amount of > hours to a date/time value. >...

Time format in code
HYCH. The code below works for what i want, BUT !! In the worksheet "Lists" i want the 1st combobox to show the values as hh:mm but am getting the display as decimal, where do i format the code below to show "hh:mm" Any help please!! Private Sub UserForm_Initialize() Dim cPart As Range Dim ws As Worksheet Set ws = Worksheets("Lists") For Each cPart In ws.Range("A1:A12") With Me.ComboBox1 .AddItem cPart.Value .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value End With Next cPart For Each cPart In ws.Range("B1:B7") With Me.Co...