Customizing the Opportunity Pipeline Summary Information

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Customizing the Opportunity Pipeline Summary Information

  • Comments 6
  • Likes
Can we customise the "Statistics for All stages" that appear next to the pipeline in My CRM / Opportunities?

The pipeline graphic and its associated statistics are driven from within the dll and have no definition within meta data. This means that we have no obvious way of changing the data.

The pipeline is produced by using the PipeLineGraphicBlock and this has the property pipe_summary which allows the contents of the summary area to be defined. If this had been coded in an ASP page then the usage would be:

myPipeline=eWare.GetBlock('pipeline');
myPipeline.Pipe_Summary='<TABLE><TD CLASS=TABLEHEAD>Negotiating Selected (70)</TD></TABLE>';

But obviously in this case we are not able to access the pipe_summary property of the pipeline graphic as it is driven from the dll and not exposed to us.

We can however see that the current text in the pipeline summary area of the Opportunity Pipeline is translatable.

The text values of 'No. Oppos' and 'Forecast' can all be found in the custom_captions table and therefore changed in the Administration -> Customization -> Translations area.

Caption Family, Code, US Translation
ColNames, qry_avgcertainty, Average Certainty
ColNames, qry_avgdealvalue, Average Value
ColNames, qry_avgweighted, Weighted Average
ColNames, qry_Count, No. Oppos
ColNames, qry_sumforecast, Forecast
ColNames, qry_weightedForecast, Weighted Fcst

Also an examination of the SQL used to create the screen:

SELECT TOP 20 COUNT(*) AS Qry_Count, AVG(Capt_Order) AS Qry_Order, SUM((Oppo_Forecast/Curr_Rate) * 1.000000) AS Qry_SumForeCast, SUM(((Oppo_Forecast/Curr_Rate) * 1.000000)*Oppo_Certainty)/100 as Qry_WeightedForeCast, SUM(Oppo_Certainty) AS Qry_SumCertainty, Oppo_Stage AS Qry_Stage FROM vListOpportunity LEFT JOIN Custom_Captions ON Oppo_Stage = Capt_Code LEFT JOIN Currency ON Oppo_Forecast_CID = Curr_CurrencyID WHERE ((comp_secterr is null OR (comp_ChannelId=1) OR (Comp_PrimaryUserId=4) OR (comp_CreatedBy=4) OR (comp_secterr>=-2147483639 AND comp_secterr<=-1879048185) OR (comp_secterr>=-1610612729 AND comp_secterr<=-1342177275)) AND (oppo_secterr is null OR (oppo_secterr>;=-1610612729 AND oppo_secterr<=-1342177275) OR (oppo_secterr>=-2147483639 AND oppo_secterr<=-1879048185) OR (oppo_AssignedUserId=4) OR (oppo_ChannelId=1) OR (oppo_CreatedBy=4)) AND (pers_secterr is null OR (pers_CreatedBy=4) OR (Pers_PrimaryUserId=4) OR (pers_secterr>=-2147483639 AND pers_secterr<=-1879048185) OR (pers_ChannelId=1) OR (pers_secterr>=-1610612729 AND pers_secterr<=-1342177275))) AND ( Oppo_AssignedUserId=4 AND oppo_status = N'In Progress' and (RTRIM(UPPER(Capt_Family)) = RTRIM(UPPER('Oppo_Stage')) OR Capt_Family IS NULL)
)GROUP BY Oppo_Stage ORDER BY 2 DESC

Shows where the values of the existing contents of the summary area originates.

Another useful fact is that the Summary Area data values are actually treated like any other field data when the values are written out to the browser. This means that the field captions and the field data are wrapped in spans that have ids given to them. If you examine the HTML of the page you will see the summary fields have each section clearly identified:

e.g.

<SPAN class=VIEWBOXCAPTION id = _CaptQry_Count>#text</SPAN>
<SPAN class=VIEWBOX id= _DataQry_Count>#text</SPAN>

This means that these area can be very easily addressed using client side code.

For example code like this could be placed into the custom content of the Opportunity List:

<SCRIPT>
crm.ready(function () {
var strSpan = '';
var strLink = '';
strSpan = _DataQry_Count.innerHTML;
strLink = 'This is new:';
strLink += strSpan;
_DataQry_Count.innerHTML = strLink;
})
</SCRIPT>

Although I started the article by saying that we have no obvious way of changing the data we have seen that the easily changed translations for the fields in the summary area and the easily addressable id's of the same fields mean that it would be quite achievable to change the summary area of the Opportunity PipeLineGraphic.
Comments
  • Is there a way to manipulate that SQL query that runs which calculates the forecast.  Instead of doing a sum on the oppo_forecast field I want to do it on another field.  Please let me know how to go about changing that SQL query or is that locked in the black box?

  • Matt

    I think the SQL is all generated from the dll and can not be changed.

  • This works in SageCRM 7.2 with IE10 but not with IE11 and Firefox.

    Any idea  of I have to change in order to use it on IE11?

  • The original article only was using IE specific attachEvent() method and was not cross browser compatible.  But you can now use in Sage CRM 7.2 the new API to make cross browser code very easy.

  • If I wanted to return different data to the totals, how would I do that? I would need to get either the set of data that has gone into the creation of the list, or what the exact filter is from the filterbox serverside, create my totals serverside using a filter fields createscript, pass those into a hidden div, and then change the pipeline clientside? The part of that I'm struggling with is finding the where clause to the list... any ideas?

  • MagicalArmchair

    I am not sure about what you want... but using clientside code you could read the contents of the filter box, then use that value to fetch with clientside code the data you want (here you could use the crm.sdata() method) and then change the data displayed in the pipeline summary.