Displaying a Funnel Chart for Cases

Displaying a Funnel Chart for Cases

We had a customer who recently wanted to implement their application process in Dynamics CRM Online 2015 so that they could track and manage applications from initial enquiry through to approval/closure.  We utilized Cases to capture application information and the Business Process Bar to manage the stages of the application.
Once we had this in place, the customer quite reasonably wanted to see this information in a funnel chart similar to the Opportunity Sales Pipeline chart.  In this way, they could view and understand how many applications they had in each stage and see the ones that were about to close vs. those just starting the process. We thought this would be a great example to share of how our technical team help solve everyday business issues for our customers

 

So our first thought was to simply add our Business Process Stages to the Stage Category Global Option Set and link our stages in the Business Process to those values and everything would just work.

display-funnel-2

 

Although this all just works with the Opportunity entity, it doesn’t just work for Cases (or any other entity).  The Opportunity entity has some additional (hidden) functionality in the background that sets the Pipeline Phase field automatically with the Stage Category (it actually concatenates the id of the option set value with a space and the process stage name). This means that Opportunity records are automatically updated with the correct stage name as they move through a business process – all without us having to do anything more than enter the stage in the Business Process Flow.

But wait – the Case entity has its own Case Stage (incidentstagecode) field – maybe that’s the key!  Apparently not, because adding values to this field doesn’t auto-magically do anything.  It isn’t updated based on the Business Process Flow changing and it isn’t avaialble from the Business Process Flow user interface.  It appears to simply be an option set that is incuded with Cases in the same way as the Case Type (casetypecode) and nothing more. The Case entity has its own Service Stage (casestage) field – maybe that’s the key!  Apparently not, because you can’t even edit it!  It appears to be a field from a past verison of CRM that is now depreciated.

So the only way we’re going to get the functionality we want for our Case entity is to implement it ourselves.
First we need a field to hold the text of the stage that we can use in our Views and Dashboards.  In our project we created a new field on the Case called Service State (zt_ServiceStage).  We could have used that Case Stage field mentioned above, but we opted for a custom field.
We then created a real-time Workflow that would populate zt_ServiceStage with the text from our original Stage Category Global Option set

We needed a field that would trigger the workflow to update the Application – that would be the Process Stage field.  This is the unique GUID of the current stage of the Business Process Flow that a record is on and therefore changes whenever the Case is moved from one stage to the next

Lastly, we needed the information to populate our new zt_ServiceStage field with – that would be the Stage Category from the related Process Stage.

And that was it – very simple in the end – just a long road to get there.

The Zero2Ten Delivery and Technical team are helping customers solve daily business issues using Dynamics CRM.


Microsoft Dynamics CRM Blog