Hide
Solve a problem – Filter by language, license, keyword, owner, or search text to find code & info fast. Join Siafoo Now or Learn More

Won - Lost Report using SharePoint Data Zoom Extension for salesforce.com Atom Feed 0

In Brief This VTL script will create a Won-Lost report from salesforce.com opportunity data. The SharePoint Data Zoom Extensions for salesforce.com is required.... more
# 's
  1## SalesForce Won-Closed Performance 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 = -120 )
12#set( $maxOpp = $minOpp * -1 )
13#set( $periods = [ $minOpp, -90, -60, -30, -14, -7, 0 ] )
14#set( $funnelQuery = "StageName LIKE 'Won - Closed' AND CloseDate = LAST_N_DAYS:${maxOpp}" )
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 DESC", $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 [5..0])
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( $lpidx = $pidx + 1 )
259 #set( $lastMin = $periods.get_item( $lpidx ))
260 #set( $lastMin = $lastMin * -1 )
261 #set( $lastMax = $minOpp * -1 )
262 #set( $periodTitle = $Strings.Format('Won between {0} and {1} days ago', $lastMin, $lastMax ) )
263 #set( $maxp = $periods.get_item( $npidx ) )
264 #elseif ( $pidx == 6 )
265 ## no items will render on the last pass
266 #else
267 #set( $maxp = $periods.get_item( $npidx ) )
268 #set( $tmp = $minp * -1)
269 #set( $periodTitle = $Strings.Format('{0} days ago', $tmp ) )
270 #end
271 #if( $minp > $minOpp && $maxp < $maxOpp )
272 #set( $tmp = $maxp * -1)
273 #if( $tmp == 0 )
274 #set( $periodTitle = "Won within the past week" )
275 #else
276 #set( $periodTitle = $Strings.Format('Won between {0} and {1}', $tmp, $periodTitle ) )
277 #end
278
279 #end
280 #set( $low = $DateTimes.Today.AddDays( $minp ) )
281 #set( $high = $DateTimes.Today.AddDays( $maxp ) )
282 #set( $titleRendered = $false )
283 #set( $cnt = 0 )
284 #set( $sum = 0 )
285 #set( $expSum = 0 )
286 #each
287 #foreach( $sfaOpportunity in $sfaOpportunities )
288 #before
289 #set( $shouldRender = $false )
290 #if( $sfaOpportunity.CloseDate.CompareTo($low) > 0 && $sfaOpportunity.CloseDate.CompareTo($high) <= 0 )
291 #set( $shouldRender = $true)
292 #end
293 ## Normal
294 #set( $hl = "background-color:transparent;" )
295 ## Selected
296 #if($ctx_id != $null && $sfaOpportunity.Id == $ctx_id)
297 #set( $hl = "background-color:#B0C4DE;" )
298 #end
299 ## Past Due
300 #if($sfaOpportunity.CloseDate.CompareTo($DateTimes.Today) < 0 )
301 #set( $hl = "$!{hl}color:green;" )
302 #end
303 #if($shouldRender == $true && $titleRendered == $false)
304 #set( $titleRendered = $true )
305 <tr><td colspan="9" class="ms-vb2" style="width:100%;background-color:#98AFC7"><strong>$!{periodTitle}</strong></td></tr>
306 #end
307 #odd
308 #if($shouldRender == $true)
309 <tr class="">
310 #end
311 #even
312 #if($shouldRender == $true)
313 <tr class="ms-alternating">
314 #end
315 #each
316 #if($shouldRender == $true)
317 #set( $cnt = $cnt + 1 )
318 #if( $sfaOpportunity.Amount != $null )
319 #set( $sum = $sum + $sfaOpportunity.Amount )
320 #end
321 #if( $sfaOpportunity.ExpectedRevenue != $null )
322 #set( $expSum = $expSum + $sfaOpportunity.ExpectedRevenue )
323 #end
324 <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>
325 <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>
326 <td class="ms-vb2" style="$!{hl}"><a name="${sfaOpportunity.Id}"></a>$sfaOpportunity.Name</td>
327 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.StageName</td>
328 <td class="ms-vb2" style="$!{hl}">$Strings.Format( '{0:C}', $!{sfaOpportunity.Amount} )</td>
329 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.Probability %</td>
330 <td class="ms-vb2" style="$!{hl}">$Strings.Format( '{0:C}', $!{sfaOpportunity.ExpectedRevenue} )</td>
331 <td class="ms-vb2" style="$!{hl}">$sfaOpportunity.CloseDate.ToString("d")</td>
332 <td class="ms-vb2" style="$!{hl}"><script>document.write(users['${sfaOpportunity.OwnerId}']);</script></td>
333 #end
334 #after
335 #if($shouldRender == $true)
336 #if( $acct_id != $null && $sfaOpportunity.AccountId == $acct_id && $sfaAcct != $null && $Strings.IsNullOrEmpty($sfaAcct.Name) != $true && $ctx_id != $null && $ctx_id == $sfaOpportunity.Id)
337 </tr>
338 <tr>
339 <td colspan=6 style="padding-left:75px">
340 <table style="background-color:#F5F5F5;border-width:1px;border-style:solid;border-color:black">
341
342 <tr>
343 <th style="text-align:right">Account name:</th><td style="background-color:white">$sfaAcct.Name</td>
344 </tr>
345
346 #if( $Strings.IsNullOrEmpty($sfaAcct.BillingStreet) != $true )
347 <tr>
348 <th style="text-align:right">Address:</th><td style="background-color:white">$sfaAcct.BillingStreet</td>
349 </tr>
350 #end
351 #if( $Strings.IsNullOrEmpty($sfaAcct.BillingCity) != $true )
352 <tr>
353 <th style="text-align:right"></th><td style="background-color:white;margin-top:0px">$!sfaAcct.BillingCity, $!sfaAcct.BillingState $!sfaAcct.BillingCountry $!sfaAcct.BillingPostalCode</td>
354 </tr>
355 #end
356 </table>
357 </td>
358 #elseif( $Strings.IsNullOrEmpty($sfaOpportunity.Description) != $true && $ctx_id != $null && $ctx_id == $sfaOpportunity.Id)
359 </tr>
360 <tr>
361 <td colspan=6 style="padding-left:75px">
362 <table style="background-color:#F5F5F5;border-width:1px;border-style:solid;border-color:black">
363 <tr>
364 <td style="padding:12px"><strong><u>Notes:</u></strong><br/><br/>$sfaOpportunity.Description</td>
365 </tr>
366 </table>
367 </td>
368 #else
369 </tr>
370 #end
371 #end
372 #afterall
373 #if( $cnt > 0 )
374 <tr>
375 <td colspan="4" class="ms-vb2"></td>
376 <td class="ms-vb2"><strong>$Strings.Format( '{0:C}', $!{sum} )</strong></td>
377 <td class="ms-vb2"></td>
378 <td class="ms-vb2"><strong>$Strings.Format( '{0:C}', $!{expSum} )</strong></td>
379 <td class="ms-vb2"></td>
380 <td class="ms-vb2"><br/><br/><br/></td>
381 </tr>
382 #end
383 #nodata
384 #if($shouldRender)
385 <div style="padding:10px">No opportunities found in range.</div>
386 #end
387 #end
388 #afterall
389 </table>
390 #nodata
391 <div style="padding:10px">No opportunities found in range.</div>
392 #end
393<div>

This VTL script will create a Won-Lost report from salesforce.com opportunity data. The SharePoint Data Zoom Extensions for salesforce.com is required.

Live Demo: Won/Lost/Closed