This technique allows you to create a Visualforce page that prompts a user for ‘Start Date’ and ‘End Date’ values that can be passed to corresponding filters on a Salesforce Report or Conga Query.

Due to their complicated nature, the integration of Conga with JavaScript, Visualforce, and Apex (or other programming languages) is not supported. This solution does not work in Salesforce Lightning due to the usage of Javascript.

Your Date field in Salesforce MUST be a Date Field, NOT a Date/Time Field.

  1. Create a Visualforce Page called "DatePicker".

    • Navigate to Setup → Build → Develop → Visualforce Pages

    • Click New.

    • Name your page “DatePicker”.

  2. Paste the Visualforce code below into the page. The default start date is 1 week before today’s date and the default end date is 1 week after today’s date. These can be altered on line 24 (default start date) and line 32 (default end date), if necessary.
  3. Create a standard Conga Composer button that you want to use with the Datepicker.
  4. Replace the first line of the Conga Composer button URL with /apex/Datepicker.

    • Example Button Syntax on Account Object:

      /apex/Datepicker
?sessionId={!API.Session_ID}
&serverUrl={!API.Partner_Server_URL_80}
&id={!Account.Id}

5. Create a Salesforce Report or Conga Query to filter on a dynamic date range.

  • Your report or query will need at least two filters to provide a date range. For a standard range, your filter operators will be greater or equal to your start date and less or equal to your end date. For example, a Salesforce Report with the start date as the ‘pv1’ filter and the end date as the ‘pv2’ filter:

6. Pass your start and end dates to your report or query.

  • For Salesforce Reports, you’ll pass these variables: 
    • Start Date: {StartDateR}
    • End Date: {EndDateR}

  • For Conga Queries, you’ll pass these variables:

    • Start Date: {StartDateQ}

    • End Date: {EndDateQ}

  • Report-based example:

    • /apex/DatePicker
       ?sessionId={!API.Session_ID}
      &serverUrl={!API.Partner_Server_URL_80}
      &id={!Account.Id}
      &ReportId=[DynamicOpps]00OE0000002T4H8?pv0={!Account.Id}~pv1= {StartDateR}~pv2={EndDateR}
  • Query-based example:
    • /apex/DatePicker
      ?sessionId={!API.Session_ID}
      &serverUrl={!API.Partner_Server_URL_80}
      &id={!Account.Id}
      &QueryID=[DynamicOpps]a04E0000004SmqG?pv0={!Account.Id}~pv1= {StartDateQ}~pv2={EndDateQ}

When a user clicks this button, the Visualforce page is shown. The user can then select the start and end dates before clicking “Launch”. The date values are then substituted into the URL and the browser is re-directed to the Composer dialog.

Visualforce Page

<apex:page language="en" showHeader="false" sidebar="false" standardStylesheets="true" ><link type="text/css" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/themes/cupertino/jquery-ui.css" rel="stylesheet" /><script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.js"></script><script type="text/javascript" src="https://jquery-ui.googlecode.com/svn/trunk/ui/i18n/jquery.ui.datepicker-en-GB.js"></script><script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.js"></script><apex:pageBlock title="Please select the start and end dates:"><p>Start Date:<br /><input id="startDate" type="textbox"/></p><p>End Date:<br /><input id="endDate" type="textbox"/></p><!--$(selector).datepicker({defaultDate: new Date(2009, 1-1, 26)});$(selector).datepicker({defaultDate: '01/26/2009'});$(selector).datepicker({defaultDate: +7});$(selector).datepicker({defaultDate: '+1m -1d'});--><script type="text/javascript">$(function() {$("#startDate").datepicker(); // You must leave this line$("#startDate").datepicker($.datepicker.regional['en-US']);$("#startDate").datepicker('setDate', '-1w'); // This is optional});s</script><script type="text/javascript">$(function() {$("#endDate").datepicker(); // You must leave this line$("#endDate").datepicker($.datepicker.regional['en-US']);$("#endDate").datepicker('setDate', '+1w'); // This is optional});</script><br /><input type="button" onclick="launchConga()" value="Launch" /><script>function launchConga(){var startDate = document.getElementById("startDate");var endDate = document.getElementById("endDate");// For Queries...var sDate = new Date(startDate.value);var eDate = new Date(endDate.value);var startDateForQueries = sDate.getFullYear() + "-" + ("0" + (sDate.getMonth() + 1)).slice(-2) + "-" + ("0" + sDate.getDate()).slice(-2);var endDateForQueries = eDate.getFullYear() + "-" + ("0" + (eDate.getMonth() + 1)).slice(-2) + "-" + ("0" + eDate.getDate()).slice(-2);var qsIndex = window.parent.location.href.indexOf('?') + 1;var qs = window.parent.location.href.substring(qsIndex, window.parent.location.href.length);qs = unescape(qs);// Use these lines if you're passing to Reports:qs = qs.replace(/{StartDateR}/g , startDate.value);qs = qs.replace(/{EndDateR}/g , endDate.value);// Use these lines if you're passing to Queries:qs = qs.replace(/{StartDateQ}/g, startDateForQueries );qs = qs.replace(/{EndDateQ}/g, endDateForQueries );window.parent.location.href = "https://composer.congamerge.com?" + qs;}</script></apex:pageBlock></apex:page>

Sample Button (Account)

/apex/DatePicker
?sessionId={!API.Session_ID}
&serverUrl={!API.Partner_Server_URL_80}
&id={!Account.Id}
&ReportId=[DynamicOpps]00OA00000044TEO?pv0={StartDateR}~pv1={EndDateR}