Bringing Graphs Down to the Record Level

I was recently told that one thing that is well-liked about my blogs are the ‘codeless workflow workarounds’ I occasionally put out. I have not done one for a while so here is one I came across the other day. A client was facing a problem where they wanted to show a graph, usually used for a collection of records, on each record. A good example of this is the ‘Campaign Budget vs Actual Costs (By Fiscal)’.

Looking at the definition, we can see that this graph rolls up totals based on fields on the Campaign record.

If I wanted to quickly see how a specific campaign was going, it makes sense to have this graph on the Campaign form but this is not possible, although we can add graphs to the record form.

Adding Graphs to a Record Form

A little-known trick is being able to add a graph directly to a form. To do this you add a sub-grid and go to the formatting settings. Scrolling down to the bottom, you see how to do it.

Ticking the ‘Show Chart Only’ replaces the sub-grid with a graph and displays it on the form instead. In this case, I am replacing the ‘Recent Opportunities’ grid with a graph showing which Campaigns they came from. You can see how it looks at the bottom right below.

So all we need to do is add a sub-grid with the same record in it, replace it with our graph and we are sorted.

The Recursive Grid

First we add a 1:N between the Campaign entity and itself.

Then we set up a workflow to populate the resulting lookup with the same record, on creation. In this case I have made the workflow NOT run in the background so it appears the moment the record is saved but, for CRM 2011, the trick will still work but you will need to close and open the record to see the magic.

The result is a 1:N relationship where the child record will always be the same as the parent record.

Next we add the sub-grid to our form and create a new record to make sure it works (remember to format the sub-grid to have, say, ten lines so the graph is readable).

Sure enough, when we create a new Campaign record and save it with data, the graph appears and populates with data just from this record. In this case I set the budget to $10,000 and the Misc. Costs to $8,000.

Obviously, you can create a new graph to embed and change the title to give it more context.

Conclusions

If you have a customer looking for a ‘record dashboard’ or perhaps you want to summarise a set of fields on the form in a more graphical way, this trick may be just what you need and, as is my way, completely codeless. Enjoy ;)