Hide
Siafoo is here to make coding less frustrating and to save you time. 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.