Salesforce: Dynamic Reports in Customer Portal

Overview:

This post covers a method that allows your customers to pass custom date ranges and other filters to a specific report in the Salesforce Customer Portal through simple HTML forms and Visualforce pages.

One of the limitations with Reports in the Salesforce Customer Portal is that customers cannot modify criteria(such as date ranges) in a report, and reports need to be built with either relative date ranges(i.e. last 90 days, this year, next week, etc), or expose entire large data sets that include ALL results to give the customers what they are looking for.

In this example we will create a report on a custom object called Transactions which stores a customer’s purchase records and loyalty points.  We will then create a form that allows customers to send their own date ranges to the report and filter by these dates.

Requirements:

– Salesforce Enterprise/Unlimited Edition with Administrator access

– Salesforce Customer Portal

– Basic understanding of HTML Forms

Instructions:

Creating the Report

  1. Create your report as you would like your customers to see it, adding the appropriate columns, groupings, etc.
  2. Set the Last Activity range to “All time” to return all results.
  3. Add the Date field you wish to filter by twice to the report criteria.  Once with Greater Than and once with Less Than as the operators.  (In this case we are using our custom field Transaction Date)
  4. Set both Date filters to a blank value.  (We will set these values dynamically later)
  5. Add any other Report filters you would like to the report.
  6. Save and Run the Report (Make sure to save it in a folder that is accessible in the Customer Portal)
The report should return all results as the filters are set to blank.
Customer Portal Report - No Filters

Adding Report Criteria manually through the URL

Salesforce allows you to update filter values through Querystring variables in the URL.
Running the above report, but by typing in the URL with querystring variables:
https://na12.salesforce.com/00OU00000013vG6?pv0=1/1/2011&pv1=2/1/2011
Tells the Report to substitute “1/1/2011” for the first Transaction Date (PV0) and “2/1/2011” for the second Transaction Date (PV1)
This method works in both the regular Salesforce Reports section, and when viewing reports in the Customer Portal.  Try experimenting with different values for Pv0, Pv1, Pv2, etc in the Querystring.  You can add as many Variables as you have filters in the report. Remember that the Variable numbers correspond to the Filters in order from top to bottom.  So P0 always equals the first Filter value and just add 1 for any subsequent Filter values(Pv1,Pv2,…Pvn).

Creating a Form to Enter Date Ranges

Now that the report is built and we know we can send filter variables through the Querystring, we need to develop a Visualforce page to allow the User to enter the date ranges and send the criteria to the Report.

This can be done by going to Setup->Develop->Pages

Create a new Page with the following HTML FORM code:

<apex:page >
  <br/>
  <B>Use the date fields below to view Loyalty Points for a specific time period</B> 
  <br />
  <br/><br/>
  <form method="get" action="/00OU00000013vG6" >
    <B>Date Ranges:</B>
    <br/><br/>
    Transaction Date From (MM/DD/YYYY):
       <input type="text" name="pv0" id="pv0" /><br />
    Transaction Date To (MM/DD/YYYY):
       <input type="text" name="pv1" id="pv1" />
       <br />
       <input type="submit" value="Filter Transactions" class="submit" />
  </form>

</apex:page>

Note the Highlighted areas.

method=”get” – Tells the Form to send the variables to the querystring .

action=”/00OU00000013vG6″ – Tells the Form which Report to open.  Replace this value with the full URL of your Report, OR, a relative path can be used by just including the forward slash “/” and the Report ID.

name=”pv0″  id=”pv0″ – Assigns the input box value to the variable PV0 and sends it in the querystring. (i.e. ?pv0=input box value)
name=”pv1″   id=”pv1″ – Assigns the input box value to the variable PV1 and sends it in the querystring (i.e. &pv1=input box value)

Form output:

 

 

 

 

 

 

Once you have created the VisualForce page, it is important to ensure the appropriate Customer Portal Profiles have access to it.

Make sure to click the Security link for the new Page, and assign it to the appropriate Customer Portal Profiles.

Note:For simplicity sake, the above form only includes basic INPUT text boxes and a Submit button for a single report.  However, this form could be enhanced to include Javascript and CSS for formatted date selectors and even a dynamic action field based on a drop-down menu if you have multiple reports that you wish to send filter variables to.

Create a Tab for the newly developed Visualforce Page

Once the Visualforce Page has been created and the appropriate permissions have been given to the Customer Portal users, you can now create a Tab to display the new Visualforce page.

Setup->Create->Tabs

Click New under the Visualforce Tabs

Select the Visualforce page you just created, type a Label that accurately describes the page, in this case, “View Loyalty Points”, and click Next.

Since you only want this tab visible in the Customer Portal, select Apply a different tab visibility for each profile, and select Default On for the Customer Portal Profiles you wish to expose the new Tab to.

Final Results

Once you have completed these steps you will have a new Tab that allows the Customer to send date ranges to a specific report. Pictured below.

 

 The Results

 

 

Tags: