+ Start a Discussion

Need report automation help when there are two date fields as criteria

Hi all, I need some help trying to streamline report refreshes from within Salesforce.com out to Excel, either via the add-in or the Connector tool.  We have a semi-unique situation where our reports are intended to provide a snapshot in time, and need to look at two different date fields.  For example, we want to see for June 2006, how many records were created within that month or had an "acceptance date" (a custom field) within that month.  That criteria looks like this in Salesforce.com reports (though this could be tweaked further too):

Filtered By:1 AND ((2 AND 3) OR (4 AND 5))
   1. Type equals XYZ
   2. Create Date greater or equal 6/1/2006 
   3. Create Date less or equal 6/30/2006 
   4. Acceptance Date greater or equal 6/1/2006 
   5. Acceptance Date less or equal 6/30/2006 

This approach leads to monthly static report folders and other non-standard structures in Salesforce.com, and removes the capability of a fast automated refresh using the GUI, the Excel add-in, or the Excel connector.

1)  The Excel connector is ruled out for automating this: it’s not well-built for pulling in nested criteria (for example, [1 or [2 and 3]]) that are used for the multiple date ranges, and thus can’t reproduce the initial reports to automate them.  It also doesn’t have this nested capability within the connector itself.

2)  The Excel add-in is ruled out for automating this: while it can pull across the nested criteria from SFdc reports, those criteria are currently static dates due to the “one rolling date” limitation on the front side of SFdc.  The Excel add-in doesn't allow updating of filter criteria.

Can anyone recommend a way to alleviate some of the manual-ness of this process?  Thanks!

Message Edited by chris925 on 07-09-2006 08:08 PM