Search This Blog

Wednesday, August 24, 2011

Passing Report Criteria via Dynamic Links

I know, this is nothing new. There are countless of blogs out there with information on how to pass report criteria in custom links, but as I looked around last night I realized that the information is somewhat scattered. I just want to try and bring it all together with a list of input prefixes and operators.  Hopefully this will be a useful resource for others. This is by no means a step-by-step post.

The whole concept of passing report criteria in a custom link boils down to URL hacking. Salesforce.com does not officially support this practice, but I find it very useful and leverage it every chance I get. For the experienced guru, you probably already know all of this stuff, and you probably stopped reading after the title. For everyone else, here we go.

Below is a screenshot of a report criteria. There are three fields in the criteria that you will need to pass data to in order to  fully control the criteria.



pc0: Corresponds to the field in the report criteria.
pn0: Corresponds to the operator in the report criteria.
pv0: Corresponds to the value in the report criteria.

These inputs are 0 based that means that you will access the first set of filter fields using 0 (e.g. pc0, pn0, pv0). As you add more filters, you just increase the number (pc0, pc1, pc2...).

Now to the operators. Here are the values that Salesforce is expecting:

eq = equals
ne = not equals
lt = less than
le = less than or equals
gt = greater than
ge = greater than or equals
co = contains
nc = does not contain
sw = starts with
in = includes

Bringing it all together. Run the desired report and copy the report ID from the URL. We'll use our Won Opportunities report for this example (https://na12.salesforce.com/00OU0000000aYlZ). Paste the report id into the link window. You can then append filter parameters at your leisure.

"/00OU0000000aYlZ?pc0=ACCOUNT_ID&pn0=eq&pv0={!Account.Id}&pc1=WON&pn1=eq&pv1=TRUE"

In the example above we are passing the Account ID in dynamically based on the record the user is in when he/she clicks on the link. Keep in mind that you can use the methods we have covered here to override filter criteria for existing report allowing you to reuse them when necessary instead of having to create a report with predefined filters where you only pass the filter value (pv). There are things you will need to tweak here and there. For example when passing currencies in your pv parameter you will want to URLENCODE() to make sure that value gets passed correctly.

You can also leverage Visual Force pages that include these links to make it a little more snazzy, adding descriptions and images to your link. We'll leave that for another day. Today's post is just scratching the surface on the subject.




6 comments:

  1. Hello, that's cool. I've found out, that for any Customfield to be passed as pc value, you have to pass the field ID. That's not the case for Standardfields: here you have to gues how the right spelling of the field is (in this context Salesforce is case-sensitive). Does anyone know how to pass the standard Salesforce field RecordType of a Custom object. I'm trying to find out now for a long time, (with the CustomOBjectName follwoed with . or : and with the CustomObjectID followed by . or :) but nothing worked until now.

    ReplyDelete
  2. I was able to do this for the RecordType for Account.
    The trick is to use the actual recordtype ID.
    Specifically in our salesforce instance, we have 3 account record types (buy-side,sell-side, Others). And the trick is not to do this:
    ?pc0=RECORDTYPE&pn0=eq&pv0=Sell-side
    But to see what is the recordtypeID value for Sell-side and use that. So for our instance, I see that the value associated with our Sell-side account record type is: 0120000000000Bs

    So you would use:
    ?pc0=RECORDTYPE&pn0=eq&pv0=0120000000000Bs

    ReplyDelete
  3. Thanks for sharing.
    When passing Id as a parameter from URL, always pass 15 digit ID.
    If you pass 18 digit ID, it will not show any error , but your report will not run.
    Just take care of passing 15 digit ID

    ReplyDelete
  4. Note that if pvX is a date field, and you want to pass in a relative date value, you will need to use the SOQL special date filters, not the user interface special date filters.

    For example,
    If pv0 is Opportunity.closeDate, use pv0=THIS_QUARTER

    ReplyDelete
  5. The best way to get the correct API name for the URL in the '?pc0=' part is by doing it like this in a visualforce page and referencing the controller to get the values.

    This example I am getting the API name for state/province filter.

    Here is the controller:

    public String getChartFilter() {

    string s = NULL;
    Reports.ReportDescribeResult descRes = Reports.ReportManager.describeReport(getreportbusinessId());
    for (Reports.ReportTypeColumnCategory category : descRes.getReporttypeMetadata().getCategories()) {
    for (Reports.ReportTypeColumn col : category.getColumns().values()) {
    if (col.getFilterable()) {
    if( col.getLabel() == 'State/Province')
    { s = col.getName(); return s;}
    }
    }
    }

    return s;
    }

    Then in the page just enclose this within a header tag

    {!chartFilter}


    youll get the right API name to pass into the report URL.

    ReplyDelete
  6. Do you have any insights on what parameters are used for Filter Logic?
    I have not found any documentation on that…

    ReplyDelete