Adding items to the Opportunity Snapshot (LAN)

Jason Buss — Wed, 29 Apr 2009 12:51:00 GMT

The opportunity snapshot on the Opportunity Detail view is a handy place to display summary information, but customizing it can be a bit confusing. The Snapshot is simply a browser control, displaying HTML which is being generated by a method on the Opportunity detail screen. You can see the HTML code in that method (LoadSnapShot), but it is so broken up and pieced back togther to add variable data, that it can be a little tough to navigate. Hopefully this basic rundown helps explain how the snapshot is put together, and how simple it can be to add new information.

First, let's take a look at the conditions involved in generating the snapshot (Opportunity Detail view, LoadSnapshot method). Primarily, the Opportunity status is checked to determine which version of the snapshot is to be displayed. I've cleared out the html in this code, so we can see how things are setup based on status.

The first thing checked is to see if the current status is either Open or Inactive (I've cleared out localization code as well to make this more readable):

If strStatus = "Open" Or pklStatus.Text = "Inactive" Then

In this area, they set up the header information for the HTML
    If Not blnMCEnabled Then
Here they display pricing information, depending if Multi Currency is enabled or not.
    Else
    End If

Finally, they show summary information for the opportunity which is not reliant on currency. This is usually fields like Days Open, Current Stage, etc... This is repeated for both Closed statuses as well as a catch all for any other status value.

ElseIf strStatus = "Closed - Won" Then
'Header Info
    If Not blnMCEnabled Then
'MultiCurrentcy enabled
    Else
'MultiCurrentcy disabled
    End If
'Summary Information
ElseIf strStatus = "Closed - Lost" Then
    If Not blnMCEnabled Then
    Else
    End If
Else 'The code below is a "default" if the user addes a picklist value that doesn't equal the above conditions out of box.
    If Not blnMCEnabled Then
    Else
    End If
End If

That's all there is to the code in terms of WHERE you want to edit, but there is a lot of broken up HTML to navigate at that point; However, If you search for label values in the Snapshot code, and do a little copying/pasting, it's not do difficult to add some additional information to this area.

I'm going to add a "Total Margin" field in the Value section of the snapshot. Total Margin will be defined as (TotalSales - TotalFixedCost) / TotalSales. We'll want to calculate margin based off of both SalesPotential for Open Opportunities, and ActualAmount for Closed Opportunities.

First of all, we need to get the total cost value. I'm going to add this code near the top of the LoadSnapShot method on Opportunity Detail. This will give me a variable name dTotalCost containing the total cost value for all products on the current opportunity:

Dim dTotalCost

    Set objCostRS = objSLXDB.GetNewRecordSet
    strSql = "select sum(p.fixedcost) as totalcost from opportunity_product o inner join product p on o.productid = p.productid" & _
             "Where o.opportunityid = '" & strCrntOppID & "'")
    objCostRS.Open strSql, objSLXDB.Connection

    If objCostRS.RecordCount <> 0 Then
        dTotalCost = objCostRS.Fields("totalcost")
    Else
        dTotalCost = 0
    End If

objCostRS.Close
Set objCostRS = Nothing

Now that we have cost, we have to calculate the margin. I do this in two places actually. First right outside the first If statement that checks the opp status, and I also set it inside the If statement if status is "Open". I do this because margin will be based off of the Actual Close Amount if the Opp has any closed status, and off of Sales Potential if the status is Open or Inactive.

(My added code is in bold, you will probably find this right around line 370 on the Opp detail form code)

            If IsNull(objRS.Fields("WSALE").Value) Then 'DNL
               lngWeighted = 0
            Else
               lngWeighted = objRS.Fields("WSALE").Value 'DNL
            End If

            If cDbl(intActualAmount) = 0 Then
                dMargin = 0
            Else
                dMargin = (cDbl(intActualAmount) - cDbl(dTotalCost)) / cDbl(intActualAmount)
            End If

            Application.BasicFunctions.GlobalInfoSet "CurrentStatus", strStatus 'DNL
            If Application.Translator.Localize(strStatus) = Application.Translator.Localize("Open") Or Application.Translator.Localize(pklStatus.Text) = Application.Translator.Localize("Inactive") Then 'DNL

               If lngSalesPotential = 0 Then
                    dMargin = 0
               Else
                    dMargin = (cDbl(lngSalesPotential) - cDbl(dTotalCost)) / cDbl(lngSalesPotential)
               End If

Now that we've calculated the Margin value, we need to insert it into the snapshot. Because of how the snapshot is being built, we'll need to add it in eight different places, twice for each Opportunity Status (Open or Inactive, Closed Won, Closed Lost, other). If you know that multicurrency functionality is not an important aspect of your database, you can add it only once for each status).

                  vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Sales Potential = ") & FormatCurrency(lngSalesPotential, 2)
                  vHTML = vHTML + "</tr><tr>"
                  vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Weighted = ") & FormatCurrency(lngWeighted, 2) 'Calculated Field
                  vHTML = vHTML + "</tr><tr>"

You can justcopy the last two lines here, and change the copied line to use our new margin values to add it to the snapshot.

vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Margin = ") & FormatCurrency(dMargin, 2) 'Calculated Field
vHTML = vHTML + "</tr><tr>"

You'll then want to go through the code and add this new line in each of the MultiCurrency sections for each status.

Adding a single new value is simple enough, but if you try to get more complicated with it, keep a close eye on <td> and <tr> tags before and after any code you enter to avoid breaking the Snapshot all together.

I hope this post is helpful in explaining the basics of how the snapshot is put together, and encourages people to experiment with modifications to this area. Thanks for reading!

Jason Buss' Blog : snapshot

Adding items to the Opportunity Snapshot (LAN)