Hide
Need a quick chart or graph for your blog? Try our reStructured Text renderer. Join Siafoo Now or Learn More

Sales Funnel using SharePoint Data Zoom Extension for salesforce.com Atom Feed 1

In Brief This Data Zoom script will create a Sales Funnel report based on opportunities from salesforce.com. The SharePoint Data Zoom Extensions for salesforce.com is required.... more
# 's
  1## SalesForce Opportunities v1.0
2## by SharePoint Solutions
3
4## Get search inputs
5#set( $id = $queryString.get_item("id") )
6#set( $searchCriteria = $form.get_item("searchCriteria") )
7#set( $searchType = $form.get_item("searchType") )
8#set( $rolodex = $form.get_item("rolodex") )
9
10## More global variables
11#set( $minOpp = -9999 )
12#set( $maxOpp = 9999 )
13#set( $periods = [ $minOpp, 0, 30, 60, 90, 120, $maxOpp] )
14#set( $funnelQuery = "Probability > 0.0 AND Probability < 100.0" )
15
16## Get paging attributes
17#set( $pageIndex = $Strings.ConvertToInt($form.get_item("pageIndex"), 0) )
18#set( $pageSize = $Strings.ConvertToInt($form.get_item("pageSize"), 100) )
19
20## Add filter terms
21#if( !$Strings.IsNullOrEmpty($id) && $request.HttpMethod == "GET" )
22
23 ##set( $searchFilter = $funnelQuery )
24 ##set( $searchType = "Id" )
25
26#elseif( !$Strings.IsNullOrEmpty($rolodex) )
27
28 #if( $rolodex == "All" )
29 #set( $searchFilter = $funnelQuery)
30 #set( $searchCriteria = "" )
31 #else
32 #set( $searchFilter = "OwnerId = '${rolodex}' AND ${funnelQuery}" )
33 #set( $searchCriteria = "${rolodex}" )
34 #set( $searchType = "LastName" )
35 #end
36
37#elseif( !$Strings.IsNullOrEmpty($searchCriteria) )
38
39 #set( $searchFilter = "$searchType Like '%${searchCriteria}%' AND ${funnelQuery}" )
40#else
41 #set( $searchFilter = $funnelQuery )
42#end
43
44## Get users
45
46## Setup user properties to fetch
47#set( $user_properties = ["Id", "Name"] )
48
49#set( $sfaUsers = $ApexUsers.GetUsers( "IsActive = TRUE", "LastName", 0, 1000, $user_properties ) )
50#foreach($user in $sfaUsers)
51
52#end
53## Get account details
54#set( $acct_id = $form.get_item("aid") )
55
56#if( !$Strings.IsNullOrEmpty($acct_id))
57
58 ## Setup Account properties to fetch
59 #set( $acct_properties = ["Name","BillingStreet","BillingCity","BillingState","BillingCountry","BillingPostalCode"] )
60
61 ## Get the Account
62 #set( $sfaAcct = $ApexAccounts.GetAccount($acct_id , $acct_properties) )
63
64#end
65
66#set( $ctx_id = $form.get_item("cid") )
67
68<script>
69<!--
70
71var users = new Array();
72function addUser(id,name)
73{
74 users[id] = name;
75}
76
77
78function applySearch()
79{
80 document.getElementById("pageIndex").value = "0";
81 document.getElementById("rolodex").value = "";
82 theForm.submit();
83}
84
85function setPageSize()
86{
87 document.getElementById("pageIndex").value = "0";
88 theForm.submit();
89}
90
91function gotoPageIndex(pageIndex)
92{
93 document.getElementById("pageIndex").value = pageIndex;
94 theForm.submit();
95}
96
97function gotoRolodex(rolodex)
98{
99 document.getElementById("pageIndex").value = "0";
100 document.getElementById("rolodex").value = rolodex;
101 theForm.submit();
102}
103
104function gotoAccount(rolodex,pageIndex,account,ctx)
105{
106 if(pageIndex == null)
107 pageIndex = '0';
108 document.getElementById("pageIndex").value = pageIndex;
109 document.getElementById("rolodex").value = rolodex;
110 document.getElementById("aid").value = account;
111 document.getElementById("cid").value = ctx;
112 theForm.submit();
113}
114
115function gotoDetails(rolodex,pageIndex,ctx)
116{
117 if(pageIndex == null)
118 pageIndex = '0';
119 document.getElementById("pageIndex").value = pageIndex;
120 document.getElementById("rolodex").value = rolodex;
121 document.getElementById("cid").value = ctx;
122 theForm.submit();
123}
124
125-->
126</script>
127
128#foreach($user in $sfaUsers)
129 <script>addUser('${user.Id}','${user.Name}');</script>
130#end
131
132#if( !$Strings.IsNullOrEmpty($ctx_id ))
133
134 <script>window.location.hash='${ctx_id}';</script>
135
136#end
137
138<span class="ms-linksectionheader"><h3 class="ms-standardheader">Search</h3></span>
139<blockquote style="margin:10px">
140
141<!-- Search area -->
142<input type="hidden" name="pageIndex" value="$!{pageIndex}" />
143<input type="hidden" name="rolodex" value="$!{rolodex}" />
144<input type="hidden" name="aid" value="$!{acct_id}" />
145<input type="hidden" name="cid" value="$!{ctx_id}" />
146
147<table cellpadding="2" cellspacing="0">
148 <tr>
149 <td>Find an opportunity whose
150 <select name="searchType">
151 <option value="Name" #if($searchType == "LastName")selected #end>Name</option>
152 </select>
153 contains
154 <input type="text" name="searchCriteria" value="" />
155 </td>
156 <td>
157 <a title="Find Account" href="javascript:applySearch();">
158 <img title="Go Search" alt="Find Employee" src="_layouts/images/gosearch.gif" style="border-width:0px;" />
159 </a>
160 </td>
161 </tr>
162</table>
163</blockquote>
164
165<span class="ms-linksectionheader"><h3 class="ms-standardheader">Filter by salesperson:</h3></span>
166<blockquote style="margin:10px">
167
168<!-- Rollodex -->
169<table cellpadding="2" cellspacing="2">
170 <tr>
171
172 #foreach($user in $sfaUsers)
173 <td class="ms-vb"><a href="javascript:gotoRolodex('$user.Id');">$user.Name</a>&nbsp;|</td>
174 #end
175
176 <td class="ms-vb"><a href="javascript:gotoRolodex('All');">All</a></td>
177 <tr>
178</table>
179</blockquote>
180
181## Setup Opportunity
182#set( $properties =["Id", "Name", "AccountId", "Description", "Amount", "StageName", "OwnerId", "ExpectedRevenue", "Probability", "CloseDate"] )
183
184## Get the matching entries
185#set( $sfaOpportunities= $ApexOpportunities.GetOpportunities($searchFilter, "CloseDate ASC", $pageIndex, $pageSize, $properties) )
186
187## Get the result data
188#set( $firstItemNumber = $pageIndex * $pageSize + 1 )
189#set( $lastItemNumber = $firstItemNumber + $sfaOpportunities.Count - 1 )
190#set( $prevPageIndex = $pageIndex - 1 )
191#set( $nextPageIndex = $pageIndex + 1 )
192
193<!-- Toolbar -->
194<table class="ms-menutoolbar" cellpadding="5" cellspacing="0" border="0" width="95%" >
195 <tr>
196 <td class="ms-toolbar"></td>
197
198 <td width="99%" class="ms-toolbar" nowrap>
199 <IMG SRC="_layouts/images/blank.gif" width=1 height=18 alt=""></td>
200
201 <td class="ms-toolbar" nowrap="true">
202 <table cellpadding="0" cellspacing="0" border="0">
203 <tr>
204 #if( $pageIndex > 0 )
205 <td>
206 <img src="_layouts/images/prev.gif" border="0" alt="Previous Page" onclick="gotoPageIndex($prevPageIndex);" /></td>
207 #end
208
209 <td class="ms-listheaderlabel" nowrap>&nbsp;${firstItemNumber}-${lastItemNumber}&nbsp;</td>
210
211 #if( $sfaOpportunities.Count == $pageSize )
212 <td>
213 <img src="_layouts/1033/images/next.gif" border="0" alt="Next Page" onclick="gotoPageIndex($nextPageIndex);" /></td>
214 #end
215 </tr>
216 </table>
217 </td>
218
219 <td class=ms-separator>|</td>
220
221 <td class="ms-toolbar" nowrap="true">Show &nbsp;&nbsp;</td>
222 <td class="ms-toolbar" nowrap="true">
223 <select name="pageSize" onchange="setPageSize();">
224 <option value="5" #if($pageSize == 5) selected #end>5</option>
225 <option value="10"#if($pageSize == 10) selected #end>10</option>
226 <option value="15"#if($pageSize == 15) selected #end>15</option>
227 <option value="50"#if($pageSize == 50) selected #end>50</option>
228 <option value="100"#if($pageSize == 100) selected #end>100</option>
229 </select>
230 </td>
231
232 <td>&nbsp;&nbsp;&nbsp;</td>
233 </tr>
234</table>
235
236
237<!-- Results area -->
238#foreach($pidx in [0..6])
239 #beforeall
240 <table class="ms-listviewtable" cellpadding="8" cellspacing="0" width="95%" border="0">
241 <tr class="ms-viewheadertr">
242 <th class="ms-vh2-nofilter"></th>
243 <th class="ms-vh2-nofilter"></th>
244 <th class="ms-vh2-nofilter">Opportunity name</th>
245 <th class="ms-vh2-nofilter">Sales stage</th>
246 <th class="ms-vh2-nofilter">Amount</th>
247 <th class="ms-vh2-nofilter">Probability</th>
248 <th class="ms-vh2-nofilter">Expected revenue</th>
249 <th class="ms-vh2-nofilter">Close date</th>
250 <th class="ms-vh2-nofilter">Salesperson</th>
251 </tr>
252 #before
253 #set( $minp = $periods.get_item( $pidx ))
254 #set( $maxp = $minp )
255 #set( $periodTitle = $null )
256 #set( $npidx = $pidx + 1 )
257 #if( $pidx == 0 )
258 #set( $periodTitle = "Overdue" )
259 #set( $maxp = $periods.get_item( $npidx ) )
260 #elseif ( $pidx == 6 )
261 ## no items will render on the last pass
262 #else
263 #set( $maxp = $periods.get_item( $npidx ) )
264 #set( $periodTitle = $Strings.Format('More than {0} days', $minp) )
265 #end
266 #if( $minp > $minOpp && $maxp < $maxOpp )
267 #set( $tmp = $maxp + 1)
268 #set( $periodTitle = $Strings.Format('{0} and less than {1} days to close', $periodTitle, $tmp) )
269 #end
270 #set( $low = $DateTimes.Today.AddDays( $minp ) )
271 #set( $high = $DateTimes.Today.AddDays( $maxp ) )
272 #set( $titleRendered = $false )
273 #set( $cnt = 0 )
274 #set( $sum = 0 )
275 #set( $expSum = 0 )
276 #each
277 #foreach( $sfaOpportunity in $sfaOpportunities )
278 #before
279 #set( $shouldRender = $false )
280 #if( $sfaOpportunity.CloseDate.CompareTo($low) > 0 && $sfaOpportunity.CloseDate.CompareTo($high) <= 0 )
281 #set( $shouldRender = $true)
282 #end
283 ## Normal
284 #set( $hl = "background-color:transparent;" )
285 ## Selected
286 #if($ctx_id != $null && $sfaOpportunity.Id == $ctx_id)
287 #set( $hl = "background-color:#B0C4DE;" )
288 #end
289 ## Past Due
290 #if($sfaOpportunity.CloseDate.CompareTo($DateTimes.Today) < 0 )
291 #set( $hl = "$!{hl}color:red;" )
292 #end
293 #if($shouldRender == $true && $titleRendered == $false)
294 #set( $titleRendered = $true )
295 <tr><td colspan="9" class="ms-vb2" style="width:100%;background-color:#98AFC7"><strong>$!{periodTitle}</strong></td></tr>
296 #end
297 #odd
298 #if($shouldRender == $true)
299 <tr class="">
300 #end
301 #even
302 #if($shouldRender == $true)
303 <tr class="ms-alternating">
304 #end
305 #each
306 #if($shouldRender == $true)
307 #set( $cnt = $cnt + 1 )
308 #if( $sfaOpportunity.Amount != $null )
309 #set( $sum = $sum + $sfaOpportunity.Amount )
310 #end
311 #if( $sfaOpportunity.ExpectedRevenue != $null )
312 #set( $expSum = $expSum + $sfaOpportunity.ExpectedRevenue )
313 #end
314 <td class="ms-vb2" style="width:15px;text-align:center;$!{hl}">#if( $Strings.IsNullOrEmpty($sfaOpportunity.Description) != $true )<a title="Click for notes" href="javascript:gotoDetails('$!{rolodex}','$!{pageIndex}', '$!{sfaOpportunity.Id}')"><img alt="Click for notes" style="border:0px" src="_layouts/images/ATTACHTB.GIF"></a>#end</td>
315 <td class="ms-vb2" style="width:15px;text-align:center;$!{hl}">#if( $Strings.IsNullOrEmpty($sfaOpportunity.AccountId) != $true )<a title="Click for account details" href="javascript:gotoAccount('$!{rolodex}','$!{pageIndex}', '$!{sfaOpportunity.AccountId}', '$!{sfaOpportunity.Id}')"><img alt="Click for account details" style="border:0px" src="_layouts/images/addressbook.gif"></a>#end</td>
316 <td class="ms-vb2" style="$!{hl}"><a name="${sfaOpportunity.Id}"></a>$sfaOpportunity.Name</td>
317 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.StageName</td>
318 <td class="ms-vb2" style="$!{hl}">$Strings.Format( '{0:C}', $!{sfaOpportunity.Amount} )</td>
319 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.Probability %</td>
320 <td class="ms-vb2" style="$!{hl}">$Strings.Format( '{0:C}', $!{sfaOpportunity.ExpectedRevenue} )</td>
321 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.CloseDate.ToString("d")</td>
322 <td class="ms-vb2" style="$!{hl}"><script>document.write(users['${sfaOpportunity.OwnerId}']);</script></td>
323 #end
324 #after
325 #if($shouldRender == $true)
326 #if( $acct_id != $null && $sfaOpportunity.AccountId == $acct_id && $sfaAcct != $null && $Strings.IsNullOrEmpty($sfaAcct.Name) != $true && $ctx_id != $null && $ctx_id == $sfaOpportunity.Id)
327 </tr>
328 <tr>
329 <td colspan=6 style="padding-left:75px">
330 <table style="background-color:#F5F5F5;border-width:1px;border-style:solid;border-color:black">
331
332 <tr>
333 <th style="text-align:right">Account name:</th><td style="background-color:white">$sfaAcct.Name</td>
334 </tr>
335
336 #if( $Strings.IsNullOrEmpty($sfaAcct.BillingStreet) != $true )
337 <tr>
338 <th style="text-align:right">Address:</th><td style="background-color:white">$sfaAcct.BillingStreet</td>
339 </tr>
340 #end
341 #if( $Strings.IsNullOrEmpty($sfaAcct.BillingCity) != $true )
342 <tr>
343 <th style="text-align:right"></th><td style="background-color:white;margin-top:0px">$!sfaAcct.BillingCity, $!sfaAcct.BillingState $!sfaAcct.BillingCountry $!sfaAcct.BillingPostalCode</td>
344 </tr>
345 #end
346 </table>
347 </td>
348 #elseif( $Strings.IsNullOrEmpty($sfaOpportunity.Description) != $true && $ctx_id != $null && $ctx_id == $sfaOpportunity.Id)
349 </tr>
350 <tr>
351 <td colspan=6 style="padding-left:75px">
352 <table style="background-color:#F5F5F5;border-width:1px;border-style:solid;border-color:black">
353 <tr>
354 <td style="padding:12px"><strong><u>Notes:</u></strong><br/><br/>$sfaOpportunity.Description</td>
355 </tr>
356 </table>
357 </td>
358 #else
359 </tr>
360 #end
361 #end
362 #afterall
363 #if( $cnt > 0 )
364 <tr>
365 <td colspan="4" class="ms-vb2"></td>
366 <td class="ms-vb2"><strong>$Strings.Format( '{0:C}', $!{sum} )</strong></td>
367 <td class="ms-vb2"></td>
368 <td class="ms-vb2"><strong>$Strings.Format( '{0:C}', $!{expSum} )</strong></td>
369 <td class="ms-vb2"></td>
370 <td class="ms-vb2"><br/><br/><br/></td>
371 </tr>
372 #end
373 #nodata
374 #if($shouldRender)
375 <div style="padding:10px">No opportunities found in range.</div>
376 #end
377 #end
378 #afterall
379 </table>
380 #nodata
381 <div style="padding:10px">No opportunities found in range.</div>
382 #end
383<div>

This Data Zoom script will create a Sales Funnel report based on opportunities from salesforce.com. The SharePoint Data Zoom Extensions for salesforce.com is required.

Live demo: Sales Funnel

Comments

over 5 years ago (26 Mar 2009 at 05:38 PM) by stacydraper
I gave it a 4 only because I can't tell who's more blury.