Audit9 Blog

I think it’s fair to say that consideration of reporting and analytics is not traditionally a technical architect’s direct concern. In the Salesforce context I always challenge this presumption and promote the idea that a project architect should absolutely be looking at the high-level analytical requirements during the foundation stage of the project. Why you may well ask? The answer to this is simple, in order to design a fit-for-purpose data model you must consider data coming out (via the native reporting tools) as well as data going in efficiently. I make this point in a prior post that outlines my view on the Salesforce development process. The number of projects I see where this thinking hasn’t been applied and 3rd party tools are wheeled in to address the gap continues to surprise me. Key to success here is understanding what can be achieved with the Salesforce reporting functionality. Which brings me on to the topic for this post – exception reports, a type of reporting not obviously catered for by the standard reporting tools. For instance – show me a list of Accounts without an Opportunity in the last year. Such reports are invaluable in identifying omissions, focusing sales activities, generating call lists, tracking inactivity etc. etc.

I’ll use 2 examples below to clarify how to approach such a scenario.

Simple Example.
Accounts without a closed Opportunity in the last 30 days.

For this report we use a “without” Cross Filter as per the screenshots. Cross Filters were added in Spring ’12 and enable records to be filtered based on the existence (or non-existence) of related records which meet defined criteria. Within a Cross filter up to 5 sub-filters can be defined. Note, the report’s top level filters do not apply at the sub-filter level. Cross filters can be added for any related object, custom or standard – the limit is 3 per report (each with up to 5 sub-filters). Powerful functionality indeed and a great convenience that avoids a Custom Report Type for the outer join, note the sub-filter is limited to fields on the related object, no-cross object sub-filter logic is possible.

Complex Example.
Accounts without a Won Opportunity involving a certain Product Family in the last 60 days.

For this report we start with a Cross Filter report to satisfy the “without Won Opportunity” requirement. From there we need a indicator (of the existence of an Opportunity Product linked to a product of the specified family) at the Opportunity level which can be used in a sub-filter. This is actually straightforward to do and relies on the old school technique for exception reports, namely the use of a rollup summary fields and simple workflow.

Solution components :
Opportunity Product – Custom Field – ProductFamilyHardwareFlag
Number (18,0 default 0)

Opportunity Product – Workflow Rule – “Opp Product – New or Changed Product”
Evaluate – Created and Edited – Formula (OR(ISNEW(), ISCHANGED( Product2Id )))

Opportunity Product – Workflow Action – “Opp Product – Set ProdFamilyHardwareFlag”
Field to update [ProductFamilyHardwareFlag] with Formula = IF( TEXT(Product2.Family)=’Hardware’, 1,0)

Opportunity – Custom Field – ProductFamilyHardwareCount
Rollup Summary Field (Opportunity Product – SUM – Field to aggregate = ProductFamilyHardwareFlag). All records included.

How does this all work? In short, a workflow rule on Opportunity Product sets a numeric field equal to 1/0 based on the product family meeting the defined criteria. The numeric field is then rolled up to the Opportunity level via RSF, which is then applied in a sub-filter as below.

Note, this is just an example to illustrate the concept. An actual implementation would typically require a more generic approach.

A real frustration with inline Visualforce pages (added to standard page layouts) is the static nature of the height setting. From the layout we can set a specific height, but ideally we want the height set dynamically from the content height. Sounds like a simple enough requirement, however, the fact that the VF page section is implemented as an iFrame loaded from another domain makes cross-domain communication a non-trivial task. Note, for security reasons browsers enforce a same-origin policy, preventing script running across domain boundaries. Workarounds to this restriction include the HTML5 postMessage function on the client and proxy services on the server. So the question becomes, how can the iFrame content communicate across domains to tell the host page the correct height for the iFrame? The answer to this is somewhat contrived, but hopefully my basic approach below tells a clear enough story.

Here we go.
1. The inline VF page contains an iFrame, into which we load a helper script file from the base salesforce domain with a height parameter in the querystring.

document.getElementById(‘helpframe’).src=’https://emea.salesforce.com{!$Resource.iFrameHelper}?height=’+h+’&iframename=MYPAGENAME&cacheb=’+Math.random();

The random parameter is there to avoid caching issues. Crucially as this helper script is running on the same domain as the standard page layout, it can call a script in the page itself. Note the helper script is loaded from a static resource. To keep the solution generic the page name is passed as a parameter also, handily the title attribute in the host page is set to the page name, we’ll use this later to find the id for the iFrame.

2. In the helper script we extract the 2 parameters from the querystring and call a script function in the host page (via parent.parent – which traverses up the DOM to the parent page).

3. In order to add script to the host page we use the Sidebar injection technique (or hack) and introduce a simple Javascript function (via a narrow component) which takes the page name and height, finding the former in the DOM using Ext.query (Ext is already referenced in the page), and setting the element height to the latter.

Example solution components::

0. Pre-requisites:
User Profiles must have the “Show Custom Sidebar On All Pages” General User Permission ticked.

1. Add a HTML file static resource, named iFrameHelper – content below.
[sourcecode language=”html”]
<html>
<body onload="parentIframeResize()">
<script>
// Tell the parent iframe what height the iframe needs to be
function parentIframeResize(){
var height = getParam(‘height’);
var iframename = getParam(‘iframename’);
// This works as our parent’s parent is on our domain..
parent.parent.resizeIframe(height,iframename);
}
// Helper function, parse param from request string
function getParam(name){
name = name.replace(/[\[]/,"\\\[").replace(/[\]]/,"\\\]");
var regexS = "[\\?&]"+name+"=([^&#]*)";
var regex = new RegExp( regexS );
var results = regex.exec( window.location.href );
if( results == null )
return "";
else
return results[1];
}
</script>
</body>
</html>
[/sourcecode]

2. Add a HTML sidebar component (narrow left) – click “Show HTML” and paste in markup below.
[sourcecode language=”html”]
<script>
function resizeIframe(h, ifn){
var e = Ext.query("iframe[title=’"+ifn+"’]");
console.log(e);
var itarget = e[0].getAttribute(‘id’);
Ext.get(itarget).set({height: parseInt(h)+10});
}
</script>
[/sourcecode]

3. Add a Visualforce page named MyTestInlineVFPage – paste in markup below.
[sourcecode language=”html”]
<apex:page docType="html-5.0" standardController="Account">
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>
This is your new Page<br/>

<script type="text/javascript">
function resizeParentIFrame(){
var h = document.body.scrollHeight;
//TODO – replace with the relevant page name.
var iframename = ‘MyTestInlineVFPage’;
//TODO – replace with the relevant base url – page runs on the VF domain so functions return the VF domain.
var baseUrlForInstance = ‘https://emea.salesforce.com&#8217;;
document.getElementById(‘helpframe’).src = baseUrlForInstance+'{!$Resource.iFrameHelper}?height=’+h+’&iframename=’+iframename+’&cacheb=’+Math.random();
}

function forceParentIFrameResize(){
document.getElementById(‘helpframe’).src=document.getElementById(‘helpframe’).src;
}

window.onload=function(){
resizeParentIFrame();
}
</script>
</apex:page>
[/sourcecode]

4. Add the VF page to a new section on an Account layout.

The solution above needs further work in the areas below. I’m planning to improve this as part of a commercial AppExchange package I’m working on and will post the improved resize solution.

Code quality.
Exception handling.
Calculation of the base salesforce domain – currently hardcoded in the inline page.

I’d be delighted to hear about other improvements, or indeed alternative approaches.